Uploaded image for project: 'PUBLIC - Liferay Portal Community Edition'
  1. PUBLIC - Liferay Portal Community Edition
  2. LPS-9690

Improve RoleFinderImpl.countByR_U()'s query logic

    Details

    • Branch Version/s:
      5.2.x, 5.1.x
    • Backported to Branch:
      Committed

      Description

      This improvement can save 6 inner joins for every RoleFinderImpl.countByR_U() call.

      Before:
      ( SELECT COUNT(Role_.roleId) AS COUNT_VALUE FROM Role_
      INNER JOIN Groups_Roles ON (Groups_Roles.roleId = Role_.roleId)
      INNER JOIN Users_Groups ON (Users_Groups.groupId = Groups_Roles.groupId) WHERE (Role_.roleId = ?) AND (Users_Groups.userId = ?) )

      UNION

      ( SELECT COUNT(Role_.roleId) AS COUNT_VALUE FROM Role_
      INNER JOIN Groups_Roles ON (Groups_Roles.roleId = Role_.roleId)
      INNER JOIN Group_ ON (Group_.groupId = Groups_Roles.groupId)
      INNER JOIN Users_Orgs ON (Users_Orgs.organizationId = Group_.classPK) WHERE (Role_.roleId = ?) AND (Users_Orgs.userId = ?) )

      UNION

      ( SELECT COUNT(Role_.roleId) AS COUNT_VALUE FROM Role_
      INNER JOIN Groups_Roles ON (Groups_Roles.roleId = Role_.roleId)
      INNER JOIN Groups_Orgs ON (Groups_Orgs.groupId = Groups_Roles.groupId)
      INNER JOIN Users_Orgs ON (Users_Orgs.organizationId = Groups_Orgs.organizationId) WHERE (Role_.roleId = ?) AND (Users_Orgs.userId = ?) )

      UNION

      ( SELECT COUNT(Role_.roleId) AS COUNT_VALUE FROM Role_
      INNER JOIN Users_Roles ON (Users_Roles.roleId = Role_.roleId) WHERE (Role_.roleId = ?) AND (Users_Roles.userId = ?) )

      UNION

      ( SELECT COUNT(Role_.roleId) AS COUNT_VALUE FROM Role_
      INNER JOIN Groups_Roles ON (Groups_Roles.roleId = Role_.roleId)
      INNER JOIN Group_ ON (Group_.groupId = Groups_Roles.groupId)
      INNER JOIN Users_UserGroups ON (Users_UserGroups.userGroupId = Group_.classPK) WHERE (Role_.roleId = ?) AND (Users_UserGroups.userId = ?) )

      UNION

      ( SELECT COUNT(Role_.roleId) AS COUNT_VALUE FROM Role_
      INNER JOIN Groups_Roles ON (Groups_Roles.roleId = Role_.roleId)
      INNER JOIN Groups_UserGroups ON (Groups_UserGroups.groupId = Groups_Roles.groupId)
      INNER JOIN Users_UserGroups ON (Users_UserGroups.userGroupId = Groups_UserGroups.userGroupId) WHERE (Role_.roleId = ?) AND (Users_UserGroups.userId = ?) );

      Now:
      ( SELECT DISTINCT Groups_Roles.roleId AS ROLE_ID FROM Groups_Roles
      INNER JOIN Users_Groups ON (Users_Groups.groupId = Groups_Roles.groupId) WHERE (Groups_Roles.roleId = ?) AND (Users_Groups.userId = ?) )

      UNION

      ( SELECT DISTINCT Groups_Roles.roleId AS ROLE_ID FROM Groups_Roles
      INNER JOIN Group_ ON (Group_.groupId = Groups_Roles.groupId)
      INNER JOIN Users_Orgs ON (Users_Orgs.organizationId = Group_.classPK) WHERE (Groups_Roles.roleId = ?) AND (Users_Orgs.userId = ?) )

      UNION

      ( SELECT DISTINCT Groups_Roles.roleId AS ROLE_ID FROM Groups_Roles
      INNER JOIN Groups_Orgs ON (Groups_Orgs.groupId = Groups_Roles.groupId)
      INNER JOIN Users_Orgs ON (Users_Orgs.organizationId = Groups_Orgs.organizationId) WHERE (Groups_Roles.roleId = ?) AND (Users_Orgs.userId = ?) )

      UNION

      ( SELECT DISTINCT Users_Roles.roleId AS ROLE_ID FROM Users_Roles WHERE (Users_Roles.roleId = ?) AND (Users_Roles.userId = ?) )

      UNION

      ( SELECT DISTINCT Groups_Roles.roleId AS ROLE_ID FROM Groups_Roles
      INNER JOIN Group_ ON (Group_.groupId = Groups_Roles.groupId)
      INNER JOIN Users_UserGroups ON (Users_UserGroups.userGroupId = Group_.classPK) WHERE (Groups_Roles.roleId = ?) AND (Users_UserGroups.userId = ?) )

      UNION

      ( SELECT DISTINCT Groups_Roles.roleId AS ROLE_ID FROM Groups_Roles
      INNER JOIN Groups_UserGroups ON (Groups_UserGroups.groupId = Groups_Roles.groupId)
      INNER JOIN Users_UserGroups ON (Users_UserGroups.userGroupId = Groups_UserGroups.userGroupId) WHERE (Groups_Roles.roleId = ?) AND (Users_UserGroups.userId = ?) )

        Attachments

          Activity

            People

            • Assignee:
              support-lep@liferay.com SE Support
              Reporter:
              shuyang.zhou Shuyang Zhou
              Participants of an Issue:
              Recent user:
              Esther Sanz
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Days since last comment:
                10 years, 4 weeks, 3 days ago

                Packages

                Version Package
                6.0.1 RC
                6.0.2 RC