This query needs to do 2 things it’s not doing at the moment:
If a h.name is strictly equal to other h.name, it should only select the latest record (where the h.modify_at is the most recent). Currently it selects all “h”).
The following line:
(EXTRACT(epoch FROM (SELECT (NOW() - date_cible)))/86400)::INT AS retard
should return the number of days before or above thedate_cible
in number of days. At the moment it does give an int as number of days, however it’s incorrect.
I’m basically being onboarded in a project nearly finished and have to work with entities as they are, any help is greatly appreciated.
SELECT h.target_date, h.flux_id, h.modify_at, f.description, d.nom, d.date_creation, f.ordre, f.name, COALESCE(h.target_date,d.deadline) AS date_cible, (EXTRACT(epoch FROM (SELECT (NOW() - date_cible)))/86400)::INT AS retard FROM public.flux_historique AS h LEFT JOIN public.flux as f INNER JOIN public.document as d ON d.id = :docId ON h.flux_id = f.id WHERE f.id_system_droit_id = d.id_system_droit_id
:docId is a Symfony parameter.
Sample data from this query:
"2019-12-10 10:39:59" 15 "dzd" "Rapport d'Evaluation ANTE" "2019-11-15 14:05:51" 1 "Lecture" "2019-12-10 10:39:59" 2179 "2019-12-22 10:39:59" 121 "Naaaaaaan" "Rapport d'Evaluation ANTE" "2019-11-15 14:05:51" 2 "Relecture" "2019-12-22 10:39:59" 2179 "2019-12-23 10:39:59" 121 "Naaaaaaan" "Rapport d'Evaluation ANTE" "2019-11-15 14:05:51" 2 "Relecture" "2019-12-23 10:39:59" 2179 "2019-12-24 10:39:59" 121 "Naaaaaaan" "Rapport d'Evaluation ANTE" "2019-11-15 14:05:51" 2 "Relecture" "2019-12-24 10:39:59" 2179 "2019-12-25 10:39:59" 121 "Naaaaaaan" "Rapport d'Evaluation ANTE" "2019-11-15 14:05:51" 2 "Relecture" "2019-12-25 10:39:59" 2179 "2019-12-26 10:39:59" 121 "Naaaaaaan" "Rapport d'Evaluation ANTE" "2019-11-15 14:05:51" 2 "Relecture" "2019-12-26 10:39:59" 2179 "2019-12-27 10:39:59" 122 "non" "Rapport d'Evaluation ANTE" "2019-11-15 14:05:51" 3 "Introduction" "2019-12-27 10:39:59" 2179 "2019-12-28 10:39:59" 122 "non" "Rapport d'Evaluation ANTE" "2019-11-15 14:05:51" 3 "Introduction" "2019-12-28 10:39:59" 2179 "2019-12-29 10:39:59" 122 "non" "Rapport d'Evaluation ANTE" "2019-11-15 14:05:51" 3 "Introduction" "2019-12-29 10:39:59" 2179 "2019-12-30 10:39:59" 122 "non" "Rapport d'Evaluation ANTE" "2019-11-15 14:05:51" 3 "Introduction" "2019-12-30 10:39:59" 2179
Sample correct output:
1 "Lecture" "dzd" "2020-02-03 00:00:00" 2179 2 "Relecture" "Naaaaaaan" "2020-02-03 00:00:00" 2179 3 "Introduction" "non" "2020-02-03 00:00:00" 2179 4 "Introducure" "puis si" "2020-02-03 00:00:00" 2179
So essentially I need the correct output, with all the column requested from my own statement.
Advertisement
Answer
You need to fix your JOIN
conditions. Learn to interleave the ON
clauses and to put the conditions in the correct ON
clause. If you want no filtering (as suggested by the LEFT JOIN
), you need to use it consistently.
Then to get one row, use DISTINCT ON
:
SELECT DISTINCT ON (h.flux_id) h.target_date, h.flux_id, h.modify_at, f.description, d.nom, d.date_creation, f.ordre, f.name, COALESCE(h.target_date, d.deadline) AS date_cible, (EXTRACT(epoch FROM (SELECT (NOW() - date_cible)))/86400)::INT AS retard FROM public.flux_historique h LEFT JOIN public.flux f ON h.flux_id = f.id LEFT JOIN public.document as d ON d.id_system_droit_id = f.id_system_droit_id AND d.id = :docId ORDER BY h.flux_id, h.modify_at DESC;