CODEWITHSUNDEEP
laravel
Bob
Bob

Reputation: 8714

Laravel eloquent filter on hasMany relationship

I am trying to filter on the field which has many relationships.

This is how my query looks:

Order::with(['fullOrderItems' => function($query) use ($branch) {
                $query
                    ->where('branch', $branch->key);
            }])
            ->orderBy('dateCreated')
            ->toSql();

The query looks like this once outputted:

select * from `Order` where `stage` <> ? order by `dateCreated` asc

And it seems like the query doesn't even consider the nested query for filtering the branch.

My relationship in Order class is defined like this:

/*
*
* @return HasMany|null
*/
public function fullOrderItemss() : ?HasMany
{
    return $this->hasMany(OrderItems::class, 'order', 'key');
}

Do you see what am I doing wrong here? How can I get the full query (including nested condition)?

Upvotes: 1

Views: 907

Answers (1)

N69S
N69S

Reputation: 17206

To filter the Entity order with a relation you need to use whereHas

Order::whereHas('fullOrderItems', function($query) use ($branch) {
                $query
                    ->where('branch', $branch->key);
            })
            ->with(['fullOrderItems' => function($query) use ($branch) {
                $query
                    ->where('branch', $branch->key);
            }])
            ->orderBy('dateCreated')
            ->get();

You can for example get the order filtered by branch id but get all the fullOrderItems (ingore the branch id) of those fullOrderItems like this

Order::whereHas('fullOrderItems', function($query) use ($branch) {
                $query
                    ->where('branch', $branch->key);
            })
            ->with('fullOrderItems')
            ->orderBy('dateCreated')
            ->get();

This last example will make it simpler to understand the difference between the two filters.

For why the with condition doesnt show on the query:

it is used on a second unique query that fetchs the relation using the ids of orders retrieved in the first query. that way you get the orders, each with their respective fullOrderItems with only two queries.

Upvotes: 1

Related Questions