We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

How to convert this RAW SQL to Phalcon query builder?

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;
    }

I'm not sure a query with subqueries can be converted to PHQL. My question is why you want to? Usually the QueryBuilder is used to either a) simplify creating a query or b) allowing a query to be built programatically. If you've already written the query in raw SQL I can't see any reason to convert it to PHQL.