Laravel query build based on relation

Question

I have such query:

Tournament::has('participations', '<', '2')->get();

How can I replace constant number '2' on tournament's column named slots ?? I would like retrieve only these tournaments which have least participants than slots in tournament.

lukasgeiter · Accepted Answer · 2014-11-15 20:03:08Z

3

Let's start by using the column name instead of "2". Like you would do in "normal" SQL

Tournament::has('participations', '<', 'slots')->get();

Now, you don't even have to try that because it won't work. But why's that? Because Laravel treats slots like a string and escapes it so SQL does as well.

What you need to do, is use DB::raw(). raw makes sure Laravel changes nothing and just injects it into the SQL query

Tournament::has('participations', '<', DB::raw('slots'))->get();

Update

After some trying out I found this: (Its not very pretty but the only way I got it working)

$subquery = function($q) use ($uid){
    $q->where('player_id', $uid);
}

Tournament::whereHas('participations', $subquery)
    ->whereHas('participations', $subquery, '<', DB::raw('slots'))
    ->get();

The first whereAs checks for count(*) > 0, the second count(*) < slots and the subquery filters by player id.

edited Nov 15, 2014 at 20:03

answered Nov 15, 2014 at 19:21

lukasgeiter

154k29 gold badges349 silver badges280 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lukas Over a year ago

thank you, it works. Do you know how to expand this query by smth like this: (select count(*) > 0 from participations where tournament_id= tournament.id and player_id = '.$uid.') as isParticipating where $uid is current logged user id. I just want to retrieve if logged user participates in this tournament or not.

lukasgeiter Over a year ago

@Lukas I updated my answer, hope this is what you needed

Collectives™ on Stack Overflow

Laravel query build based on relation

1 Answer 1

Update

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related