Clojurians Log v2

Re postgres+CTE / performance: since postgres 12: > Specifically, CTEs are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query. Inlining can be prevented by specifying MATERIALIZED, or forced for multiply-referenced CTEs by specifying NOT MATERIALIZED. Previously, CTEs were never inlined and were always evaluated before the rest of the query. Source: https://www.postgresql.org/docs/release/12.0/

All joins accept kv-pairs, similar to e.g. hash-map. So, :left-join [t1 cond1 t2 cond2 ...].

Also, just in case - stuff like :table.column can be written as :table/column. Personally, I find it easier to read but it's also easier to create dynamically - (keyword (name table) (name column)) instead of (keyword (str (name table) "." (name column))).

Thanks @p-himik! I think that did the trick! I tried that earlier, but had an extra vector wrapping the pairs. And thanks for the tip on :table/column

And if you happen to be working with a database that cares about JOIN order: https://cljdoc.org/d/com.github.seancorfield/honeysql/2.4.1078/doc/getting-started/sql-clause-reference#join-by

Also, don't forget that HoneySQL also supports the datalog-style of quoted symbolic DSL too:

'{select (j/jar_name j/group_name homepage description
         user (j/version latest_version) (r2/version latest_release))
 from ((jars j))
 join ...}

Not knowing much about anything, but I’d argue that if your database supports common table expressions (CTE), you could rewrite this query to be somewhat more understandable. The three selects you have in your left-joins could be CTEs

Thanks @seancorfield! The query as translated still works without using join-by (we're using postgres)

@slipset I don't know what CTEs are :) But I'll check them out, thanks!

@slipset @tcrawley At least in the case of PostgreSQL, CTEs are evaluated in advance, outside of the context of the main query, so the results will be stored in-memory, there will be no pre-emptive index lookups and so on. I'd argue that a lateral join would be best here. Of course, should be measured if performance is important, but IME a lateral join, when it's applicable, is always faster than a regular join.

Can’t argue with that, but I will still argue that CTE’s can be easier to read, and also sometimes easier to compose.

With HoneySQL, any part of the DSL can become a "CTE" of sorts since we have let. :)

So your sql would be something like this:

WITH latest_jar as (SELECT jar_name, group_name, MAX(created) AS created FROM jars GROUP BY group_name, jar_name),
lastest_release AS (SELECT jar_name, group_name, MAX(created) AS created FROM jars WHERE NOT (version LIKE '%-SNAPSHOT') GROUP BY group_name, jar_name),
latest_versions AS (SELECT jar_name, group_name, version, created FROM jars)
SELECT j.jar_name, j.group_name, homepage, description, user, j.version AS latest_version, r2.version AS latest_release
FROM jars AS j
INNER JOIN latest_jar AS l
ON (j.jar_name = l.jar_name)
   AND (j.group_name = l.group_name)
   AND (j.created = l.created)
LEFT JOIN  lastest_release AS r
ON (j.jar_name = r.jar_name)
   AND (j.group_name = r.group_name)
LEFT JOIN latest_versions AS r2
ON (j.jar_name = r2.jar_name)
   AND (j.group_name = r2.group_name)
   AND (r.created = r2.created)
WHERE j.group_name = ?
ORDER BY j.group_name ASC, j.jar_name ASC

I treat SQL as bytecode - I never read (let alone write) it unless I really have to. It's hard for me to understand why its readability would be important in the context of HoneySQL.

Interesting. I/we treat our queries as the “important” stuff in our codebase.

Queries - yes. SQL - no.

Unfortunately, still have to deal with the implementation details, like the aforementioned fact about large (in the data sense) CTEs being detrimental to performance, potentially significantly.

Hmm, but I/we express our queries in honeysql, so it matters to me that the honey is readable. How do you represent your queries?

Also (in my case), I prefer readable over performant until the performance becomes a problem.

As I said - I use let. If you don't like to join on something in the middle of a DSL data structure, you can trivially move it to any place outside of that structure.

Right.

Oh, and even without let, it's still possible here, at least when there are no deeply nested subqueries, because the data structure is a map - the order of the keys doesn't matter. Something like:

{:left-join [...]
 :select    ...
 :from      ...}

And queries compose in HoneySQL so -> through a bunch of smaller functions with good names can often really help readability.

Agreed.