Fork me on GitHub
#sql
<
2020-07-17
>
Ramon Rios12:07:27

Hello everyone

Ramon Rios12:07:52

I'm trying to make that a map follow a certain order when i do query

Ramon Rios12:07:08

For example, i have this query

{:select [:customer.customer_number :contact.name :contact.street :contact.house_number :contact.phone :contact.mobile :contact.fax :contact.email
                           [:city.name :city] :city.zip_code [:mlv.ml_value :country]]
                  :from [[:contacts :contact]]
                  :join [[:customers :customer] [:= :contact.id :customer.contact_id]
                         [:cities :city] [:= :contact.city_id :city.id]
                         [:countries :country] [:= :country.id :city.country_id]
                         [:multi_language_values :mlv] [:= :country.name_id :mlv.ml_string_id]]
                  :where [:= :mlv.language_id 1]}

Ramon Rios12:07:31

And my maps are coming not in the order of the select:

{:cities/city aaaaa, :contacts/house_number 11, :cities/zip_code 1111, :contacts/phone 11111, :contacts/mobile 1111111, :contacts/fax , :customers/customer_number 111111, :multi_language_values/country Aaaa, :contacts/name aaaaa, :contacts/street Caaa, :contacts/email [email protected]}

Ramon Rios12:07:42

I'm using next.jdbc

Kevin12:07:30

Not sure why you need this to be ordered? Clojure maps don't have any order

Kevin12:07:44

Or at least are not sorted

Kevin12:07:14

Since maps are key value pairs, I don't think you should rely on any order

Kevin12:07:25

Clojure does have a sorted-map . But I don't know of next-jdbc supports that

Ramon Rios13:07:31

Because i'm going to create a excel file with

Ramon Rios13:07:46

So i need it into a certain order before i turn it into a excel

Johannes F. Knauf13:07:29

@ramonp.rios What library do you use to create the excel file? How does the API look like?

Ramon Rios13:07:31

Using this folk

Johannes F. Knauf13:07:49

So you use it similar to the example there?

(require '[excel-clj.core :as excel])
=> nil
(def table-data
  [{"Date" "2018-01-01" "% Return" 0.05M "USD" 1500.5005M}
   {"Date" "2018-02-01" "% Return" 0.04M "USD" 1300.20M}
   {"Date" "2018-03-01" "% Return" 0.07M "USD" 2100.66666666M}])
=> #'user/table-data
(let [;; A workbook is any [key value] seq of [sheet-name, sheet-grid].
      ;; Convert the table to a grid with the table function.
      workbook {"My Generated Sheet" (excel/table table-data)}]
  (excel/quick-open workbook))

Johannes F. Knauf13:07:44

That is faily interesting.

Johannes F. Knauf13:07:54

Should not create any particular order.

Johannes F. Knauf13:07:06

However, on that page are also examples about using it with arrays.

Ramon Rios13:07:22

I'm looking at another called Docjure.

Ramon Rios13:07:30

Probably that one can get arrays

Johannes F. Knauf13:07:13

Did you try it with arrays?

noisesmith14:07:15

there's a simple idiom to get an ordered set of fields from a map:

(def field-order [:a :b :c :d :e])

#'user/field-order
(def data [{:d 2 :b 1 :c 0 :a -1}
           {:b 2 :d 1 :a 0 :c -1}])
#'user/data
user=>
(ins)user=> (map #(map % field-order) data)
((-1 1 0 2 nil) (0 2 -1 1 nil))

noisesmith14:07:36

when making a csv, that same "field order" vector can be used as the header row

Ramon Rios14:07:36

I was able to use olny arrays to it

noisesmith14:07:38

by "arrays" do you all mean [] - vectors? - we have arrays but they don't seem like they'd be useful here

Ramon Rios14:07:24

yes, i got another excel lib that uses vectors at all so i was able to keep the order

seancorfield15:07:43

@ramonp.rios The best approach for that is to have your JDBC library (`clojure.java.jdbc` or next.jdbc) return the result set as arrays.

seancorfield15:07:31

You'll get a vector of vectors: the first vector will be the column names in the natural SQL order (what you want), and all the other vectors will be the rows, each one in column order.

seancorfield15:07:02

If you have found a solution to a problem you've posted on Slack, it's a courtesy to post back with the solution -- so folks don't continue to read the question and try to help further 🙂

Ramon Rios11:07:28

So, what i basicly did was to use the result set bringing arrays..

(sql/query ds
             (format-sql query)
             {:builder-fn rs/as-unqualified-arrays})
So, i was using docjure, so it understood that the first vector are the hearders and the rest of it was data

3
seancorfield15:07:55

That ought to be a convenient format for converting to CSV or Excel etc.

ghadi19:07:26

What is the most efficient way to insert 500 homogenous maps into a table using next.jdbc?

seancorfield19:07:51

Probably using next.jdbc.prepare/execute-batch!.

ghadi19:07:56

it's not clear from the docs what the sql-params arg is to execute!

seancorfield19:07:16

It will also depend on what DB you're using. You may need to provide additional options.

ghadi19:07:17

it's a vector with a particular syntax, but what?

ghadi19:07:23

sqlite database

seancorfield19:07:08

["some SQL string" param1 param2 param3] -- one param for each ? in the SQL string

seancorfield19:07:15

Same as clojure.java.jdbc

ghadi19:07:41

I've forgotten 🙂

seancorfield19:07:41

But if you use execute-batch!, you can pass batches of parameters for a single SQL string

ghadi19:07:51

ok execute-batch! seems like what I want

seancorfield19:07:20

You'll need to pass a vector of vals from the maps for each inserted row -- since order matters 🙂

ghadi19:07:32

trying it out...

ghadi20:07:58

...works. not the prettiest thing

ghadi20:07:57

is that preferred over next.jdbc.sql/insert-multi! ?

seancorfield20:07:36

execute-batch! will let you send much larger groups of data than insert-multi! but for your case the latter might be faster.

seancorfield20:07:03

But it also expects a vector of column names and then a vector of vectors for the rows of column values.

seancorfield20:07:57

c.j.j. accepted a sequence of hash maps but behind the scenes it did a separate insert for each one which caught a lot of people out (because it was very slow) even though the docs specifically called that out 🙂