Skip to content
GitLab
Projects Groups Topics Snippets
  • /
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
    • Contribute to GitLab
  • Sign in
  • cubicweb cubicweb
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributor statistics
    • Graph
    • Compare revisions
  • Issues 182
    • Issues 182
    • List
    • Boards
    • Service Desk
    • Milestones
  • Merge requests 22
    • Merge requests 22
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Packages and registries
    • Packages and registries
    • Container Registry
  • Monitor
    • Monitor
    • Incidents
  • Analytics
    • Analytics
    • Value stream
    • CI/CD
    • Repository
  • Wiki
    • Wiki
  • Snippets
    • Snippets
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar
  • cubicwebcubicweb
  • cubicwebcubicweb
  • Issues
  • #466
Closed
Open
Issue created Nov 23, 2021 by François Ferry@fferryMaintainer

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.
Edited Nov 23, 2021 by François Ferry
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information
Assignee
Assign to
Time tracking