php - 如何在 Laravel 5 的嵌套查询中使用 GroupBy？

Question

我在数据库中有四个表，即 Packages、Fixtures、Deals 和 Fixtures Deals。

下面是表结构的详细信息:

Packages: id, title
Fixtures: id, package_id, name
Deals: id, discound, description
Fixtures_Deal: id, fixture_id, deal_id, price

我需要获取套餐列表以及每个套餐在每个固定装置中提供的最低交易价格。

这是我在 phpMyAdmin 或 SQLYog 中运行的 mysql 查询，它运行良好，但在 Laravel 中它给我“p.title' isn't in GROUP BY”错误。

SELECT 
  p.title AS package_title,      
  tbl_min_value.min_price AS min_price 
FROM
  (SELECT 
    fixture_id,
    MIN(deal_price) AS min_price 
  FROM
    fixture_deal 
  GROUP BY fixture_id) AS tbl_min_value 
  JOIN fixtures AS f 
    ON f.id = tbl_min_value.fixture_id 
  RIGHT JOIN packages AS p 
    ON f.package_id = p.id 
GROUP BY p.id 

顺便说一句，我正在尝试通过在模型中使用以下方法来实现它:

return DB::statement('SELECT 
      p.title AS package_title,      
      tbl_min_value.min_price AS min_price 
    FROM
      (SELECT 
        fixture_id,
        MIN(deal_price) AS min_price 
      FROM
        fixture_deal 
      GROUP BY fixture_id) AS tbl_min_value 
      JOIN fixtures AS f 
        ON f.id = tbl_min_value.fixture_id 
      RIGHT JOIN packages AS p 
        ON f.package_id = p.id 
    GROUP BY p.id');

Answer 1

我通过使用 Eloquent Collection 的 mapWithKeys 方法找到了另一种解决方法。

https://laravel.com/docs/5.3/collections#method-mapwithkeys

所以我分别取了两个query的数据，然后将两个collections的key都映射到一个collection object中。

示例代码:

$packages = Package::getPackages();

$deal_min_price = Deal::getMinimumPrice();

$packages_with_price = $packages->mapWithKeys(function ($packages) use ($deal_min_price) {
    return $packages['price'] => $deal_min_price['min_price'];
});

您可以在mapWithKey 的闭包 中使用foreach 循环。