SQL extract only latest modified record AND correct days count IN 3 table join

This query needs to do 2 things it’s not doing at the moment:

1. 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”).

2. The following line: (EXTRACT(epoch FROM (SELECT (NOW() - date_cible)))/86400)::INT AS retard should return the number of days before or above the date_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.

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;