SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias:

[qmeyti]

I got this error.

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'contents' (SQL: select count(*) as aggregate from (selectposts.*, max((case when LOWER(contents.title) LIKE sed then 150 else 0 end) + (case when LOWER(contents.title) LIKE sed% then 50 else 0 end) + (case when LOWER(contents.title) LIKE %sed% then 10 else 0 end) + (case when LOWER(contents.excerpt) LIKE sed then 105 else 0 end) + (case when LOWER(contents.excerpt) LIKE sed% then 35 else 0 end) + (case when LOWER(contents.excerpt) LIKE %sed% then 7 else 0 end) + (case when LOWER(contents.body) LIKE sed then 135 else 0 end) + (case when LOWER(contents.body) LIKE sed% then 45 else 0 end) + (case when LOWER(contents.body) LIKE %sed% then 9 else 0 end) + (case when LOWER(contents.seo_title) LIKE sed then 120 else 0 end) + (case when LOWER(contents.seo_title) LIKE sed% then 40 else 0 end) + (case when LOWER(contents.seo_title) LIKE %sed% then 8 else 0 end) + (case when LOWER(contents.meta_description) LIKE sed then 90 else 0 end) + (case when LOWER(contents.meta_description) LIKE sed% then 30 else 0 end) + (case when LOWER(contents.meta_description) LIKE %sed% then 6 else 0 end)) as relevance frompostsinner joincontentsonposts.id=contents.post_idinner joinusersonposts.user_id=users.idleft joincontentsonposts.id=contents.post_idwhereposts.type= post andposts.status!= auto-draft group byposts.id,contents.title,

my search rule is

` protected $searchable = [ 'columns' => [ 'contents.title' => 10, 'contents.excerpt' => 7, 'contents.body' => 9, 'contents.seo_title' => 8, 'contents.meta_description' => 6, ], 'joins' => [ 'contents' => ['posts.id', 'contents.post_id'], ],

];`

[dharmendrashah]

same here

[dannygits]

also have the same issue

[kevintresuelo]

In config/database.php, set mysql's 'strict' to false.

'mysql' => [
    ...
    'strict' => false,
   ...
],

[zaros-systems]

An alternative is to update the config modes on the fly, this way you only disable the modes needed to run your query

config()->set('database.connections.mysql.modes', [
    // 'ONLY_FULL_GROUP_BY',
    'STRICT_TRANS_TABLES',
    'NO_ZERO_IN_DATE',
    'NO_ZERO_DATE',
    'ERROR_FOR_DIVISION_BY_ZERO',
    'NO_AUTO_CREATE_USER',
    'NO_ENGINE_SUBSTITUTION'
]);

And then enable all modes again afterwards:

config()->set('database.connections.mysql.modes', null);

However this feels like a temporary fix that should not be needed.