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

Question

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

Answer 1

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!