Access Parent Model/Retalation Columns During Eager Loading ("with" method of Eloquent ORM)

[vopolonc]

I'm not sure how silly question itself but:
Currently, I've not found that Eloquent does support directly accessing parent model columns within the with method for eager loading. This limitation makes it challenging to apply conditions on related relation based on attributes of the parent model.

For example:

class Order extends Model
{
    public function products()
    {
        return $this->hasMany(OrdersProducts::class, 'order_id');
    }

    public function historicalProducts()
    {
        return $this->products()
                    ->whereColumn('orders_products.created_at', '<', 'orders.created_at');
    }
}

...

class OrdersProducts extends Model
{
    public function order()
    {
        return $this->belongsTo(Order::class, 'order_id');
    }
}

...

// Or without `historicalProducts` relation, just eager loading with some condition
Order::query()
    ->withWhereHas('products', fn ($query) => $query->whereColumn('orders.something', '=', 'products.something')

If anyone had a similar problem and solved it, I would be very grateful!

[marius-mcp]

The relations being eager loaded you don't have for each relation the value of the parent column.

[marius-mcp]

I don't see an eager load solution to this... The join solution that you spoke about is the solution. Basically you need relations declared via 2 foreign keys to solve your problem.

I have such a case but it works only via pivot:

    return $this->newHasOneThrough(
        ($d = (new Related()))->newQuery(),
        $this,
        ($o = (new Pivot()))->setTable($o->getTable() . ' as o'),
        'o.model_id',
        'related_col',
        'id', // this primary
        'o.pivot_related_col'
    )->where('o.pivot_related_col', '<>', '')->whereRaw(
        $o->getConnection()->getTablePrefix() . 'o.col2 = ' .
            $d->getConnection()->getTablePrefix() . $d->getTable() . '.col2'
    );

[marius-mcp]

You can try something similar also for your relation and if you succeed then you will have the eager loaded eloquent way. But because the eager load does not include the current model table, you can't put conditions on it. As you can see I put conditions on pivot and related, not on current table.

[vopolonc]

@marius-mcp Nice example with pivot, thanks. I've achieved the needed result for me by using ->subQuery() and json_agg. As a con of this solution, you need to manually decode the aggregated column products. Simply it looks like that:

select
    "orders".*,
    (
        select
            json_agg(products.*)
        from
            "products"
        where
          "products"."order_id" = "orders"."id"
          and "orders"."created_at" > "orders_products"."created_at"
    ) as "products"
from
    "orders"

It's mostly the same as withCount but with json_agg in role of aggregator function

[marius-mcp]

@vopolonc so you used something like

Order::newQuery()->select([
    0 => 'orders.*',
    'products' => function (QueryBuilder $query): void {
        $query->selectRaw('json_agg(products.*))->from('products')->where([
            "products.order_id", '=', "orders.id",
            "orders.created_at", '>', "orders_products.created_at"
        ]);
    }
])->where(...)->get();

?

I think you should put the solution as it represents getting relations that involve only 2 tables by using more than one foreign keys.

[vopolonc]

@marius-mcp I cannot share real code, it's a bit complex and there are a few additional joins, but to simplify it's pretty similar to what you've shown above:

// Context: OrderRepository::class

public function subQueryProducts(Builder $query, string $aggregator = 'json_agg', string $aggregateBy = 'products.name'): Builder
{
    return $query->selectRaw(sprintf('%s(%s)', $aggregator, $aggregateBy))
        ->from('products')
        ->whereColumn('orders.id', 'products.order_id')
        ->whereNotNull('products.activated_at')
        ->where('orders.created_at', '>', DB::raw('products.activated_at'))
        ->where(fn (Builder $query) => $query
            ->where('orders.created_at', '<', DB::raw('products.ends_at'))
            ->orWhereNull('products.ends_at')
        );
}

[henzeb]

Wouldn't it be "product.order" as relationship key and then the column names as usual?

[marius-mcp]

When listing operations, this is how an eager load relation query looks like:

    "2.86 ms, sql: select `clients`.* from `clients` where `clients`.`id` in (23190, 37855, 45350, 68378, 75282, 91963, 100405, 115907, 129449, 166792)",

Notice how operations table is not in the query.

operation has a relations with client

public function client(): BelongsTo
{
    return $this->belongsTo(Client::class, 'client_id', 'id');
}

client has

public function operations(): HasMany
{
    return $this->hasMany(Operation::class, 'client_id', 'id');
}

update

Viceversa:
"21.32 ms, sql: select operations.* from operations where operations.client_id in (199993, 199994, 199995, 199996, 199997, 199998, 199999, 200000, 200001, 200002)"

Notice how clients are not in the query.

[vopolonc]

Yea, I know how it works under Laravel's hood, since I already spent a decent amount of time trying to understand "how to have "clients" in a query". Thanks for answering, I guess we can mark this thread as "resolved" considering that there's no way to achieve what I've initially sought for 🙏

[henzeb]

you should use whereHas.('orders_products', fn($query)=>$query->...);

[marius-mcp]

@henzeb that will also condition the orders retrieved to force them to have at least 1 relation.

[vopolonc]

@henzeb The goal is to achive a eager-loading, not just a query the "order_products". So 'whereHas' will work, but for eager loading you'd rather use 'withWhereHas' which won't provide you access to the parent table