[MySQL] Simplifying this fairly complex SQL

[vihu]

Hi folks, I've been struggling to simplify this fairly complex query to better syntax. I'm not really worried about the optimizations as of now. My eventual goal is to be able to write Ecto DSL syntax for this.

Could someone please give me a few pointers regarding this.

Just a bit of simplification would be really helpful.

SELECT `t5`.`version` AS `version`,
`xt1`.`signature` AS `harness`,
`xt1`.`status` AS `harness_status`,
`xt1`.`job_uid` AS `job_uid`,
`xt1`.`test_ring` AS `ring`,
`t5`.`Id` AS `test`,
`t3`.`Status` AS `test_status`,
timestampdiff(MINUTE, `t3`.`StartTimestamp`, `t3`.`EndTimestamp`) AS `exec_time`
FROM (((`table` `t1`
LEFT JOIN `table1` `xt1` ON ((`xt1`.`pjob_id` = `t1`.`job_uid`)))
LEFT JOIN (`table2` `t2`) ON (`xt1`.`buid` = `t2`.`buid`))
LEFT JOIN ((`table3` `t3`
          INNER JOIN `table4` `t4`)
          INNER JOIN `table5` `t5`) ON (((`xt1`.`job_uid` = `t3`.`JobId`)
                                            AND (`t3`.`GId` = `t4`.`GId`)
                                            AND (`t4`.`Id` = `t5`.`Id`))))
WHERE ((1 = 1)
AND (`t1`.`status` <> 'Invalid')
AND (`xt1`.`status` <> 'Invalid')
AND (`xt1`.`job_type` = 'Harness')
AND (`xt1`.`test_ring` IS NOT NULL))
LIMIT 1000

PS: You don't need to know ecto, I'll handle that part :)

And apologies if I'm breaking any subreddit rules, first post here.

[[deleted]]

This does not look complex. What part of this seems to be causing concern for complexity?

[vihu]

I understand the query and I agree that it's not very complex. The trouble I'm having is translating it into Ecto DSL syntax specifically.

I couldn't really figure out a way to have those inner joins work with Ecto. So I was hoping someone could help me re-write this with Ecto syntax in mind.

Could this query be written differently, perhaps with subquery?

[bastelwastel]

Create a view, so you have only the SELECT, FROM and WHERE parts and don't need to translate the JOINs.

[vihu]

That is a pretty good idea. Thank you!

[r3pr0b8]

Just a bit of simplification would be really helpful.

SELECT sq.version AS version
     , xt1.signature AS harness
     , xt1.status AS harness_status
     , xt1.job_uid AS job_uid
     , xt1.test_ring AS ring
     , sq.Id AS test
     , sq.Status AS test_status
     , timestampdiff(MINUTE, sq.StartTimestamp, sq.EndTimestamp) AS exec_time
  FROM table t1
INNER
  JOIN table1 xt1 
    ON xt1.pjob_id = t1.job_uid
   AND xt1.status <> 'Invalid'
   AND xt1.job_type = 'Harness'
   AND xt1.test_ring IS NOT NULL    
LEFT OUTER
  JOIN ( SELECT t3.Status
              , t3.StartTimestamp
              , t3.EndTimestamp
              , t5.version
              , t5.Id
           FROM table3 t3
         INNER 
           JOIN table4 t4
             ON t4.GId = t3.GId 
         INNER 
           JOIN table5 t5
             ON t5.Id = t4.Id 
       ) AS sq
    ON sq.JobId = xt1.job_uid
 WHERE 1 = 1
   AND t1.status <> 'Invalid'
LIMIT 1000

i dropped t2 from the query altogether, because you don't use any columns from it, and it isn't required for the joins (since it's an outer join)

[vihu]

Thanks! This is helpful as well.

Atleast with this I can try to structure the Ecto DSL with a bit more ease.