Using many-to-many relations with filtering

[Juho Vähä-Herttua]

Hi,

I'm evaluating if korma would be useful in a hobby project, let's consider I have following structure:

users

-----

id bigint

users_contacts

--------------

user_id bigint

contact_id bigint

active boolean

I would like to join only active contacts with korma, I (intuitively) tried something like following:

(defentity contacts

  (table :users))

(defentity users

  (many-to-many contacts :users_contacts

    {:lfk :user_id

     :rfk :contact_id

     :where {:verified true}}))

However this doesn't seem to work, how could I add a where clause to the many-to-many relation so that only verified relations would be selected. Is this possible with many-to-many by korma? Are there any workarounds? The verified flag above might not be the best example, but in this case I really would like to add some metadata to a many-to-many relation.

Otherwise korma seems very nice, but I wouldn't want to design my database schema purely by the limitations of it. So hope there's a good solution.

Juho

[Juho Vähä-Herttua]

On Saturday, February 14, 2015 at 4:33:10 PM UTC+2, Juho Vähä-Herttua wrote:

However this doesn't seem to work, how could I add a where clause to the many-to-many relation so that only verified relations would be selected. Is this possible with many-to-many by korma? Are there any workarounds? The verified flag above might not be the best example, but in this case I really would like to add some metadata to a many-to-many relation.

Just to let everyone know, I'm working around this in postgres by creating a view which contains a select with a WHERE clause restricting the relations and using that view as many-to-many table argument. It works pretty well, so I'll probably continue trying to do stuff with Korma, because other than this it seems to be one of the nicest SQL libraries I've come across. But if you have good ideas how this could be implemented without a separate view, they are still welcome.

Juho

[Immo Heikkinen]

There's no support for filtering (where) in Korma entity definition in general. 

Usually filtering is implemented in the query instead of entity, but unfortunately it is not even possible to select attributes from the join table using `with`. To work around this limitation, you can define entity for the join table  as discussed in this thread:

https://groups.google.com/d/topic/sqlkorma/r3kR6DyQZHo/discussion

Alternatively you can use `join` instead of `where` giving you more flexibility.

--
You received this message because you are subscribed to the Google Groups "Korma" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlkorma+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Juho Vähä-Herttua]

That thread is enough of a workaround just didn't find it earlier, thank you.

In the end I ended up using a combination of database views (for conditional many-to-many relations) and a combination of has-many and belongs-to as suggested. If we talk with the terms of the user-to-tweet example, I also had overlapping columns in user-to-tweet and tweet tables, and sqlkorma combines the one-to-one relations into a single object, so got fields like "modified2" and such. So I simply thew in an extra entity-fields only selecting non-conflicting columns by default.

Since many-to-many doesn't even create (or allow to define) a table alias for the join table, maybe the limitations of it could be documented a bit better on the website? Also the :lfk and :rfk are not documented anywhere and had to check that from the source code. I could make a pull request to the website repo if you find it helpful.

Juho

[Immo Heikkinen]

Sure, document improvements are welcome.