使用join的构建一个mysql查询，以便在symfony中使用

DB struct:

DB description

One comapny can have review (0,1 or more) review always have reviews_item all reviews_item have one category

Problem

I need SQL query that makes list of 'company's with AVG value of reviews_items.value, i need it to set order by this value. Even if for some 'company' i dont have any 'reviews' and 'reviews_items' comapny should be in list with AVG == 0. it should select companies only with active == 1 and (if exists) review active==1

My slq query, dont work propertly:

SELECT 
    c.name,c.logo,c.url,
    AVG(ri.value) as avarage
FROM company c
LEFT JOIN reviews r ON r.company_id = c.id
LEFT JOIN reviews_items ri ON ri.review_id = r.id
LEFT JOIN category cat ON ri.category_id = cat.id
WHERE c.active = 1
GROUP BY c.id

I hope that my problem is clear :)

PS. Sorry if my language (english) is not so good :D

There was missing sth in WERE clausule (OR r.active IS NULL):

SELECT 
  c.name,c.logo,c.url,
  AVG(NULLIF(ri.value,0)) as avarage
FROM company c
 LEFT JOIN reviews r ON r.company_id = c.id
 LEFT JOIN reviews_items ri ON ri.review_id = r.id
 LEFT JOIN category cat ON ri.category_id = cat.id
WHERE c.active = 1 AND (r.active = 1 OR r.active IS NULL)
 GROUP BY c.id
ORDER BY avarage  DESC

Thanks for trying!

First of all, as you are not selecting, agrregating or checking anything from the category table you can omit the JOIN to table category.

After that you should rework your GROUP BY clause. Group by everything you select, that is not beeing aggregated: c.name, c.logo, c.url assuming, that they are always the same for all records belonging to one id.

Instead you could also aggregate the fields like

SELECT
    MIN(c.name), MIN(c.logo), MIN(c.URL)
    ...

and leave the GROUP BY as is.

Try this (can`t check ):

SELECT 
  c.name,c.logo,c.url,
  AVG(NULLIF(ri.value,0)) as avarage
FROM company c
 LEFT JOIN reviews r ON r.company_id = c.id
 LEFT JOIN reviews_items ri ON ri.review_id = r.id
 LEFT JOIN category cat ON ri.category_id = cat.id
WHERE c.active = 1 AND r.active = 1
 GROUP BY c.id
ORDER BY avarage  DESC