Yii how to make a complex database query?

Question

MySQL

The essence is as follows
There is a Camps table
-id_camp
-address
-visits_limits
and a Visits table -id -time_v
-id_camp And so you need to select the desired row from the Camps table by address but the limit of visits is more than visits in the visits table I made a normal query almost) does not work only in happens if the visits table is empty at all Tell me how to make a request in the usual form and, most importantly, in the yii 1 syntax! SELECT * FROM `camps` LEFT JOIN visits on camps.id_camp = visits.id_camp WHERE address = '$address' AND visit_limit > (SELECT count(id) FROM visits WHERE visits.id_camp = camps.id_camp GROUP BY visits.id_camp) GROUP BY camps.id_camp

Answer 1

SELECT * FROM `camps` LEFT JOIN visits on camps.id_camp = visits.id_camp WHERE address = '$address' AND visit_limit > COALESCE((SELECT count(id) FROM visits WHERE visits.id_camp = camps.id_camp GROUP BY visits.id_camp),0) GROUP BY camps.id_camp