Use property value in select query in Laravel 5

Question

Model Event -> id, guests_count, status. Model Guest -> id, event_id, confirmed.

I need something like this:

Event::where('status', 'opened')->whereNotIn('id', $nId)
                        ->has('guests', '<' , XXX)
                        ->get();

Instead XXX I need to use guests_count value - different value for each event - is this possible?

I'm trying to get all not filled events (count of guests relation < guests_count).

I'm using Laravel 5.4.

guests_count is a column having different values for different Event in the database table? — Saiyan Prince
– Saiyan Prince, Commented Apr 20, 2018 at 9:34
@SaiyanPrince it's number of max guests. Different values for diff Event. If number of guests (relations) is equal to guests_count and all guests are confirmed (invitations), status is updated to Full. So there might be case when number if guests is equal to guests_count but status is not Full (when all invitations aren't confirmed yet) - so I can't simply search by status — Nidecker
– Nidecker, Commented Apr 20, 2018 at 10:06

Jonas Staudenmeir · Accepted Answer · 2018-04-20 14:55:04Z

1

Use this:

Event::where('status', 'opened')->whereNotIn('id', $nId)
    ->withCount('guests as count')
    ->having('guests_count', '>', DB::raw('`count`'))
    ->get();

answered Apr 20, 2018 at 14:55

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

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

2 Comments

Nidecker Over a year ago

Perfect! Thanks! Just small edit - withCount('guests as count') is adding _count after that name, so 'correct' is ->having('guests_count', '>', DB::raw('count_count'))

Jonas Staudenmeir Over a year ago

I only tested the code in 5.6. I looked at the source, this behavior was changed in Laravel 5.5: In 5.5+ the alias is just count.

qskane · Accepted Answer · 2018-04-20 10:01:15Z

0

did you need this ?

Event::select([DB::raw('count(guests) as count'),'id'])
    ->where('status','opened')
    ->whereNotIn('id', $nId)
    ->groupBy('guests')
    ->having('count','>',10)
    ->get();

sql output

select count(guests) as count, `id` from `event` where `status` = 'opened' and `id` not in ([1,2,3]) group by `guests` having `count` > 10

answered Apr 20, 2018 at 10:01

qskane

5214 silver badges17 bronze badges

2 Comments

Nidecker Over a year ago

Why is there ->having('count','>',10)? This number is always different for different Event, in one Event it is 50, in another 300, I have to use this specific value in query. So if guests_count is 10, I need include this event in collection only if count of guests relation is < 10. If these 2 values are equal, I don't want this in collection

Nidecker Over a year ago

Event 1 - guests_count = 10; count of guests relation = 7; include in collection Event 2 - guests_count = 55; count of guests relation = 42; include in collection Event 3 - guests_count = 15; count of guests relation = 15; don't include

Collectives™ on Stack Overflow

Use property value in select query in Laravel 5

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related