Query not ordering by start_date?

Hi there, I'm new to the Laracasts forum and I look forward to using this forum to learn more about Laravel.

I've created a query below to output events for a user. It all works fine, except to the orderBy doesn't actually order the events by start_date. Can anyone assist with this query?

I've used the raw SQL for this query and it seems to work, so I'm not sure why it won't order correctly. I've even turned off the JSON formatter chrome plugin as that has bitten me before in the past!

$events = EventUser::with(['event' => function($query) use($compulsory) {
    $query->where('deleted', 0)->where('start_date', '<', NOW())->where('compulsory_id', $compulsory->id)->orderBy('start_date', 'DESC');
}])
->where('user_id', $user_id)
->get()
->pluck('event')
->flatten()
->values()
->all();

Many thanks

munazzil

6 years ago

Best Answer

Level 13

I think you are using both get and all in your query and it won't work, try to change it and use get() or all() in your query with that use get()->toArray() it also help to achieve what you need.

itsfg

6 years ago

Level 5

Do you always sort your events by start_date DESC ? If so, you could just modify the relation events and add your sorting :

public function events() {
    return $this->hasMany(Event::class)->orderBy('start_date', DESC);
}

Then your query will just be :

$events = EventUser::with('events')->where('...')->get();

If you have constraints for your events, use whereHas :

$events = EventUser:with('events')->whereHas('events', function($query) use ($compulsory) {
    $query->where('compulsory_id', $compulsory->id)->where('...');
})->where('...')->get();

This will only load events that are coherent with your where clauses.

thomas93

6 years ago

Level 1

Thanks all, I could only solve this by producing the below code. A bit ugly and not 100% pleased with it, but it works.

$events = EventUser::with('event')->whereHas('event', function($query) use ($compulsory) {
    $query->where('deleted', 0)->where('start_date', '<', NOW())->where('compulsory_id', $compulsory->id);
})->where('user_id', $user_id)->get()->pluck('event')->flatten()->toArray();

usort($events, function($a1, $a2) {
    $v1 = strtotime($a1['start_date']);
    $v2 = strtotime($a2['start_date']);
    return $v2 - $v1;
});

1 like

Please or to participate in this conversation.