How to build such query?

Hi,

I have three db tables:

tournaments
id
slots
is_closed
participations
id
user_id
tournament_id
users
id
name

(all of them are simplified).

First I want to get tournaments which has is_closed field set to false and which participations count is less than slots available in this tournament.

It would be done if I knew how to replace '999' on tournament's slots field in such query:

$activeTournaments = Tournament::has('participations', '>=', '999')->where('is_closed',false)->get();

If someone could help me I would like to expand this query and retrieve if logged user is participant in select field?

uxweb

11 years ago

Level 20

I will do it like this:

<?php

Tournament::where('is_closed', '=', false)
    ->whereExists(function($query)

        $query->select(DB::raw('COUNT(1) AS count'))
            ->from('participations')
            ->whereRaw('tournaments.id = participations.tournament_id')
            ->having('count', '<=', DB::raw('tournaments.slots'));
    )
    ->get();

Think this should work, not pretty sure about the having part using the DB::raw but give it a try.

pmall

11 years ago

Level 56

WhereHas with a callback function maybe ?

And I think you can replace '999' by 'slots' in your example.

Please or to participate in this conversation.