CODEWITHSUNDEEP
laravel-5eloquent
Alex Angelico
Alex Angelico

Reputation: 4065

Filter results from a laravel collection based on the related record

I'm using https://github.com/nicolaslopezj/searchable in my Laravel Project.

I have a Hotel object with a many to many relationships with Users.

I can query the relationship using this:

protected $searchable = [
    'columns' => [
        'hotels.nombre' => 10
    ],
    'joins' => [
        'hotel_user' => ['hotels.id' , 'hotel_id'],
        'users' => ['hotel_user.user_id', 'users.id']
    ]
];

The search returns something like this (json format)

 [
  {
    "id": 3,
    "nombre": "Paradisus Playa del Carmen La Esmeralda",
    "url_foto": "uploads/hotel/3/1484747239.jpg",
    "website_url": "https://www.melia.com/es/hoteles/mexico/playa-del-carmen/home.htm",
    "updated_at": "2017-01-18 13:47:44",
    "relevance": 60,
    "users": [
      {
        "id": 1,
        "first_name": "Alex",
        "last_name": "Angelico",
        "formatted_name": "Alex",
        "company": "ConexionBIZ",
        "title": "Director",
        "picture_url": "https://media.licdn.com/mpr/mprx/0_FIUn3Wf5E4OMEwzR5feW3o7IoRSveSkR5W7d3oxczOM5BdPUwDws7EIJXvDEIE5c6HMeaSSFgb19",
        "created_at": "2017-01-17 12:00:00",
        "updated_at": "2017-01-18 13:50:19",
        "pivot": {
          "hotel_id": 3,
          "user_id": 1,
          "created_at": null,
          "updated_at": null
        }
      }
    ]
  },

I want to filter all the hotels belonging to some particular user. I tried: 

 $result = \App\Hotel::search($request->get('q'))->with([
            'users' => function($q) use ($user_id) {
                $q->where('users.id', '<>', $user_id);
            },
        ])->get();

But this returns the hotel without the related user. I need to remove the HOTEL (not just the USER) from the results.

How can I do this?

Thanks a lot!

Upvotes: 0

Views: 613

Answers (1)

Eric Tucker
Eric Tucker

Reputation: 6345

The with() method is actually ran as a second query after the Hotel collection is retrieved and is used for lazy loading.

What you're looking for is the whereHas() which will filter the current model results based on a related model.

$result = \App\Hotel::search($request->get('q'))
    ->whereHas('users', function($q) use ($user_id) {
        $q->where('users.id', '<>', $user_id);
    })
    ->get();

That will return all Hotel models that have users that don't match $user_id.

If you need to load the users too then:

$result = \App\Hotel::search($request->get('q'))
    ->with('users')
    ->whereHas('users', function($q) use ($user_id) {
        $q->where('users.id', '<>', $user_id);
    })
    ->get();

That will load all Hotel models that have at least 1 user not matching $user_id but the $result->first()->users collection can still contain users with that id. To not return any Hotel models that have $user_id and to not return any users in the related collection you would combine both methods:

$result = \App\Hotel::search($request->get('q'))
    ->with(['users' => function($q) use ($user_id) {
        $q->where('users.id', '<>', $user_id);
    }])
    ->whereHas('users', function($q) use ($user_id) {
        $q->where('users.id', '<>', $user_id);
    })
    ->get();

Upvotes: 1

Related Questions