Skip to content

GitLab

  • Menu
Projects Groups 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
    • Contributors
    • Graph
    • Compare
  • Issues 230
    • Issues 230
    • List
    • Boards
    • Service Desk
    • Milestones
  • Merge requests 16
    • Merge requests 16
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Packages & Registries
    • Packages & 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
  • cubicweb
  • cubicwebcubicweb
  • Issues
  • #466
Closed
Open
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