Translation RQL to SQL with ORDERBY COUNT
With this schema:
class Person(EntityType):
__unique_together__ = [("nom", "prenom")]
nom = String(maxsize=64, fulltextindexed=True, required=True)
prenom = String(maxsize=64, fulltextindexed=True)
travaille = SubjectRelation(
"Societe",
__permissions__={
"read": (),
"add": (),
"delete": ("managers",),
},
)
evaluee = SubjectRelation("Note")
class Societe(EntityType):
nom = String(maxsize=64, fulltextindexed=True)
evaluee = SubjectRelation("Note")
class Note(EntityType):
date = String(maxsize=10)
type = String(maxsize=1)
para = String(maxsize=512)
The following RQL: Any X, COUNT(Y) GROUPBY X ORDERBY COUNT(Y) WHERE Y is IN (Societe, Person), Y evaluee X
is translated into:
SELECT T1.C0, COUNT(T1.C1) FROM (
SELECT rel_evaluee0.eid_to AS C0, _Y.cw_eid AS C1, COUNT(_Y.cw_eid) AS C2
FROM cw_Personne AS _Y, evaluee_relation AS rel_evaluee0
WHERE rel_evaluee0.eid_from=_Y.cw_eid
UNION ALL
SELECT rel_evaluee0.eid_to AS C0, _Y.cw_eid AS C1, COUNT(_Y.cw_eid) AS C2
FROM cw_Societe AS _Y, evaluee_relation AS rel_evaluee0
WHERE rel_evaluee0.eid_from=_Y.cw_eid
) AS T1
GROUP BY T1.C0
ORDER BY 2
Which is syntactically wrong (no GROUP BY
in the 2 "inside" SELECTS). Here is what we might want:
SELECT T1.C0, COUNT(T1.C1) FROM (
SELECT rel_evaluee0.eid_to AS C0, _Y.cw_eid AS C1
FROM cw_Personne AS _Y, evaluee_relation AS rel_evaluee0
WHERE rel_evaluee0.eid_from=_Y.cw_eid
UNION ALL
SELECT rel_evaluee0.eid_to AS C0, _Y.cw_eid AS C1
FROM cw_Societe AS _Y, evaluee_relation AS rel_evaluee0
WHERE rel_evaluee0.eid_from=_Y.cw_eid
) AS T1
GROUP BY T1.C0
ORDER BY 2
Fun facts:
- If we remove "Y is IN (Societe, Person)", RQL is correctly translated ;
- This happens only when we have a relation with several objects and/or several subjects, it seems the error occurs when we need a SQL JOIN.