Re: Joins

[Chris Granger]

Hey Base,

Do you want to override this all the time? or just in a specific query?

If it's the former you can set pk in your entity and fk in the relationship:

(defentity users

  (pk :somePK)

  (has-many emails {:fk :somePKID})) ;; this maps to users.somePK = emails.somePKID

If you only want to do it for a specific query you can use (join):

(select users

  (join emails :somePK :emails.somePKID))

Cheers,
Chris.

On Sun, Nov 6, 2011 at 5:32 AM, Base <basse...@gmail.com> wrote:

Hi Chris -

So a question on joins:

How does one execute a join on fields that are not the primary key?
The documentation reads:

 (has-many address)
     ;; assumes users.id = address.users_id

The assumption notwithstanding, how does one override this tables on
other indexes?  Or is this functionality not available in Korma?

Thanks

Base

[Bassel Abul-Hajj]

Thanks Chris!

Much appreciated.  Great library.  So far i am finding it a little easier to use and less verbose than ClojureQL.

Good times!

Base 

[Chris Granger]

With the change I just made in 0.2.2-SNAPSHOT, you'd do it like so:

(def constraints [[:userid 1 '=] [:carid 34 '<]]) 

(def constraint-map (reduce (fn [result [field value op]]

                              (assoc result field [op value]))

                            {}

                            constraints))

(select mytable

  (where constraint-map))

Cheers,
Chris.

On Sun, Nov 6, 2011 at 6:02 PM, Bassel Abul-Hajj <basse...@gmail.com> wrote:

Hi Chris 

Sorry to keep bothering you!  I am *stuck* on some functionality and hoping you can help.

I am trying to create a query that essentially allows you to pass in a sequence of constraints and generate the query

(def constraints [[:userid 1 '=] [:carid 34 '<]]) 

where each constraint  consists of the [columnName value operator-to-use]

The form looks like this...

 

(defn query-entity [constraints]

   (exec (let [qr (select* mytable)]

          (reduce 

            (fn [a b]

               (let [[varname value operator] b]

                   (where a

                       (list (or operator '=) varname value)))) qr constraints))))

I get strange jdbc errors that say:

Failure to execute query with SQL:

SELECT * FROM claims LEFT JOIN claimlines ON claims.clmaudnbr = claimlines.clmaudnbr LEFT JOIN members ON claims.memberid = members.memberid WHERE ({:generated "(NULL OR ?)"} :members.memberid 16000254)  ::  (korma.internal.sql/pred-=)

JdbcSQLException:

 Message: Syntax error in SQL statement "SELECT * FROM CLAIMS LEFT JOIN CLAIMLINES ON CLAIMS.CLMAUDNBR = CLAIMLINES.CLMAUDNBR LEFT JOIN MEMBERS ON CLAIMS.MEMBERID = MEMBERS.MEMBERID WHERE ( :[*]GENERATED ""(NULL OR ?)""  :MEMBERS.MEMBERID 16000254) "; expected "), NOT, EXISTS, SELECT, FROM"; SQL statement:

SELECT * FROM claims LEFT JOIN claimlines ON claims.clmaudnbr = claimlines.clmaudnbr LEFT JOIN members ON claims.memberid = members.memberid WHERE ( :generated "(NULL OR ?)"  :members.memberid 16000254) [42001-148]

 SQLState: 42001

 Error Code: 42001

nil

Any idea what I am doing wrong?  I get something similar to work using ClojureQL but am hoping to switch over to Korma as I prefer the syntax greatly.  

Thanks in advance!

[Bassel Hajj]

Love it.   Very elegant solution.