Join query using eloquent model mapping

Question

I am trying to do this

select notifications.id, reservations.number from 
notifications 
JOIN reservations 
ON notifications.reservation_id = reservations.id 
WHERE notifications.status = 1

using eloquent so I have this this

$await = Notification::with('Reservation')->
select('notifications.id', 'reservations.number')
->where('notifications.status', '=', 1)->get();

return Response::json($awaitLists);

In my Notification model

public function Reservation() {
        return $this->belongsTO('Reservation');
    }

In my Reservation Model

public function notification() {
        return $this->hasMany('Notification');
    }

So notification belongs to reservation while reservation has a 1 to many relationship

My question is why can't what I have tried works. I keep getting Unknown column 'reservation.number' but i do have column called number in the reservations table. I know they is a way to use eloquent relationship mapper to do this.

with doesn't join tables. It runs separate queries. You need to join the tables manually, or rely on relationship. — Jarek Tkaczyk
– Jarek Tkaczyk, Commented Jan 29, 2015 at 19:11

mckendricks · Accepted Answer · 2015-01-29 19:32:31Z

1

This should do it:

$notifications = Notification::where('status','=',1)->get();

foreach($notifications as $notification) {
    $id = $notification->id;
    $num = $notification->reservation->number;

    $await = [$id,$num];
    var_dump($await);
}

answered Jan 29, 2015 at 19:32

mckendricks

3771 silver badge3 bronze badges

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

1 Comment

patricus Over a year ago

Don't forget to eager load the reservation relationship, otherwise you run into the n+1 issue.

patricus · Accepted Answer · 2015-01-29 19:28:22Z

The error you're seeing is because eager loading relationships doesn't actually perform a join. It uses two separate queries, and then the relationship fields are assigned after the queries are run.

So, when you do Notification::with('Reservation')->get(), it is running two SQL statements, approximately:

Notification::with('Reservation')->get();
// select * from notifications;
// select * from reservations where id in (?, ?, ...);

You can see the actual queries run with a dd(DB::getQueryLog()), if you're interested.

How you move forward depends on what you need to do. If you need to duplicate your existing query exactly, then you'll need to manually perform the joins.

$notifications = Notification::select('notifications.id', 'reservations.number')
    ->join('reservations', 'notifications.reservation_id', '=', 'reservations.id`)
    ->where('notifications.status', '=', 1)
    ->get();

foreach($notifications as $notification) {
    print_r($notification->number);
}

Otherwise, you can just use the objects as they are built by Laravel:

$notifications = Notification::with('Reservation')->where('status', '=', 1)->get();

foreach($notifications as $notification) {
    print_r($notification->Reservation->number);
}

Collectives™ on Stack Overflow

Join query using eloquent model mapping

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related