CODEWITHSUNDEEP
phpmysqllaraveljoineloquent
harveyslash
harveyslash

Reputation: 6012

Laravel How to do this relationship query

I have a current database setup like so : enter image description here

I have a user model/vote model/group model/vote subitem model. There is a many to many relationship between user and vote, and also a many to many relationship between user and subitem.

I want to get all the votes with created at timestamp to something , which belong to a set of group ids , where a particular user does NOT have an entry in user_vote_casts table.

How can I do this in laravel ?

In mysql, this is what I think is right:

SELECT * from vote left join user_vote_casts on vote.id = user_vote_casts.vote_id where user_vote_casts.user_id IS NULL AND vote.id IN (12,3142,512,51,12,1) ;

Upvotes: 0

Views: 120

Answers (2)

harveyslash
harveyslash

Reputation: 6012

What I finally ended up doing is:

    $vote = Vote::leftJoin('user_vote_casts', function ($join) {
                $join->on('vote.id', '=', 'user_vote_casts.vote_id');
            })->whereNull('user_vote_casts.user_id')
->orderBy('vote.updated_at', 'DESC')
->take(30)->get();

Upvotes: 0

Yasin Patel
Yasin Patel

Reputation: 5731

Laravel Query as per your SQL query:

$users = DB::table('vote')
            ->leftJoin('user_vote_casts', 'vote.id', '=', 'user_vote_casts.vote_id')
            ->select('vote.*')
            ->where('user_vote_casts.user_id','')
            ->whereIN('vote.id',[12,3142,512,51,12,1])
            ->get();

Upvotes: 3

Related Questions