Custom queries in Laravel’s Eloquent relationships

Laravel’s relationship classes and methods cover like 90% of my use cases.

But sometimes, there’s some weird distant relationship between a few models that doesn’t quite fit a HasMany or BelongsTo.

For instance, I’ve been working with an app that has a few models, some of which are distantly related.

The relationship looks like this:

Location -> Place <- Place Review <- Review Pins

It uses data from an API to populate a Location. The raw data from the API is stored in a places table. The location has a place_id.

Places also have Reviews, which also have a place_id.

And users can “pin” reviews. Pins have a location_id and a place_review_id.

The goal is to display only pinned reviews on the frontend while avoiding the N+1 problem, e.g. by calling $organization->locations()->with(['pinnedReviews']).

So how would we set up that pinnedReviews relation?

We can’t do a default HasManyThrough, since the models don’t actually depend on one another in one direction.

Also, what if we want to do some JOINs on the place_reviews table, to e.g. include when a review was pinned (i.e., join the review_pins.created_at column to the place_reviews records)?

And how can we keep the relationship in Eloquent?

Well thanks to a super helpful post on the Laracasts forum, I know how to do that now!

You can manually create relationships on your model with a custom query.

To reiterate:

Locations belong to Places, and Places have PlaceReviews, and PlaceReviews have ReviewPins. We’re trying to access reviews for the location with their pinned status, preferably with a HasMany on Locations. We can’t do a regular HasMany or HasManyThrough since Reviews belong to Locations indirectly. We want to be able to do something like this: $location->reviews->first()?->pinned_at.

Here’s what it looks like:

public function reviews(): HasMany
{
    $relation = $this->newHasMany(
        PlaceReview::query()
            ->fromSub(
                PlaceReview::query()
                    ->select(['locations.id as location_id', 'place_reviews.*', 'review_pins.created_at as pinned_at'])
                    ->join('locations', 'locations.place_id', '=', 'place_reviews.place_id')
                    ->leftJoin(
                        'review_pins',
                        fn (JoinClause $join) => $join
                            ->on('review_pins.place_review_id', 'place_reviews.id')
                            ->on('review_pins.location_id', 'locations.id')
                    ),
                'sub'
            )->withCasts(['sub.pinned_at' => 'immutable_datetime']),
        $this, // parent model -- $this HasMany reviews
        'sub.location_id', // The foreign key that references $this record
        $this->getKeyName(), // This model's ID column
    );

    return $relation;
}Code language: PHP (php)

The secret sauce here is the newHasMany method, which accepts an Eloquent query as its first argument.

That query starts at the related model, not the parent (Location, in this case).

Since we’ll want to add new where clauses to select only pinned reviews, the PlaceReview joins need to take place in a subquery. (If it wasn’t a subquery, doing $organizations->locations()->whereNotNull('pinned_at') would throw an exception, because pinned_at doesn’t exist in the place_reviews table. Instead, it’s a result of a JOIN.) (Side note: maybe this is a Postgres-specific thing, or maybe I’m just holding it wrong.)

That’s all there is to it! Now, I can write additional relations based on this one, like pinnedReviews:

public function pinnedReviews(): HasMany
{
    return $this->reviews()->whereNotNull('pinned_at');
}Code language: PHP (php)

Caveats

I should note I ended up going in a different direction for the above example.

It was too tricky to write this relationship method and give users the ability to write new reviews, etc…so I scrapped it and created a view in the database, along with a new model.

It definitely makes things sketchy if you’re interacting with that relationship extensively. Also I think future-me would look at that and have to figure out what the heck is going on. (That’s why I’m blogging about it, actually — I’d definitely forget this is possible otherwise.)

But as a nice, scope-able relation method, it works great!