雄辩地按关系字段执行订单 [英] Perform order by relationship field in Eloquent

问题描述

我想用Eloquent创建产品过滤器。

I want to create product filter with Eloquent.

我是这样开始的

$query = Product::whereHas('variants')
        ->with('variants')
        ->with('reviews')

$query = $this->addOrderConstraints($request, $query);

$products = $query->paginate(20);

其中

private function addOrderConstraints($request, $query)
{
    $order = $request->input('sort');

    if ($order === 'new') {
        $query->orderBy('products.created_at', 'DESC');
    }

    if ($order === 'price') {
        $query->orderBy('variants.price', 'ASC');
    }

    return $query;
}

但是，这行不通，因为Eloquent正在像这样执行此查询（来自Laravel DebugBar的信息）

However, that doesn't work, cause Eloquent is performing this query like this (information from Laravel DebugBar)

select count(*) as aggregate from `products` where exists 
(select * from `variants` where `products`.`id` = `variants`.`product_id`)

select * from `products` where exists 
(select * from `variants` where `products`.`id` = `variants`.`product_id`)

select * from `variants` where `variants`.`product_id` in ('29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48')

因此，当我尝试使用按价格排序时，这只是明显的错误

So when I try to use sorting by price it just obvious error

Unknown column 'variants.price' in 'order clause' (SQL: select * from 
`products` where exists (select * from `variants` where `products`.`id` =

变体。 product_id ）按变体排序。价格 asc限制20偏移量0）

variants.product_id) order by variants.price asc limit 20 offset 0)

那么是否可以与雄辩的人进行关系排序？

So is it possible to perform relationship ordering with Eloquent or not?

推荐答案

这将对子查询进行排序。不是第一个查询（产品查询）。

This will sort the subquery. Not the "first query (the product query)".

基本上，您的子查询将是：
从*中按价格在（....）中按product_id排序的变量中选择* ，而这不是您想要的，对吧？

Basically, your subquery will be: select * from variants where product_id in (....) order by price, and that is not what you want, right?

<?php 
// ...

$order = $request->sort;

$products = Product::whereHas('variants')->with(['reviews',  'variants' => function($query) use ($order) {
  if ($order == 'price') {
    $query->orderBy('price');
  }
}])->paginate(20);

如果要对产品+ /或变体进行排序，则需要使用join。

If you want to sort product +/or variant you need to use join.

$query = Product::select([
          'products.*',
          'variants.price',
          'variants.product_id'
        ])->join('variants', 'products.id', '=', 'variants.product_id');

if ($order === 'new') {
    $query->orderBy('products.created_at', 'DESC');
}

if ($order === 'price') {
    $query->orderBy('variants.price');
}

return $query->paginate(20);

这篇关于雄辩地按关系字段执行订单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！