How can I sort a field from Swagger if the query contains a Join?

ltpitt
ltpitt used Ask the Experts™
on
Hi all,

I serve using Spring Boot via Swagger the following endpoint:

   @Query("select a from Table a " +
      " join a.team t " +
      " join a.user u " +
      " join a.role r " +
      " where lower(t.name) like lower(concat('%', :filter, '%'))" + SQLESCAPE +
      " or lower(t.field1) like lower(concat('%', :filter, '%'))" + SQLESCAPE +
      " or lower(t.field2) like lower(concat('%', :filter, '%'))" + SQLESCAPE +
      " or lower(u.field3) like lower(concat('%', :filter, '%'))" + SQLESCAPE +
      " or lower(r.field4) like lower(concat('%', :filter, '%'))" + SQLESCAPE)
   )
   Page<Role> findRolesByTypeInAndFilter(@Param("filter") String filter, Pageable pageable);

Open in new window


It works perfectly but when the user wants to sort I can only sort fields that are NOT in the joins (only fields available in Table).

If user specify the full field name (e.g. u.field3) it works perfectly,

What can I do to let the user just use, for examlpe, field3 for sorting?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
Most Valuable Expert 2011
Commented:
You have to create a data transfer object (a new model or bean) to hold the specific properties you want as one class.

In your case, fields from table a plus t.name plus field1 to field4.  Lets say this is called UserTeamRole, you would update query to be:

select new UserTeamRole(a.field, t.name, t.field1, t.field2, u.field3, r.field4) ...

Otherwise, you are projecting just object a at the moment so that is why only those fields are visible. — at least that is my understanding.

Author

Commented:
Thanks for your help, Kevin!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial