Level 70
I think you can simply achieve this via relationships. Correct me if I am wrong.
Route::with('orders.products')
->where('route_id', $id)->firstOrFail();
Recommendation: https://laravel.com/docs/9.x/eloquent-relationships
3 likes
Jun 13, 2022
4
Level 1
Inner relation where query
Hello,
I'm fresh laravel developer and have problem with querying inner relation data.
I want to get route with orders and products for this route, same order sometimes can be assigned to different route, so order and product have to have route_id and i need to have two orders for different routes.
Structure of db looks like this:
routes
- route_id
orders
- order_id
- route_id
products
- order_id
- route_id
Relations:
- Route::hasMany(Order::class) -> relation name orders()
- Order::hasMany(Product::class) -> relation name products()
I tried with this query which return route with orders and products, but doesn't use where 'products.route_id':
$id = 105;
Route::with('orders.products')
->whereHas('orders.products', function($q) use ($request) {
$q->where('products.route_id', $id);
})
->where('route_id', $id)->firstOrFail();
#Result from toSql()
select
*
from
`routes`
where
exists (
select
*
from
`orders`
where
`routes`.`route_id` = `orders`.`route_id`
and exists (
select
*
from
`products`
where
`orders`.`order_id` = `products`.`order_id`
and `products`.`route_id` = 105
)
)
and `route_id` = 105
Am i missing something, maybe using wrong relation type? Any help will be appreciated
Level 1
@tisuchi thanks for answer.
Unfortunately when i run this query products results are from different routes, not only this one passed as $id argument.
Simplified result:
{
"id": 22,
"route_id": 1533,
"orders": [
{
"id": 265,
"route_id": 1533,
"order_id": 398256890,
"products": [
{
"id": 2233,
"order_id": 398256890,
"route_id": 1533
}
]
},
{
"id": 270,
"route_id": 1533,
"order_id": 401414687,
"products": [
{
"id": 2239,
"order_id": 401414687,
"route_id": 1534,
}
]
}
]
}
My relation in Order model looks like this.
public function products()
{
return $this->hasMany(Product::class, 'order_id', 'order_id');
}
If i could map Order products via route_id it then should return correct values, ie.
public function products()
{
return $this->hasMany(Product::class, 'order_id', 'order_id', 'route_id', 'route_id');
}
I think i'm missing something, but really don't know what.
Level 5
@przemokon So the Order model connects with the Product model with 2 columns (order_id & route_id) not 1?
In this case you cannot use regular relations, you should use this package https://github.com/topclaudy/compoships which allows you to make relations with more than one columns
EDIT: code sample
Order Model
use Awobaz\Compoships\Compoships;
use Awobaz\Compoships\Database\Eloquent\Relations\HasMany as ComposhipHasMany;
class Order extends Model
{
use Compoships;
public function products(): ComposhipHasMany
{
return $this->hasMany(Product::class, ['item_id', 'route_id'], ['item_id', 'route_id']);
}
}
Product Model
use Awobaz\Compoships\Compoships;
use Awobaz\Compoships\Database\Eloquent\Relations\BelongsTo as ComposhipBelongsTo
class Product extends Model
{
public function order(): ComposhipBelongsTo
{
return $this->belongsTo(Order::class, [order_id', 'route_id'], ['order_id', 'route_id']);
}
}
Eager Load
Route::with('orders.products')->where('route_id', $id)->firstOrFail();
1 like
Level 1
@achatzi thank you, i will check this solution and come back with conclusions
update
This solution works perfectly for my problem, thanks for sharing it
Please or to participate in this conversation.