join table query with one where clause

[mavericklou]

Hey guys,

(select user (where {:account.type "A_TYPE"}) (with account) )

After upgrade from 0.3.0-RC6 to 0.3.0, I found this query doesn't work anymore. I have to nest where clause into account entity. This is bad because it's better that we can just passing in one union query for join tables.

[immoh]

I think the where clause should be nested inside (with account). I'm assuming you have one-to-one relation between user and account, this wouldn't work for one-to-many relations either.

The change from 0.3.0-RC6 to 0.3.0 was that transformers are also executed for subentities of one-to-one relations and therefore subentities are fetched with separate query if there's a transformer defined fore subentity.

[mavericklou]

Understand. We are building APIs for accepting user query directly. Nest where query is not flexible for us. If we have multiple conditions from our end user, how can we separate them into different subentities?

[siscia]

I have a similar problem.

Given a schema:

(defentity buyers
  (belongs-to sysaccounts))

(defentity sellers
  (belongs-to sysaccounts)

(defentity sysaccounts
  (has-one sellers)
  (has-one buyers))

A query like this:

(select sysaccounts
                       (fields [:last_seen])
                       (with sellers
                             (fields)
                             (where {:id id})))
;; or its sister
(select sysaccounts
                      (fields [:last_seen])
                      (with buyers
                            (fields)
                            (where {:id id})))

yield an error:

JdbcSQLException Syntax error in SQL statement 
"SELECT  FROM ""sellers"" WHERE[*] (""sellers"".""id"" = ?) AND (""sellers"".""sysaccounts_id"" = ?)";
 expected "., (, AS, RIGHT, LEFT, FULL, INNER, JOIN, CROSS, NATURAL, ,, SELECT"; SQL statement:
SELECT  FROM "sellers" WHERE ("sellers"."id" = ?) AND ("sellers"."sysaccounts_id" = ?) [42001-174]  org.h2.message.DbException.getJdbcSQLException (DbException.java:332)

It use to work fine in the RC-6, the RC-8 broken it for sure, maybe also the RC-7. (I will check if you need me too.)

However I am not really sure that I am doing everything right, probably either the belogs-to (in the (defentity {seller-buyer) ) or the has-one (in the (defentity sysaccounts)) could be wrong...

[immoh]

@mavericklou You need to find a way to transform user input to Korma query. Korma API should be flexible enough to do this, take a look at how to compose query using select* and with*. In your example you know that the where condition is related to the account entity (based on the prefix) so it should be possible to find the correct with clause and use it there.

Please note that if you remove the transform fn from account entity, your example would still work. However, I do not recommend this since there's no guarantee that it will work with future Korma versions.

@siscia No-arg call (fields) is producing invalid SQL. It's not related to one-to-one relations only:

user=> (sql-only (select :foo (fields)))
"SELECT  FROM \"foo\""

Similar thing, you were able to get away with this until RC6 but not anymore. Just remove it and you should be fine.

[siscia]

:+1: Grazie mille Thank you so much...

Edit: change language