Question: Possible to join snippets in sql?

[sthomp]

The documentation for snippets shows how to join snippets together in clojure code. But Im wondering if its possible to do something like this:

-- :snip where-user-id
WHERE user_id = :user-id

-- :snip where-order-id
WHERE order_id = :order-id

-- :snip main-query
SELECT * FROM ORDERS

-- name: select-by-user-id :? :*
:snip:main-query
:snip:where-user-id

-- name: select-by-order-id :? :*
:snip:main-query
:snip:where-order-id

(select-by-user-id db {:user-id 1})

(select-by-oder-id {:order-id 2})

[csummers]

Snippets don't merge parameters up into the calling query's parameter data. The reason for this is that a snippet generates an sqlvec (e.g., ["where id = ?" 2]) and a snippet parameter type consumes an sqlvec, so there's no parameter data being passed around.

I realize this is a bit more verbose:

(select-by-user-id db {:user-id (where-user-id {:id id}))

however, the one-to-one mapping of SQL snippet to hashmap parameter data remains simple. I think the auto-merging of parameters across all underlying snippets (remember that snippets can contain snippets, which can contain snippets,...) would create incidental complexity.

Another benefit to the way it works is that you can build up your own sqlvecs for consumption from other sources (not just HugSQL snippets).

All that said, if you're not liking the verbosity of snippet usage, you might give Clojure Expressions more of a go, since they can dynamically change the query without abstracting out to the snippet level. I find myself using expressions more because of this reason.

[csummers]

So I'm considering the possibility that your request might be a fairly intuitive feature and would simplify some usage in the "small" where it's unlikely to create any confusing merges of parameter names.

It would probably be called a partial or part. Something like:

-- :part by-id
id = :id

-- :name user :? :1
select * from users where :part:by-id

-- :name order :? :1
select * from orders where :part:by-id

My other concern here is how to refer to the same partial from another sql file and whether or not that should be allowed at all. I'll have to keep thinking on that. The sharing of snippets across sql files is handled by the fact that we are explicitly injecting them into the parameter data. Partials would not have the same luxury; we would have to create a require-like include/import of "shared" sql for that to work, and that seems a bit like a can of dependency worms.

Anyhow, I'll keep thinking on that. Thanks for the question!

[spacepluk]

I would definitely use this :+1:

[timgilbert]

I'd personally be glad of an implementation that would limit "part" elements to being referred to in the same file they were defined in, and I could live with needing to define them before they're used, just for the sake of being able to reuse code which varies only a little bit. Needing to refer to the snippets in the calling Clojure code itself seems a little like a leaky abstraction (at least for my specific use case, where I have maybe three or four queries which are almost identical except for maybe a WHERE or ORDER BY clause).

[csummers]

Yes, I think I've finally decided to implement this when I get some time to do so. The new feature will likely be called Fragments to stay away from part/partial name confusion (no, it's really just so I can write -- :frag :smile: ).

I haven't quite decided on dependency issues, but I definitely see a use case for allowing a common.sql file with common fragments in it that can be included into other sql files.

In addition to all of your comments adding support for this, I've seen some code in the wild that defines common functions returning SQL strings in a clojure namespace and then uses the Clojure expression support to bring in common fragments in this way. Something like:

(ns myapp.db.common-sql)

(defn id-eq [] "id = :id")

-- :name foo :? :1
-- :require [myapp.db.common-sql :as common]
select * from foos where /~* (common/id-eq) *~/

Clever, and shows the power of what's already available in HugSQL, but direct Fragment support would prevent jumping through these hoops.

[Bclayson]

Doesn't look like this ever got implemented. Any chance it could still happen?

[brjann]

The fragment feature would be a very nice addition!

[glenjamin]

I was also looking into something like this, we've got a fairly long SQL file that contains lots of CRUD queries that end up repeating the same list of fields.

So far the best approach I can find to reduce the duplication in this file is to have them refer to a namespace-qualified symbol defined elsewhere via a clojure expression. It would be nice if there was a way to reduce repetition within a SQL file without having to define things outside of it.

[solatis]

I would be interested in this. I have situations where I need to do something like WHERE (col1 = foo AND col2 = bar) OR (col1 = wom AND col2 = bat), and would love to be able to construct this as snippets and compose them using "OR" into a where clause.