I tried to sort products from shop_products
table by pinned
column from shop_products_options
table:
$products = Shop\Product::with(['options' => function ($query) {
$query->orderBy('pinned', 'desc');
}])->paginate(5);
I set relation in Shop\Product model:
public function options()
{
return $this->hasOne('Shop\Options');
}
But products aren't sorted. I get a query that only works with shop_products_options
table.
SELECT * FROM `shop_products_options` WHERE `shop_products_options`.`product_id` in ('8', '9', '10', '11', '12') ORDER BY `pinned` DESC
How to fix it?
Eager loading uses separate queries so you need join for this:
$products = Shop\Product::join('shop_products_options as po', 'po.product_id', '=', 'products.id')
->orderBy('po.pinned', 'desc')
->select('products.*') // just to avoid fetching anything from joined table
->with('options') // if you need options data anyway
->paginate(5);
SELECT
clause is there in order to not appending joined columns to your Product
model.
edit: as per @alexw comment - you still can include columns from joined tables if you need them. You can add them to select
or call addSelect/selectRaw
etc.
You can't sort by related table column without the manually joining related table. Jarek answer is correct but this could be really awkward :
1.The first problem is that you need to worry about select.
->select('products.*')
reason: without select() id from shop_products_options can be selected and hydrated into Product model.
2.The second problem is that you need to worry about groupBy.
->groupBy('products .id');
reason: if the relation is HasOne and there are more than one shop_products_options for the product, the query will return more rows for products.
3.The third problem is that you need to change all other where clauses from :
->where('date', $date)
to
->where('products .date', $date)
reason: products and shop_products_options can both have "date" attribute and in that case without selecting attribute with table "ambiguous column" error will be thrown.
4.The fourth problem is that you are using table names(not models) and this is also bad and awkward.
->where('products.date', $date)
5.The fifth problem is that you need to worry about soft deletes for joined tables. If the shop_products_options is using SoftDeletes trait you must add :
->where('shop_products_options .deleted_at', '=', null)
All above problems are very frustrating and joining tables with eloquent can introduce to your code many problems. I created a package which takes care of all above problems and you can sort by relationship attribute with elegant way, for your case it would be :
$products = Shop\Product::orderByJoin('options.pinned', 'desc')->paginate(5);
For more info see https://github.com/fico7489/laravel-eloquent-join
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With