首页 文章

在Eloquent中按关系字段执行排序

提问于
浏览 次
2

我想用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的信息）

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')

等等

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

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

variants . product_id ）由 variants 订购 . price asc limit 20 offset 0）

那么是否可以与Eloquent进行关系排序？

laravel eloquent

2 回答

  • 0

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

    基本上，你的子查询将是： select * from variants where product_id in (....) order by price ，这不是你想要的，对吧？

    <?php 
// ...

$order = $request->sort;

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

    如果要对产品/或变体进行排序，则需要使用连接 .

    $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);
    回复于
  • 5

    你可以在whereHas进行排序

    // Retrieve all products with at least one variant ordered by price

$query = Product::whereHas('variants', function ($query) use ($request) {
    if ($request->input('sort') == 'price') {
        $query->orderBy('variants.price', 'ASC');
    }
})
->with('reviews')

    并从 addOrderConstraints 方法中删除变体排序

    回复于

相关问题