Clojurians Log v2

Hello everyone

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

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]}

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 aaa.@aaa.aa}

I'm using next.jdbc

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

Or at least are not sorted

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

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

Because i'm going to create a excel file with

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

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

https://github.com/matthewdowney/excel-clj

Using this folk

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))

Yes

That is faily interesting.

Should not create any particular order.

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

I'm looking at another called Docjure.

Probably that one can get arrays

Did you try it with arrays?

Trying

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))

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

I was able to use olny arrays to it

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

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

@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.

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.

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

I did that.

Thank you

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 🙂

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

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

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

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

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

sqlite database

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

Same as clojure.java.jdbc

I've forgotten 🙂

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

ok execute-batch! seems like what I want

See https://cljdoc.org/d/seancorfield/next.jdbc/1.1.569/doc/getting-started/prepared-statements#batched-parameters

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

trying it out...

...works. not the prettiest thing

thanks @seancorfield

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

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

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

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 🙂