CODEWITHSUNDEEP
laraveleloquent
naneri
naneri

Reputation: 3971

How to use multiple 'where' clause with multiple tables with Eloquent ORM?

I have a database query with multiple 'JOIN' statements in my Laravel application, but I don't know how to correctly add a where clause to it.

Here is my function: 

    return Topic::join('blogs', 'topics.blog_id', '=', 'blogs.id')
            ->join('blog_subscriptions as us', function ($j) use ($userId){
                  $j->on('us.blog_id', '=', 'blogs.id')
                    ->where('us.user_id', '=', $userId);
            })
            ->take(Config::get('topic.topics_per_page'))
            ->offset($offset)
            ->get(['topics.*']);

I would like to add a 'where' clause to the 'topics' table - so I add a line where('rating', '>', 1), after "Topic::", so the code is like this: 

Topic::where('rating', '>', 1)
            ->join('blogs', 'topics.blog_id', '=', 'blogs.id')
            ->join('blog_subscriptions as us', function ($j) use ($userId){
                $j->on('us.blog_id', '=', 'blogs.id')
                ->where('us.user_id', '=', $userId);
            })
            ->take(Config::get('topic.topics_per_page'))
            ->offset($offset)
            ->get(['topics.*']);

but it only leads to an error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'rating' in where clause is ambiguous (SQL: select topics.* from topics inner join blogs on topics.blog_id = blogs.id inner join blog_subscriptions as us on us.blog_id = blogs.id and us.user_id = 1 where rating > 1 limit 2 offset 0)

Upvotes: 0

Views: 1202

Answers (1)

lukasgeiter
lukasgeiter

Reputation: 153150

As the error message says, the column rating is ambiguous. Meaning SQL can't tell which column you mean because there might be another one in your join that's named rating as well.

In this situation it helps to clarify in which table the field is. This is simply done by using the [table].[field] syntax

where('topics.rating', '>', 1)

Upvotes: 0

Related Questions