EzDevInfo.com

clojureql select between two dates

How would I use clojureql to select between two dates? Hopefully something like this:

@(-> (table :abc)
     (select (where (between d1 d2))))

How can I add an index to a table using ClojureQL?

Using any clojure database/orm library, how can I create an index in a database? I can't seem to find any relevant examples. (I'm most interested in ClojureQL and clojure.java.jdbc, since I'm currently using those).

EDIT: OK, so I figured out a way to do this with clojure.java.jdbc:

(ns foo
  (:require [clojure.java [jdbc :as sql]]))

(sql/with-connection db
  (sql/do-commands
     "CREATE INDEX IF NOT EXISTS my_index ON some_table (a_column)" ))

But I'd really like to know how to do this in ClojureQL.

ORM for clojure?

I was reading this site about the clojure web stack:

http://brehaut.net/blog/2011/ring_introduction

and it has this to say about ORM for clojure:

"There are no SQL/Relational DB ORMs for Clojure for obvious reasons."

The obvious reason I can see is that the mapping to object happens automatically when you do a clojure.contrib.sql or clojureql query. However it seems some extra work is needed to do one-to-many or many-to-many relations (although maybe not too much work).

I found this write up for one-to-many: http://briancarper.net/blog/493/

Which I'm not sure I agree with; it appears to be assuming that both tables are pulled from the database and then the joined table is filtered in memory. In practice I think the sql query would specify the where criteria.

So I'm left wondering, is there some fairly obvious way to automatically do one-to-many relations via clojureql or clojure.contrib.sql? The only thing I can think of is something like this (using the typical blog post/comment example):

(defn post [id] 
    @(-> (table :posts)
        (select (where :id id))))
(defn comments [post_id]
    @(-> (table :comments) 
         (select (where :post_id post_id))))
(defn post-and-comments [id]
    (assoc (post id) :comments (comments id)))

Is there any way to sort of automate this concept or is this as good as it gets?

clojureql, open-global and with-results

Just trying to understand the purpose of clojureql's open-global and with-results. I started by reading this overview: How does ClojureQL compare to clojure.contrib.sql?

I thought for some reason that open-global would replace sql/with-connection, e.g. I thought this would work:

(def db {...}) ; connection details omitted
(open-global db) 

(println (:name (first @(table :users))) 

However this doesn't work. It seems I need to both do open-global and wrap the executing query in a (sql/with-connection db), which sort of surprised me (I thought open-global provided a default globally accessible connection). So since that doesn't appear to be the case I'm now left wondering exactly what it does.

Also...how does with-results differ from simply executing the query with @? Because it seems @(table :users) will leave me with a sequence that is the result of executing the query (isn't that what with-results does as well)?

Clojureql - Can't take value of a macro (clojure)

I need to generate the following:

(clojureql.core/join (clojureql.core/table db :tableA) (clojureql.core/table db :tableA)
     (clojureql.core/where (apply and (= :tableA.id :tableB.id)
                      [(apply or [(clojureql.predicates/in :tableA.id [1 2])
                                  (clojureql.predicates/in :tableA.id [3 4])])])))

But the "apply or" form needs to be "passed" from another function or macro. Here's what I mean:

(clojureql.core/join (clojureql.core/table db :tableA) (clojureql.core/table db :tableA)
     (clojureql.core/where (apply and (= :tableA.id :tableB.id)
                      [(my-or-f)])))

where this function is:

(defn my-or-f []
    (apply or [(clojureql.predicates/in :tableA.id [1 2])
         (clojureql.predicates/in :tableA.id [3 4])]))

however this function throws this exception:

#<CompilerException java.lang.Exception: Can't take value of a macro: #'clojure.core/or (NO_SOURCE_FILE:2)>

I've also tried using a macro. It compiles but it throws the same exception when I try running the query using this macro

(defmacro my-or-f []
    `(apply or [(clojureql.predicates/in :tableA.id [1 2])
         (clojureql.predicates/in :tableA.id [3 4])]))

Is there another way that I could use the "apply or"?

Thanks.

How can I change a list to code in a Clojure macro?

I have the following macro:

(defmacro ss [x]

`(clojureql.core/select 
      (clojureql.core/table db "users_table")
      (clojureql.core/where  ~x)
  )
)

(macroexpand '(ss '(= :type "special")))

: but it produces :

(clojureql.core/select (clojureql.core/table oe.db.dbcore/db "users_table") (clojureql.core/where '(= :type "special"))) 

: instead of :

(clojureql.core/select (clojureql.core/table oe.db.dbcore/db "users_table") (clojureql.core/where (= :type "special"))) 

: I realise that the problem is I am passing in a list '(= :type "special"), but how can I get this to unquote in the macro?

Update:

I finally got this working thanks to Mikera's answer by doing this:

(defn ss [x]

  (clojureql.core/select 
      (clojureql.core/table db "users_table")
      x
  )


)

(macroexpand '(ss (eval `(clojureql.core/where ~'(= :type "special")))))

