lundi 21 novembre 2016

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 



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire