1

Im using Laravel 9.39.0, php 8.1, mysql 8.0.31.

My goal is to add a scope that will filter models where the user that user_id references has been archived or filtered in some way. The filtering of the user is done in another scope.

Just using $builder->has('user'); in the scope works just fine, but eloquent will use an exists query which is not that efficient. (This will later have to work for polymorphic relations which is where the performance-impact will be very noticeable).So i tried creating a join instead of has which will be more efficient.It works fine on an index-view but when i try to show a single model i get: "**Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous".**If i dump the query for the show-function in the models controller i can see that it tries: where id = ? limit 1 and the id is not prefixed with the models name which leads to the error.

Even this basic code below in the scope generates the error.

builder->leftJoin('users', 'users.id', 'projects.user_id')

This code above generates the following query:

select * from `projects` left join `users` on `users`.`id` = `projects`.`user_id` where `id` = ? limit 1

Is there a way to force eloquent to prefix the table name in the query?

Thank you for reading, i hope you can help.

I have tried googling for a long time, i tried to use Eloquent Power Join package but it didn't work..

Tim Lewis
  • 27,813
  • 13
  • 73
  • 102
Filip
  • 11
  • 1
  • This doesn't seem like a problem with the join but rather with you select/ where conditions – SuperDJ Nov 23 '22 at 16:16
  • I'm not entirely sure that an exists is more efficient than an outer join to be honest. Unless this is a 1 - 1 relationship you will end up with a lot of data duplication in the result. – apokryfos Nov 23 '22 at 16:27
  • @SuperDJ, the select/where conditions are created using route model binding. This is the issue. – Filip Nov 24 '22 at 16:52
  • @apokryfos i said that the exists query was less efficient, not more – Filip Nov 24 '22 at 16:53
  • Yes I misspoke, I'm not sure an exists is less efficient – apokryfos Dec 02 '22 at 16:59

1 Answers1

1

You can use:

$builder->leftJoin('users', 'users.id', 'projects.user_id')->where("users.id",1);
Santiago Za
  • 21
  • 1