Transforming SQL into QueryBuilder #6790

New Issue

admin · 2026-01-22T15:38:44+01:00

admin commented

2026-01-22 15:38:44 +01:00

Originally created by @bglevon on GitHub (Jul 23, 2021).

Hello,

I have an SQL query that I need to do in QueryBuilder, but I can't get it because of multiple leftJoin.

Here is my SQL query

SELECT r.lot,DATE_FORMAT(r.datetime_creation,'%d-%m-%Y %H:%i') as date_creation, u.societe, j_3.date_cloture , COUNT(r.id_reparation) as nb_element, j_2.nb_element_total
, IF(j_1.nb_element_cloture IS NULL,0,CONVERT((j_1.nb_element_cloture * 100 / j_2.nb_element_total),int)) as progression
FROM utilisateur u, reparation r
LEFT JOIN (SELECT COUNT(*) as nb_element_cloture, lot FROM reparation WHERE fk_reparation_statut_id IN (5,10,11,14,15,23,24,27)  GROUP BY lot) j_1 ON j_1.lot = r.lot
LEFT JOIN (SELECT COUNT(*) as nb_element_total, lot FROM reparation WHERE fk_reparation_statut_id > 0 GROUP BY lot) j_2 ON j_2.lot = r.lot
LEFT JOIN (SELECT DATE_FOreparationT(datetime_cloture ,'%d-%m-%Y %H:%i') as date_cloture,lot
              FROM reparation
              WHERE fk_reparation_statut_id IN (5,10,11,14,15,23,24,27)
              GROUP BY lot
             ORDER BY datetime_cloture DESC) j_3 ON j_3.lot = r.lot
  WHERE r.fk_reparation_statut_id IN(1, 16)
    AND r.revendeur = u.id_utilisateur

Is it possible to put the SELECT in the leftJoin with QueryBuilder? and if so how should I proceed?

Thank you!

Originally created by @bglevon on GitHub (Jul 23, 2021). Hello, I have an SQL query that I need to do in QueryBuilder, but I can't get it because of multiple leftJoin. Here is my SQL query ``` SELECT r.lot,DATE_FORMAT(r.datetime_creation,'%d-%m-%Y %H:%i') as date_creation, u.societe, j_3.date_cloture , COUNT(r.id_reparation) as nb_element, j_2.nb_element_total , IF(j_1.nb_element_cloture IS NULL,0,CONVERT((j_1.nb_element_cloture * 100 / j_2.nb_element_total),int)) as progression FROM utilisateur u, reparation r LEFT JOIN (SELECT COUNT(*) as nb_element_cloture, lot FROM reparation WHERE fk_reparation_statut_id IN (5,10,11,14,15,23,24,27) GROUP BY lot) j_1 ON j_1.lot = r.lot LEFT JOIN (SELECT COUNT(*) as nb_element_total, lot FROM reparation WHERE fk_reparation_statut_id > 0 GROUP BY lot) j_2 ON j_2.lot = r.lot LEFT JOIN (SELECT DATE_FOreparationT(datetime_cloture ,'%d-%m-%Y %H:%i') as date_cloture,lot FROM reparation WHERE fk_reparation_statut_id IN (5,10,11,14,15,23,24,27) GROUP BY lot ORDER BY datetime_cloture DESC) j_3 ON j_3.lot = r.lot WHERE r.fk_reparation_statut_id IN(1, 16) AND r.revendeur = u.id_utilisateur ``` Is it possible to put the SELECT in the leftJoin with QueryBuilder? and if so how should I proceed? Thank you!

admin closed this issue

2026-01-22 15:38:44 +01:00

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#6790