How can I convert the below RAQ SQL query to phalcon query builder?
RAW SQL Query:
SELECT es.id AS id, es.title AS title
, coalesce(eus.counts, 0) as counts
, eus.date_created
FROM eb_evaluation_symptom AS es
LEFT JOIN
( select eb_evaluation_symptom_id
, COUNT(*) AS counts
, min(date_created) AS date_created -- or MAX?
from eb_evaluation_user_symptom
GROUP BY eb_evaluation_symptom_id
) AS eus
ON es.id = eus.eb_evaluation_symptom_id
WHERE es.is_active = 1 ;
Here is what I tried:
public static function getAllActiveEvaluationSymptomsWithNameForDataTable(){
$queryBuilder = new Builder();
$queryBuilder
->from(array('es' => static::class))
->leftJoin('Cx\EbFront\Models\Evaluation\EbEvaluationUserSymptom', 'es.id = eus.eb_evaluation_symptom_id','eus')
->columns('es.id, es.title, coalesce(eus.counts, 0) as counts, eus.date_created')
->where('es.is_active = 1')
->groupBy('eb_evaluation_symptom_id');
return $queryBuilder;
}