Clojurians Log v2
honeysql
When using next.jdbc / honeysql to do a query joining a table to itself (e.g. :from [:device :d] :join [[:device :parent] [:= :device.parent_id :device.id]]) - is there a way of using the alias as the namespace (- so we get device/... and parent/...) , as opposed to the table name?
Db is Mysql
Yes. It's all about formatting by HoneySQL so the actual DB is irrelevant. By default, both :a.b and :a/b will be formatted as a.b.
Oh - I mean in the results - so that the result set as qualified maps has device/ and parent/
rather then device/ with the second device/ clobbering the first? Or is that what you’ve answered, but I’ve not understood quite?
Ah, I see. No clue then, I don't use that functionality myself.
Thanks anyway!
Ah not sure if this is a bad idea, but it looks like I can accomplish it by using a :with ... CTE and putting the alias in there
Just to be clear: HoneySQL does not run SQL, it only generates it.
next.jdbc is what runs the SQL and it uses the table name as reported by the JDBC driver under the hood. next.jdbc has no control over that, so it's the JDBC driver returning the underlying table name for both device.* and for your aliased parent.*.
What you can do in that situation is alias the columns you are selecting from parent so they are identifiable as distinct from the same columns selected from device.
(I think the docs even specifically talk about that case...)
Thanks Sean for the clarification - is there anything wrong with using the with CTE to alias the table - this does seem to get picked up by JDBC
Yeah, see https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.3.1070/doc/getting-started#options--result-set-builders and the caveats about aliases.
No idea about with -- I don't use CTEs because I'm on an older MySQL version.
Like I said above, the qualifier in the keywords comes from whatever the JDBC driver reports as the table name, so if that works for you, fine. You'll have to make the call on using a CTE from a SQL p.o.v. but it has no impact on next.jdbc or HoneySQL...
Cool, thanks. Yeah it seems to work, for some reason I suppose the alias on the CTE causes getTableName to return that name rather than the source table - which makes sense. I guess I’ll see what my colleagues think.
CTEs could result in a different execution plan. It's not necessarily bad, just something to be aware of.