Laravel belongsToMany.belongsTo nested relationship query where child column = parent id

Question

I have two models (and database tables with Laravel naming conventions) and I am trying to Eager Load the Collections with Equipment and Equipment.collection_meters where collection_meter equals Collection.id.

How can I access the collections.id in the child relationship $query->with?

or

How can I access the equipment.pivot.collection_id in the child relationship $query->with?

Here are my models ...

class Collection extends Model {
    public function equipment() {
        return $this->belongsToMany(Equipment::class, 'collection_equipment', 'collection_id', 'equipment_id')->withTimestamps();
    }
}

class CollectionMeter extends Model {
    public function equipment() {
        return $this->belongsTo(Equipment::class); 
    }
}

class Equipment extends Model {
    public function collection_meters() {
        return $this->hasMany(CollectionMeter::class)->latest(); // ->currentStatus('active')
    }
}

equipment_id column is in the collection_meters table

Here is the code I have tried but I just can't seem to get the Collection.id in the child relationship query. If I leave out the child $query->where, I get ALL of the equipment.equipment_meters.

$collections = Collection::with([
    'equipment',
    'equipment.collection_meters' => function($query) {
        $query->where('collection_meters.collection_id', '=', 'collections.id');
    }
])
->get();

I have tried these in place of 'collections.id' with the same result 'equipment.pivot.collection_id' 'equipment.collection_id'

I do see this in the [equipment] => Array, can I access [collection_id] => 2 in the $query->where somehow?

[pivot] => Array
    (
        [collection_id] => 2
        [equipment_id] => 1
        [created_at] => 2019-09-17 00:17:00
        [updated_at] => 2019-09-17 00:17:00
    )

Results without the $query->with ([collection_meters] has ALL)

Results with the $query->with ([collection_meters] is empty)

Array
(
    [0] => Array
        (
            [id] => 2
            [name] => Name of Collection
            [operator_id] => 1
            [location_id] => 4
            [account_id] => 1
            [date_time] => 2019-09-16 04:41:26
            [reconciliation_id] => 
            [created_by_id] => 1
            [updated_by_id] => 1
            [created_at] => 2019-09-16 23:44:43
            [updated_at] => 2019-09-18 17:32:53
            [equipment] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [name] => Name of Equipment
                            [ims_identifier] => AFA-64
                            [operator_identifier] => 073076-01
                            [equipment_model_id] => 1
                            [operator_id] => 1
                            [location_id] => 4
                            [created_by_id] => 1
                            [updated_by_id] => 1
                            [created_at] => 2019-07-17 13:17:28
                            [updated_at] => 2019-08-14 00:04:07
                            [pivot] => Array
                                (
                                    [collection_id] => 2
                                    [equipment_id] => 1
                                    [created_at] => 2019-09-17 00:17:00
                                    [updated_at] => 2019-09-17 00:17:00
                                )

                            [collection_meters] => Array
                                (
                                )

                        ) ...

Results expected ([collection_meters] has [id] => 2 equals to [collection_id] => 2)

score -1 · Answer 1 · answered Sep 19 '19 at 21:07

What kind of relationship are you using? "Many to Many" or "One to Many"? If you have three tables, one of them "pivot", you must define in both Models the relationship as belongsToMany:

class Collection extends Model {

    public function equipments() {
        return $this->belongsToMany(Equipment::class, 'collection_equipment', 
        'collection_id', 'equipment_id')->withTimestamps();
    }
}

What is the name of the pivot table? collection_equipment ? How is your structure?

collections => collection_equipment <= equipments

class Equipment extends Model {

    public function collections() {
       return $this->belongsToMany(Collection::class, 'collection_equipment', 
       'equipment_id', 'collection_id')->withTimestamps();
    }
}

Where is the collection_meters column? In the equipments table?

Then, if you want to get nested table information, just:

$collections = Collection::with('equipments')->get();

But sometimes you want to get information using a Pivot Model:

class CollectionEquipment extends Model {

    protected $table = 'collection_equipment';

    public function collection() {
       return $this->belongsTo(Collection::class, 'collection_id');
    }

    public function equipment() {
       return $this->belongsTo(Equipment::class, 'equipment_id');
    }

}

Then, just:

$collections = CollectionEquipment::with(['equipment', 'collection'])
               ->get();

Since you have already defined collection_id and equipment_id in all Model classes, you don't need to map them in your query as you would using 'join'

I have edited my question ... sorry, I forgot the `Equipment` relationship ... `public function collection_meters() { return $this->hasMany(CollectionMeter::class)->latest(); }` — Scotty G, Sep 19 '19 at 22:15
So, do you have a "one to many" relationship between equipment and CollectionMeter and another "one to many" relationship between collection and equipment? If so I will edit my answer — Alexandre Barbosa, Sep 19 '19 at 22:26
Yes, @alexandre-barbosa, that is exactly what I have ... `one to many` relationship between `equipment` and `CollectionMeter` and another `one to many` relationship between `collection` and `equipment` — Scotty G, Sep 20 '19 at 17:46
You can see from my posted `Results` (toArray()'s) that I can get ALL of the `collection_meters` or NONE of the `collection_meters` just not the ONLY ONE that matches the `Collection.id` (root) — Scotty G, Sep 20 '19 at 17:50
I need this somehow ... in the eager loaded scenario from above ... `WHERE collection_meters.equipment_id in (1, 5, 9, 10, 11, 12, 16) AND collection_meters.collection_id = collections.id` — Scotty G, Sep 20 '19 at 18:00

Laravel belongsToMany.belongsTo nested relationship query where child column = parent id

1 Answers1