2023-05-05 honeysql | Clojure Slack Archive

honeysql

2023-05-05T11:21:05.725209Z

Our codebase is in honeysql 1.x version and I am trying to get this query working

SELECT *
FROM
    schemes s
    LEFT JOIN progress p ON p.scheme_id = s.id
    AND s.term = 1
    AND p.date > '2023-04-27'
WHERE
    p.driver_id = '123'
    ORDER BY p.date DESC
    LIMIT 10 OFFSET 0;

and till left join it’s working as below:

(-> (sqle/select* [:*])
    (helpers/from [:schemes :s])
    (helpers/left-join [:progress :p] [:= :p.scheme-id :s.id])
    (honeysql.core/format))

but I am not sure how to add the

AND s.term = 1
AND p.date > '2023-04-27'

part in honeysql(I want it to be there instead of where condition because it optimizes the query by filtering records at join time).

p-himik 2023-05-05T11:34:19.455189Z

Wrap [:= ...] in [:and ...]. Also, are you sure about that "it optimizes the query" statement? I would be rather surprised if that's the case since it seems to be a very obvious optimization that a query planner can do on its own. Have you run EXPLAIN ANALYZE on that query and compared it to the one where the conditions are in the WHERE clause?

2023-05-05T11:45:49.734329Z

tried placing [:and [:= :s.term 1]] inside the left-join as well as after it but it’s not working:

(-> (sqle/select* [:*])
    (sqlh/from [:schemes :s])
    (sqlh/left-join [:progress :p] [:= :p.scheme-id :s.id])
    ([:and [:= :s.term 1]])
    (sqlc/format))

is giving ; Key must be integer error.

2023-05-05T11:46:29.746459Z

And yes, the query with AND statements is giving less execution time on average than the query with “WHERE” conditions. It was suggested by a colleague and I was also surprised(It’s 30-40% improvement) on running it with explain analyze

p-himik 2023-05-05T12:04:13.472399Z

I meant to wrap the existing := in :and: (sqlh/left-join [...] [:and [:= ...] [:= ...]]).

p-himik 2023-05-05T12:09:03.142799Z

> And yes, the query with AND statements is giving less execution time on average Intersting. Are there differences in actual plans? On my end, I can only see "hash join" vs "hash right join" and they don't result in any difference time-wise.

p-himik 2023-05-05T12:10:44.589389Z

Oh, hold on - in your case it's actually quite important because it's a left join and not an inner join. Putting the conditions in a different place will result in different results. Didn't happen on my end because there were always entries in the joined table for any row in the main table.

p-himik 2023-05-05T12:11:08.956839Z

So, there shouldn't be a difference if you have an inner join - both result- and performance-wise.

👍 1

2023-05-05T16:28:22.077079Z

(-> (sqle/select* [:*])
    (sqlh/from [:schemes :s])
    (sqlh/left-join [:progress :p] [:= :p.scheme-id :s.id] [:and [:= :s.term 1]]) 
    (sqlc/format))

["SELECT * FROM schemes s LEFT JOIN progress p ON p.scheme_id = s.id"]

that’s not working either, it seems like it’s just ignoring the end part

p-himik 2023-05-05T16:29:30.861579Z

Why do you keep putting that original [:= ...] outside of the [:and ...]?

p-himik 2023-05-05T16:29:56.874059Z

It should be (sqlh/left-join [:progress :p] [:and [:= :p.scheme-id :s.id] [:= :s.term 1]]).

🙌 1

p-himik 2023-05-05T16:30:28.224249Z

In other words, you need to be joining on :and, not on :=.

2023-05-05T16:30:52.610519Z

I see, that worked!

2023-05-05T16:31:12.036689Z

Thanks a lot!

👍 1

Clojurians Log v2

honeysql