Query a many to many relationship table

Good afternoon,

Basically I have 2 models related with many to many. Species that might or might not be present on differents zones of different maps.

Table especies

class Especie extends Model
{
    public function zona()
    {
        return $this->belongsToMany('App\Zona', 'especie_zona', 'especie_id', 'zona_id');
    }
}

Table zonas

id
name
mapa_id

class Zona extends Model
{
    public function especie()
    {
        return $this->belongsToMany('App\Especie', 'especie_zona', 'zona_id', 'especie_id');
    }

}

In the middle of both tables sits a table especie_zona (with about 100k records)

especie_id
zona_id

I'm trying to get all zones where a species is present.

What I have so far ...

On the MapsController

        $zonasMarcadas = Zona::whereHas('especie', function ($query) use ($id) {
            $query->where('id', '=', $id);
        })->get();

which generates the query

select * from `zonas` where exists (select * from `especies` inner join `especie_zona` on `especies`.`id` = `especie_zona`.`especie_id` where `zonas`.`id` = `especie_zona`.`zona_id` and `id` = '3041')

that takes about 60 seconds to return results when I realy want is the query

SELECT * FROM especie_zona WHERE especie_id = 85

that takes about 0.125 seconds to return 400 results.

What am I doing wrong ? Any help greatly appreciated!

lostdreamer_nl

7 years ago

Best Answer

Level 53

Not really doing anything wrong here, it's just the basics of Laravel, hitting a very big pile of data.

whereHas is checking the existence of the relationship, which is done with the query:

select * from `especies` inner join `especie_zona` on `especies`.`id` = `especie_zona`.`especie_id` where `zonas`.`id` = `especie_zona`.`zona_id` and `id` = '3041'

So especies * especie_zona ~= 100.000 * 400 = 40.000.000 rows to check after this join.

It might get a lot better if you put in indexing on those 2 fields (if there aren't yet):

especie_zona.especie_id
especie_zona.zona_id

If there was already an index, and it's still slow, you might want to do the query a bit differently by doing 2 single select queries instead of the subquery with join:

$zoneIds = \DB::table('especie_zona')->select('zona_id')->where('especie_id', $especieId)->get()->pluck('zona_id');

$zonasMarcadas = Zona::whereIn('id', $zoneIds)->get();

If that works ok, you could put it in a query scope for reusability:

// Zona.php
public static method scopeHavingEspecie($query, $especieId)  
{
    $zoneIds = \DB::table('especie_zona')->select('zona_id')->where('especie_id', $especieId)->get()->pluck('zona_id');
    return $query->whereIn('id', $zoneIds);
}

// usage:

$zonasMarcadas = Zona::havingEspecie(85)->get();

1 like

Penaf

7 years ago

Level 1

@LOSTDREAMER_NL - Thanks mate! That worked out perfectly and the scope added a nice level of cleanliness to the controller. Cheers!

Please or to participate in this conversation.