Ad

Find Eloquent Models Using Where And Relations

- 1 answer

I have three database tables, Hubs, Rooms & Devices as below.

Database tables and columns

  • Hubs: id, name
  • Rooms: id, hub_id, name
  • Devices: id, room_id, name

As you can imagine, I have three models.

class Hub extends Model {
  public $hasMany = ['rooms' => ['Models\Room']];
}

class Room extends Model {
  public $hasMany = ['devices' => ['Models\Device']];
  public $belongsTo = ['hub' => [
        'Models\Hub',
        'key' => 'hub_id'
  ]];
}

class Device extends Model {
  public $belongsTo = ['room' => [
        'Models\Room',
        'key' => 'room_id'
  ]];
}

And I have the following data records.

Hubs

  • 1, HubOne
  • 2, HubTwo

Rooms and devices (for example)

  • Hub(1) Living room (living room light, walkway light)
  • Hub(1) Bedroom (bedroom light, table lamp)
  • Hub(1) Kitchen (table lamp)
  • Hub(2) Living room (living room light, walkway light)

I'd like to find devices by hub_id and a particular term. The hub_id should be exactly match. The term should be "LIKE" either the room name or device name.

For example: hub_id = 1, term = "living room".

The results should be all the devices in Hub(1) living room.

For example: hub_id = 1, term = "table lamp"

The results should be the two lamps in the Bedroom & Kitchen.

But if the hub_id = 1, term = "living room light"

The result should be the only light match the name.

How can I do this using the Eloquent Models & Collections query style instead of raw query? The following script of mine is pretty close but still not correct. Please help.

$rooms = Room::with('devices')
        ->where('hub_id', $hub_id)
        ->where(function ($query) use ($term) {
            $query->whereHas('devices', function ($query) use ($term) {
                $query->where('device_name', 'LIKE', '%' . $term . '%');
            })->orWhere('title', 'LIKE', '%' . $term . '%');
        })->get();

$devices = $rooms->lists('devices');
Ad

Answer

This code should work:

$devices = Device::where(function($query) use ($term) {
    $query->where('device_name', 'like', '%' . $term . '%')
        ->whereIn('room_id', function($sub_query) use ($term) {
            $sub_query->select('id')->from('rooms')
                ->where('room_name', 'like', '%' . $term . '%');
        }, 'or')
    })
    ->whereIn('room_id', function ($query) use ($hub_id) {
            $query->select('id')->from('rooms')
            ->where('hub_id', $hub_id)
    })->get();
Ad
source: stackoverflow.com
Ad