Salman Mustafa Salman Mustafa - 13 days ago 7
PHP Question

How to use GroupBy in nested query in Laravel 5?

I have four tables in the database i.e. Packages, Fixtures, Deals and Fixtures Deals.

Here are the table structure details:

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


I need to get the packages list along with the minimum deal price that offers in each fixture for each package.

Here is the mysql query I run in phpMyAdmin or SQLYog, and it works perfectly, but in Laravel it gives me "p.title' isn't in GROUP BY" error.

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


By the way, I'm trying to achieve it by using the following method in model:

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

I have found another way around by using Eloquent Collection's mapWithKeys method.

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

So I fetched the data of two queries separately and map both the collections with keys into one collection object.

Sample Code:

$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'];
});

You may either use foreach loop within the mapWithKey's closure.