Clojurians Log v2

The following is not working for me:

{:insert-into :collection_products
         :columns [:collection_id :product_id]
         :values [{:collection_id collection-id
                   :product_id {:select [:id]
                                :from :products
                                :where [:= :external_id
                                        [:any (into-array String external-ids)]]}}]}

It formats this statement:

INSERT INTO collection_products(collection_id, product_id)
  VALUES (?,(
      SELECT
        id
      FROM
        products
      WHERE
        external_id = ANY (?)));

user=> (sql/format {:insert-into [[:collection-products [:collection-id :product-id]] {:select [42, :id] :from :products :where [:= :external-id [:any 13]]}] :returning :*})
["INSERT INTO collection_products (collection_id, product_id) SELECT ?, id FROM products WHERE external_id = ANY(?) RETURNING *" 42 13]
user=>

See https://cljdoc.org/d/com.github.seancorfield/honeysql/2.6.1147/doc/getting-started/sql-clause-reference#insert-into-replace-into for several similar examples.

Gosh, why couldn’t I find this?!

Sorry!

Thank you!

NP. It's quite subtle to get all the pieces together.

And it does work, thank you again!

I think for your insert into with values, since you're providing hash maps, you don't want :columns in there but, yeah, as you noted the SQL won't work for no matches.

Yes, very true. That was another mistake.

I tried to find this example on the site you linked, but I could not find it. Am I blind?

These examples?

Doesn't have :returning (or a where clause on the select) but otherwise is very similar I thought?

I missed the ' at the beginning and I thought this is the other set of examples with the helper functions

The "Datalog"-style DSL with quoted symbols instead of keywords 🙂

Now, I can see that. I haven’t used Datalog yet