How to use GroupBy in a subquery in Laravel 5?

Question

I have four tables in the database, that is, packages, fixtures, offers and offers.

Here are the details of the table structure:

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

I need to get a list of packages along with the minimum transaction price offered in each device for each package.

Here is the mysql query that I run in phpMyAdmin or SQLYog and it works fine, but in Laravel it gives me the error "p.title" not 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 

By the way, I am trying to achieve this using the following method in the 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 1

I found another way using the Eloquent Collection method mapWithKeys.

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

, .

:

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

foreach mapWithKey closure.

Answer 2

, , , MySQL 5.7 SQL99. , SQL ONLY_FULL_GROUP_BY, , GROUP BY. ?

, p.title tbl_min_value.min_price , , :

GROUP BY p.id, p.title, tbl_min_value.min_price

: " ?". , GROUP BY, "" , , , . , , , ​​ MIN() MAX(), , , , .

, , , :

# Run this query and copy the values
SELECT @@sql_mode;

# Remove "ONLY_FULL_GROUP_BY" and paste it in here where the XXX is
SET sql_mode = 'XXX';

, my.cnf [mysqld], , XXX - SELECT @@sql_mode; ONLY_FULL_GROUP_BY:

[mysqld]
sql_mode=XXX 

Answer 3

, SQL- phpMyAdmin, Laravel 5.3. db , ?

By the way, I tried following SQL and it works fine in Laravel 5.2:

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
    fixtures_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