sqlingvo icon indicating copy to clipboard operation
sqlingvo copied to clipboard

Enums and Multiple Insert

Open Folcon opened this issue 4 years ago • 0 comments

Firstly thanks for the lib =)...

Two quick questions, if I define an enum as:

(sql/create-type sql-db :gender
  (sql/enum ["male" "female"]))

Is there no better api than:

(sql/insert sql-db :peep []
   (sql/values [{:gender `(raw "'male'")}]))
#_=> ["INSERT INTO \"peep\" (\"gender\") VALUES ('male')"]

I don't see how else to insert enums, as they always appear to be inserted as strings, IE:

(sql/sql
    (sql/insert sql-db :peep []
        (sql/values [{:gender :male}])))
#_=> ["INSERT INTO \"peep\" (\"gender\") VALUES (\"male\")"]

Also if I wanted to use a sql statement inside an insert:

(sql/with sql-db
    [:person (sql/insert sql-db :peep []
               (sql/values [{:name "Peep 1"}])
               (sql/returning :*))]
    (sql/insert sql-db :peep []
      (sql/values [{:name "Peep 2" :friend-id (sql/select sql-db [:id] (sql/from :person))}])))
#_=>
["WITH \"person\" AS (INSERT INTO \"peep\" (\"name\") VALUES (?) RETURNING *) INSERT INTO \"peep\" (\"friend-id\", \"name\") VALUES (SELECT \"id\" FROM \"person\", ?)"
 "Peep 1"
 "Peep 2"]

As you can see SELECT \"id\" FROM \"person\" is not inside it's own expr ie: (SELECT \"id\" FROM \"person\"), so it's not valid, the only way I can see to do this is:

(sql/with sql-db
    [:person (sql/insert sql-db :peep []
               (sql/values [{:name "Peep 1"}])
               (sql/returning :*))]
    (sql/insert sql-db :peep []
      (sql/values [{:name "Peep 2" :friend-id `(raw ~(str "(" (first (sql/sql (sql/select sql-db [:id] (sql/from :person)))) ")"))}])))
#_=>
["WITH \"person\" AS (INSERT INTO \"peep\" (\"name\") VALUES (?) RETURNING *) INSERT INTO \"peep\" (\"friend-id\", \"name\") VALUES ((SELECT \"id\" FROM \"person\"), ?)"
 "Peep 1"
 "Peep 2"]

Which is pretty horrible, is there a better way?

Folcon avatar Mar 30 '20 15:03 Folcon