Creating an eloquent query using polymorphic tables

Hi, I've the next structure in DB:

plants (id, name, etc...)
plants_sunlights(id, plant_id, sunlight_id, ....)
sunlights(id, name,...)

According that I have the next relationships between 3 tables to perform a n..m relationship:

plants_sunlights.plant_id = plants.id and plants_sunlights.sunlight_id=sunlights.id

I have too 3 models: Plant, PlantSunlight and Sunlight, and I have a relationship method in Plants: sunlights() that uses return $this->hasManyThrough(...) but this doesn't work. How should be this method created? And the method inside Sunlight that relates with Plant model? And how about PlantSunlight model, is necessary to have this model?

Finally, the main reason of this post is that I'd like to perform the next query but using directly Eloquent methods (according that I have the relationship methods) BUT WITHOUT USIGN join Eloquent mehotds, directly models and with() and whereHas methods:

SELECT <several fields from plants> FROM plants p INNER JOIN plants_sunlights ps ON p.id=ps.plant_id INNER JOIN sunlights s ON s.id=ps.sunlight_id WHERE s.id IN (1, 2, 3)

LaryAI

3 years ago

Level 58

To create a relationship between the Plant and Sunlight models using the PlantSunlight pivot table, you can use a polymorphic many-to-many relationship. In the Plant model, define the sunlights relationship method like this:

public function sunlights()
{
    return $this->morphToMany(Sunlight::class, 'sunlightable', 'plants_sunlights');
}

In the Sunlight model, define the plants relationship method like this:

public function plants()
{
    return $this->morphedByMany(Plant::class, 'sunlightable', 'plants_sunlights');
}

Note that the morphToMany and morphedByMany methods are used instead of hasManyThrough because the relationship involves a polymorphic pivot table.

To perform the query using Eloquent methods, you can use the with and whereHas methods like this:

$plants = Plant::with('sunlights')
    ->whereHas('sunlights', function ($query) {
        $query->whereIn('sunlights.id', [1, 2, 3]);
    })
    ->get();

This will retrieve all plants that have at least one sunlight with an ID of 1, 2, or 3, and eager load the sunlights relationship for those plants. The resulting collection will include all fields from the plants table, as well as the related sunlights for each plant.

Please or to participate in this conversation.