Uploaded image for project: 'Spring Data JPA'
  1. Spring Data JPA
  2. DATAJPA-346

Sort by property of an associated object doesn't work. Join type should be LEFT OUTER JOIN for sorting on associated objects.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.4 M1
    • Fix Version/s: 1.4 RC1
    • Component/s: Core, Querydsl
    • Labels:
      None
    • Environment:
      Both Windows and Linux. MySql DB.
    • Sprint:
      Babbage - RC1 - Stage Two

      Description

      The issue I have is that I try to order by a property in an associated object (composite pattern). Thus I have a class PreApprovalRequest and a property in that class assignedTo of type PaceUser. I am trying to order requests by the name of the assignedTo user.

      Concrete code below. I have a call that loads all PreApprovalRequest objects (for simplicity I'll leave out pagination). E.g.

       select * from PreApprovalRequest

      That translates to:

      select * from ( select preapprova0_.ID as ID0_, preapprova0_.CREATED as CREATED0_ from PRE_APPROVAL_REQUEST preapprova0_ order by preapprova0_.CREATED desc ) where rownum <= ?

      Now, when I apply the ordering by first name and then last name of the assignedTo user, the query translates into:

      select * from ( select preapprova0_.ID as ID0_, preapprova0_.CREATED as CREATED0_ from PRE_APPROVAL_REQUEST preapprova0_, PACE_USER paceuser1_ where preapprova0_.ASSIGNED_TO=paceuser1_.ID order by lower(paceuser1_.FIRST_NAME) asc, lower(paceuser1_.LAST_NAME) asc ) where rownum <= ?

      Note that because the join used when applying sorting is the default one (not using the JOIN keyword) - which is basically INNER JOIN - the items where there is no user assigned to the request will be filtered out, which is not desired of course.

      Thus, I think the solution is to join using LEFT OUTER JOIN instead of default join and that should fix it.

      I'm happy to clone the repository and add the fix if needed.

      Thanks,
      Iulian.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              thomasd Thomas Darimont
              Reporter:
              stefanicai Iulian Stefanica
              Last updater:
              Trevor Marshall
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: