CODEWITHSUNDEEP
phplaravellaravel-5eloquent
wobsoriano
wobsoriano

Reputation: 13462

Laravel 5 where clause from another table

I have a simple code and what I want to do is to access a field from another table and put it in my where clause. This is my code:

ReportController.php

$reservations = Reservation::with('charge', 'room', 'client')
-> whereBetween('reservation_from', [$from, $to])
-> where('room.type', \Request::input('type')) //what should this be
-> orderBy('created_at')
-> get();

Room.php

class Room extends Model
{
    use SoftDeletes;
    protected $table = 'rooms';

    protected $fillable = ['id', 'roomNumber', 'type', 'price', 'description'];

    public function reservations() {
        return $this->hasMany('App\Reservation', 'id', 'room_number');
    }
}

Reservation.php

class Reservation extends Model
{
    use SoftDeletes;
    protected $table = 'reservations';

    protected $fillable = ['roomNumber', 'clientId', 'reservation_from', 'reservation_to'];

    public function room() {
        return $this->belongsTo('App\Room');
    }

}

Schema: enter image description here

As you can see in the ReportController.php, there is a comment saying "what should this be", that's the part that I want to fix. What I wanted to do is access the type field in the rooms table in my eloquent query.

The query that I want to do is like this:

select * from `reservations` where `reservation_from` between '2015-10-29' and '2015-10-29' and `rooms.type` = "test"

Is there a way to do this? Thank you.

Upvotes: 3

Views: 19991

Answers (2)

Thomas Kim
Thomas Kim

Reputation: 15961

What you are looking for is the whereHas method.

$reservations = Reservation::with('charge', 'room', 'client')
    ->whereBetween('reservation_from', [$from, $to])
    ->whereHas('room', function($query) {
        $query->where('type', '=', \Request::input('type'));
    })
    ->orderBy('created_at')
    ->get();

Link to docs: http://laravel.com/docs/5.1/eloquent-relationships#querying-relations

Edit:

Editing this to clarify some things in the comments.

To create convenient, reusable query constraints to make your code cleaner, you can use query constraints: http://laravel.com/docs/5.1/eloquent#query-scopes

Also, because queries can be chained, you can do something like this: 

// Create query with common constraints
$query = Reservation::with('charge', 'room', 'client')
    ->whereBetween('reservation_from', [$from, $to]);

// Ternary operator to decide whether or not to add whereHas constraint
$query = (\Request::input('type') == "all") ? $query : $query->whereHas('room', function($query) {
    $query->where('type', '=', \Request::input('type'));
});

// Finally, fetch the results sorted by 'latest', which is a convenient way of doing "orderBy('created')"
$reservations = $query->latest()->get();

Upvotes: 10

whoacowboy
whoacowboy

Reputation: 7447

I believe you are looking to do this. Updated per your question update. The with method takes a string or an array.

$reservations = Reservation::with(['charge', 'client', 'room' =>   
    function($query){
        $query->where('type', \Request::input('type'));
    }])
->whereBetween('reservation_from', [$from, $to])
->orderBy('created_at')
->get();

Upvotes: 4

Related Questions