Fork me on GitHub
#sql
<
2019-10-17
>
dpsutton17:10:18

I can do SELECT (<uuid> NOT IN (SELECT id FROM table));. I'm not sure how to represent this in honey though. My first attempts are

{:select [(hdb/sql-fn :not-in field-id
                      {:select :id
                       :from :table})]}
but its erroring saying don't know how to create ISEQ from keyword

dpsutton17:10:23

anything stand out to anyone?

seancorfield17:10:24

{:select [:id] :from :table}

seancorfield17:10:05

(this is why I tend to use the helpers instead of trying to construct the data structures directly!)

dpsutton17:10:25

i need the t. I'm doing a sequence of checks for the UI for some data wrangling. So I need the info "this field isn't used in smart tasks" "this field isn't used in ..."

dpsutton17:10:34

if any of these fail the migration can't happen

seancorfield17:10:03

user=> (-> (select :id) (from :table))
{:select (:id), :from (:table)}
user=> 

dpsutton17:10:18

kinda like (hdb/fetch {:select [[(hdb/sql-fn :not-in 3 [1 2]) :hi]]})

dpsutton17:10:28

which does return ({:hi true})

dpsutton17:10:06

so the analogue is to select id not in table-1 as check-1, not in table-2 as check-2, etc

dpsutton17:10:20

our own little wrapper around honey. sorry

dpsutton17:10:50

basically calls hsql/format and

seancorfield17:10:31

I'm not sure how to write that in HoneySQL either. It doesn't expect arguments to "functions" to be SQL statements, just expressions.

dpsutton17:10:39

i was missing vectors on :id and :table

dpsutton17:10:54

{:select [[(hdb/sql-fn :not-in field-id
                       {:select [:field-id]
                        :from [:svc.custom_fields]})
           :custom-field]]}
thanks as always @seancorfield

seancorfield17:10:52

Hmm, I guess I was still doing something wrong locally then... This may be a place that the helper functions can't help...

seancorfield17:10:42

user=> (-> (select [(sql/call :not-in "uuid" (-> (select :id) (from :table))) :custom_field]) (h/format))
["SELECT (? not in (SELECT id FROM table)) AS custom_field" "uuid"]
user=> 

seancorfield17:10:17

Where sql is honeysql.types. It's a place where the tagged literal form of call doesn't work (`#sql/call`).

Vachi18:10:53

@seancorfield it is possible to put a prefix in the generated table name from honeySql?

seancorfield18:10:13

The "generated table name" is whatever you pass in as the table name, pretty much, so you already control it. I don't think HoneySQL should do much translation there.

Vachi18:10:24

well, the problem that I have is that, in some setups the tables has prefixes, and in others it don't , and the prefix can be any thing, so I would like to create generic sql sentences , but make table prefix dynamic

seancorfield18:10:47

What do you mean by "prefix" here?

Vachi18:10:27

for example

ps_
as prefix and
product
as table name , then the result would be
ps_product
as the table name, the problem is that the prefix can be anything, but it is shared among all the tables

Vachi18:10:55

what I did for now, is to rename all the tables , deleting the prefix

Vachi19:10:05

so I can make generic statements

seancorfield19:10:26

That's an edge case caused by poor schema design. Not something HoneySQL should support, sorry.

Vachi19:10:56

yeah, you are right , I'm so grateful with your libraries 🙂 Thank you!

seancorfield19:10:35

If you write your generic sql in functions that take the prefix as the argument and use it to build the table names, you could at least minimize the duplication.

Vachi19:10:03

yeah, that's a possible solution, or hack into honeysql source , which on the other hand I would learn how it works 🙂

seancorfield20:10:30

@vachichng I just pulled up the HoneySQL source and there is a dynamic variable you can bind: *name-transform-fn*

seancorfield20:10:22

So you could try (binding [*name-transform-fn* #(str "ps_" %)] ,,,) but I'm pretty sure that will transform all identifiers, not just table names.

seancorfield20:10:42

I'm pretty sure HoneySQL doesn't distinguish between table names and other identifiers in the SQL. I guess you could have your name transform function look at the identifier and conditionally prefix it, if it is one of your tables -- but I think you'd run into problems if you have a column that matches any of those table names.

Vachi20:10:55

@seancorfield thank you for your time, I will have a look on that, for now, I will just run a script that renames the tables in the database, deleting the prefix

seancorfield21:10:00

That would be my preference 🙂