Filter Pivot table conditionally with inverse relation - Laravel 5

Question

I have roomConfigurations table like bellow,

id
roomTypeID
hotelId

Then Hotels table is like this

id
hotelNeme

Then Users table like this

id
userId
userName
passwd

Then hotel_user (Pivot) table like this

id
user_id
hotel_id

Now my question is, How can i get all records from roomConfigurations table for selected user ?

I can do this using MySQL by joining tables, But i dont have any idea to do this in Laravel 5.

I would be very appreciate, if anyone can help me on this. Thanks

score 1 · Answer 1 · edited May 23 '17 at 12:14

Laravel docs should help here.

First, you need to make sure that roomConfigurations table has id, room_type_id, and hotel_id not hotelId because Laravel requires certain naming conventions.

But, guessing by what you've written so far, I think it'd be something like this:

class HotelUser extends Model {
    // so that we can get hotels from a user
    public function hotels () {
        return $this->belongToMany ('App\Hotel');
    }
}

class Hotel extends Model {
    // so that we can get users of a Hotel
    public function users () {
        return $this->belongsToMany ('App\HotelUser');
    }

    public function roomConfigurations () {
        return $this->belongToMany ('App\RoomConfiguration');
    }
}

class RoomConfiguration extends Model {
}

And then to use this it might be something like:

$userRoomConfigs = HotelUser::find(123)->hotels()->roomConfigurations();

Another, better, possibility might be here:

class HotelUser extends Model {
    public function roomConfigurations () {
        return $this->hasManyThrough ('App\RoomConfiguration', 'App\Hotel');
    }
}

And this would be used like so:

$userRoomConfigs = HotelUser::find(123)->roomConfigurations();

To help with your endeavor, check out this stack overflow answer which explains how to show the raw SQL queries that Laravel is making.

Thanks for the answer – Lahiru Maduranga Aug 27 '15 at 06:14 — Lahiru Maduranga, Aug 27 '15 at 06:14

score 0 · Answer 2 · answered Aug 27 '15 at 06:18

I used this and it is giving me what i want
class roomConfigController extends Controller {

public function index()
{
if(Auth::User()->role == 'client') {    
$roomConfigs = roomConfiguration::whereHas('hotels', function($query)
    {
        $query->whereHas('users', function ($query)
        {
        $query->where('users.id', Auth::User()->id);
        });
    })->where('is_delete', '=', '0')->get();
}
else {
    $roomConfigs = roomConfiguration::where('is_delete', '=', '0')->get();
}

//return $roomConfigs;
    return view('roomConfig.index',compact('roomConfigs'));
}

Filter Pivot table conditionally with inverse relation - Laravel 5

2 Answers2