: although the output is slightly different it works as expected:

(ss (eval (clojure.core/seq (clojure.core/concat (clojure.core/list 'clojureql.core/where) (clojure.core/list '(= :type "special")))))) 

how to insert a default value into a column using clojureql

I have a postgre table with "id" as SERIAL(auto-increment column), "name" and "instruction" columns.

I'm trying to insert into it as:

(ql/conj! recipe-table {:id nil :name "a" :instructions "b"})

*Evaluation aborted

(ql/conj! recipe-table {:id :default :name "a" :instructions "b"})

*Evaluation aborted

(ql/conj! recipe-table {:name "a" :instructions "b"})

*Evaluation aborted

But:

(ql/conj! recipe-table {:id 1 :name "a" :instructions "b"})

works, it just inserts the 1 into id, but it doesn't do the auto-increment part.

So I have access to the table, I have rights to write into it as proven by the last conj!, I just can't write it with a default value.

clojureql query with sub-select

Given a table with the following structure:

CREATE TABLE transitions (id INT, ordering INT, item_id INT, action_id INT)

Is it possible to get ClojureQL to generate a query like this one:

SELECT a.item_id, a.action_id
  FROM transitions a
 WHERE a.ordering = (SELECT MAX(b.ordering)
                       FROM transitions b
                      WHERE b.item_id = a.item_id
                    )

This will return many rows, one for each item, indicating that item's latest transition.

I've been looking at using join, but worry I might hit this bug: https://github.com/LauJensen/clojureql/issues/114

Getting the HAVING clause in ClojureQL query

I'm trying to build the following query in ClojureQL:

SELECT ixIso, MIN(IF(dtDespatched, fQuantity, 0)) AS quantity
  FROM IsoItem WHERE fQuantity > 0
  GROUP BY ixIso
  HAVING quantity < 1

I've been trying variations of the following code, basing it off this IRC log I found.

(-> (cql/table DB :IsoItem)
    (cql/select (cql/where (> :fQuantity 0)))
    (cql/aggregate [[ "MIN(IF(IsoItem.dtDespatched, IsoItem.fQuantity, 0))" :as :quantity]]
                   [:ixIso] )
    (cql/select (cql/where (< :quantity 1))))

However, the query it keeps giving me is:

SELECT IsoItem.ixIso,MIN(IF(IsoItem.dtDespatched, IsoItem.fQuantity, 0)) AS quantity FROM IsoItem WHERE (fQuantity > 0) AND (quantity < 1) GROUP BY IsoItem.ixIso

I feel like I must be missing something so basic.

In ClojureQL how can I make a where clause case insensitive?

In ClojureQL how can I make a where clause case insensitive? I am trying to add UPPER or something to do this in the where clause but I can't figure out how to do this

Just generate SQL from ClojureQL's disj! conj! and update-in! functions

Is there a way to just generate the sql queries from ClojureQL's disj! conj! and update-in! functions, instead of executing them directly ?

How to do SELECT ... WHERE ... IN in ClojureQL?

I just worked this one out. Since it's not in the documentation and needs you to dig into the source, this seemed the right venue for the solution.

In ClojureQL, how do you create a query like:

SELECT * from foo where id in (1, 5, 7);

Clojure QL : running plain SQL queries

are there any ways to perform the following queries in clojureql ? :

• insert into table1(id, name, age) select id, name, age from table2
• create table t1 (id int, name varchar(50), age varchar(100));
• drop table table3;
• analyze table4;

or is there any work around to running these ? even if I need another library to do it, that's fine.