CODEWITHSUNDEEP
eloquentlaravel-5
user3036307
user3036307

Reputation:

Laravel Eloquent - orderBy column find in related model

INFORMATIONS

Database

events        | id | to_user_id    | from_user_id |
event_details | id | event_id      | when         |

Event.php

class Event extends Eloquent {

protected $table = 'events';

public function event_detail() {
    return $this->hasOne('EventDetail');
}}

EventDetail.php

class EventDetail extends Eloquent {

protected $table = 'event_details';

public function event() {
    return $this->belongsTo('Event', 'event_id');
}}

QUESTION

I want to get all events (Event model) and order by 'when' column in related EventDetail model. I wrote a query and it works:

$event = Event::join('event_details as p', 'p.event_id', '=', 'event.id')
                    ->orderBy('p.when', 'asc')
                    ->select('events.*')
                    ->with('event_detail')
                    ->get();

but when i would like to add ->where('to_user_id', '=', 0) clause i get error. How can I fix it?

Moreover I want to know if this query is correct with good practise? Can you write it better?

Upvotes: 2

Views: 226

Answers (2)

A H Bensiali
A H Bensiali

Reputation: 875

Ok, had to recreate your project/tables All tests are running correctly but I have noticed a few things you may consider changing.

$events = Event::with(['event_detail'=> function($query) {
    $query->orderBy('when', 'asc');
}])
->where('to_user_id', 0)
->get();

Also your code may be better if you have an Event

public function event_detail(){
    //considering you will have multiples? or just the one event detail?
    return $this->hasMany('EventDetail');
}

As you can see, the one event I stored has 2 event details & they are all ordered by ASC. enter image description here

You can use the other method that doesnt utilize 'with()' but I prefer it. Very nice for json results where you can daisychain a lot of related models.

Hope this helps

Upvotes: 2

user3036307
user3036307

Reputation:

I solved the problem with this query. I should add ->where('events.to_user_id', '=', 0) so

$event = Event::join('event_details as p', 'p.event_id', '=', 'event.id')
                ->orderBy('p.when', 'asc')
                ->select('events.*')
                ->where('events.to_user_id', '=', 0)
                ->with('event_detail')
                ->get();

However, I would like to know if this query is correct with good practise? Can you write it better?

Upvotes: 0

Related Questions