Laravel Eloquent equivalent of a QueryBuilder query

Question

I have a Laravel 7 project and this database structure:

 races      participants      bibs      coords
-------    --------------    ------    --------
 id         id                id        id
            race_id                     bib_id
            bib_id                      [...]

The relations are:

1 race        =>  N participants  ( races.id = participants.race_id )
1 participant =>  1 bib           ( participants.bib_id = bibs.id )
1 bib         =>  N coords        ( bibs.id = coords.bib_id )

So it also means that: participants.bib_id = coords.bib_id

I want to get all the Coords related to a specific Race. The way I did is using query builder like this:

class Race extends Model
{
    public function getCoords()
    {
        return Coord::join('participants', 'participants.bib_id', '=', 'coords.bib_id')
            ->where('participants.race_id', $this->id)->select('coords.*')->get();
    }
}

Here I can do that:

$coords = Race::find(1)->getCoords();

It's working as expected, but I'm looking for a way to do it using Eloquent so that it would be easier to chain with more relations and stuff.
I tried a lot of things with belongsToMany and hasManyThrough but nothing worked. I wonder if that is even possible?

score 1 · Answer 1 · answered Jun 16 '20 at 17:23

1

// Race
public function participants()
{
    return $this->hasMany(Participant::class);
}

// Participant
public function bib()
{
    return $this->belongsTo(Bib::class);
}
public function race()
{
    return $this->belongsTo(Race::class);
}

// Bib
public function coords()
{
    return $this->hasMany(Coord::class);
}

// Coord
public function bib()
{
    return $this->belongsTo(Bib::class);
}

Now, what you want is to find coords for a given race.

$race = Race::find($id)->with('participants.bib.coords');

Now, to extract coords from it.

$coords = $race->participants->map(function($participant){
              return $participant->bib->coords;
          });

Keep me posted in the comments below. Cheers!

answered Jun 16 '20 at 17:23

Digvijay

7,836
3
32
53

I needed to write `Race::with('participants.bib.coords')->find(1)` instead of `Race::find($id)->with('participants.bib.coords')`. But that's pretty cool tho. – Marc Jun 17 '20 at 07:25
Do you think this solution can lead to performance issues? Imagine something like 500 participants with 100 000 coords (as a total). The request to get the coords will be used on a Openlayers map which is intended to have at peek of around 10k users. I don't have any clue of what could be the charge for the web and database servers. – Marc Jun 17 '20 at 07:26
Also, on this map there will be another http request to refresh the last "coords" from all participants of the race (there will be a filter on a timestamp column). Something like an AJAX call every minute. If there is around 10k users watching the map, do you think it can be a problem with the way you propose? – Marc Jun 17 '20 at 07:34
1

Yes, currently you are selecting all the columns from each relation. Please go through this link (https://stackoverflow.com/a/60015685/5326191). This will walk you through optimization. – Digvijay Jun 17 '20 at 07:35
Ok, If I follow what you wrote, I should consider keeping my query builder function for this kind of operation because it reduces the number of SQL queries performed (from my tests => reduces from 4 to 1 [but with a JOIN]). And I should do it along with `select()` in order to only take the columns I need. Is that right? – Marc Jun 17 '20 at 07:42

Laravel Eloquent equivalent of a QueryBuilder query

1 Answers1