Laravel：JOIN中的SQLSTATE [42000]错误（状态代码500）

Question

as you see I'm getting SQLSTATE[42000] error. I have 2 scopes in my model, but they can't work together, if I disable the order one it works but when I try to search I get the SQLSTAT[42000] but if I disable the order function the search works perfectly. I believe the error is in the JOINs but can't find exactly where.

This is both of the functions search and order

public function scopeSearch($query, $data) {
    if (trim($data) == "")
      return;

    $query
      ->orWhere('idResource', 'like', "%$data%")
      ->orWhere('title', 'like', "%$data%")
      ->orWhere('description', 'like', "%$data%")
      ->orWhere('minimumAge', 'like', "%$data%")
      ->orWhere('maximumAge', 'like', "%$data%")
      ->orWhere('fileName', 'like', "%$data%")
      ->orWhere('extension', 'like', "%$data%")
      ->orWhere('URL', 'like', "%$data%")
      ->orWhere('createTime', 'like', "%$data%")
      ->orWhere('productionKey', 'like', "%$data%");

    $query->orWhereHas('user', function ($query) use ($data) {
      $query->where('name', 'like', "%$data%");
    });

    $query->orWhereHas('country', function ($query) use ($data) {
      $query->where('name', 'like', "%$data%");
    });

    $query->orWhereHas('resource', function ($query) use ($data) {
      $query->where('name', 'like', "%$data%");
    });

    $query->orWhereHas('quickTags', function ($query) use ($data) {
      $query->where('name', 'like', "%$data%");
    });

    $query->orWhereHas('tags', function ($query) use ($data) {
      $query->where('name', 'like', "%$data%");
    });

    $query->orWhereHas('relatedTo', function ($query) use ($data) {
      $query->where('name', 'like', "%$data%");
    });
  }

  public function scopeOrd($query) {
    $query
        ->join('OPR_User', 'idUser', '=', 'idCreatorUser')
        ->join('CTL_Country', 'idCountry', '=', 'idCreationCountry')
        ->join('CTL_ResourceType', 'CTL_ResourceType.idResourceType', '=', 'CTL_Resource.idResourceType')
        ->select(
          'CTL_Resource.*', 
          'OPR_User.name as creatorUser',
          'CTL_Country.country as creationCountry',
          'CTL_ResourceType.resourceType as resourceType'
        );
  }

And this is my controller

  public function index(Request $request) {
    $count       = DB::table('CTL_Resource')->count();
    $per_page    = $request->per_page    ? $request->per_page    : $count;
    $order       = $request->sort_order  ? $request->sort_order  : 'DESC';
    $sort_name   = $request->sort_name   ? $request->sort_name   : 'createTime';
    $search_text = $request->search_text ? $request->search_text : '';

    $relations = [
      'tags',
      'quickTags',
      'relatedTo'
    ];

    return CTL_Resource::with($relations)
      ->search($search_text)
      ->ord()
      ->orderBy($sort_name, $order)
      ->paginate($per_page);
  }

And this is the entire error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CTL_Resource.idCreatorUser `` inner join `CTL_Country`.`idCountry` on `=` CTL_Re' at line 1 (SQL: select count(*) as aggregate from `CTL_Resource` inner join `OPR_User`.`idUser` on `=` CTL_Resource.idCreatorUser `` inner join `CTL_Country`.`idCountry` on `=` CTL_Resource.idCreationCountry `` inner join `CTL_ResourceType`.`idResourceType` on `=` CTL_Resource.idResourceType `` where `idResource` like %Blanca Wiza% or `title` like %Blanca Wiza% or `description` like %Blanca Wiza% or `minimumAge` like %Blanca Wiza% or `maximumAge` like %Blanca Wiza% or `fileName` like %Blanca Wiza% or `extension` like %Blanca Wiza% or `URL` like %Blanca Wiza% or `createTime` like %Blanca Wiza% or `productionKey` like %Blanca Wiza% or exists (select * from `OPR_User` where `CTL_Resource`.`idCreatorUser` = `OPR_User`.`idUser` and `name` like %Blanca Wiza%) or exists (select * from `CTL_Country` where `CTL_Resource`.`idCreationCountry` = `CTL_Country`.`idCountry` and `country` like %Blanca Wiza%) or exists (select * from `CTL_ResourceType` where `CTL_Resource`.`idResourceType` = `CTL_ResourceType`.`idResourceType` and `resourceType` like %Blanca Wiza%) or exists (select * from `CTL_QuickTags` inner join `CTL_Resource_has_QuickTags` on `CTL_QuickTags`.`idQuickTag` = `CTL_Resource_has_QuickTags`.`idQuickTag` where `CTL_Resource_has_QuickTags`.`idResource` = `CTL_Resource`.`idResource` and `name` like %Blanca Wiza%) or exists (select * from `CTL_Tags` inner join `CTL_Resource_has_Tags` on `CTL_Tags`.`idTag` = `CTL_Resource_has_Tags`.`idTag` where `CTL_Resource_has_Tags`.`idResource` = `CTL_Resource`.`idResource` and `name` like %Blanca Wiza%) or exists (select * from `CTL_RelatedTo` inner join `CTL_Resource_has_RelatedTo` on `CTL_RelatedTo`.`idRelatedTo` = `CTL_Resource_has_RelatedTo`.`idRelatedTo` where `CTL_Resource_has_RelatedTo`.`idResource` = `CTL_Resource`.`idResource` and `name` like %Blanca Wiza%))

Answer 1

In your Joins statements you have to add the alias of the table before the column name, for example:

join('OPR_User', 'OPR_User.idUser', '=', 'tablename.idCreatorUser')

You would do this to all of your joins, make sure you use the correct table alias/name.