cubicweb #3401014 [rql] OR on relations with a cardinality of '*' fails [open]
In Unlish we have two relations from CWUser to Event : attends (**), and organize (*?).Inline emphasis start-string without end-string.
The following request will fail miserably :
Any X WHERE X is Event, U eid %(eid)s, U organize X OR U attends X
The result of this request contains only the event entities I want, but a lot of times ! The rowcount is a product of the total number of relations or something like that (in my case, 1283 rows for 243 events in the db, when only 5 events are actually filtered out).
Digging a bit, I found that CW send the following SQL request:
SELECT _X.cw_eid FROM attends_relation AS rel_attends1, cw_Event AS _X, organize_relation AS rel_organize0 WHERE ( (rel_organize0.eid_from=537549 AND rel_organize0.eid_to=_X.cw_eid ) OR (rel_attends1.eid_from=537549 AND rel_attends1.eid_to=_X.cw_eid) )
On a side-note I see that the joins are not made with the JOIN syntax, which is not optimal but not the issue here.
The issue is that given the cardinality of the relations, this request will not gives us what we want. To get what we want, we need to use EXISTS and subqueries not to be victim of the attends relation cardinality.
Here is a SQL request that works, which uses LEFT JOIN on the relation with a cardinality of '1', and EXISTS on the relation with a cardinality of '*':
SELECT _X.cw_eid, rel_organize0.eid_from FROM cw_Event AS _X LEFT JOIN organize_relation AS rel_organize0 ON _X.cw_eid = rel_organize0.eid_to WHERE rel_organize0.eid_from=537549 OR EXISTS (SELECT 1 FROM attends_relation AS rel_attends1 WHERE _X.cw_eid = rel_attends1.eid_to AND rel_attends1.eid_from=537549);
The initial RQL request looks pretty natural, and it should really be supported by the engine.
|done in||<not specified>|
|closed by||<not specified>|