Activity

1. rbygrave commented on Jun 26, 2024

   

   rbygrave

   on Jun 26, 2024

   Member

   More actions

   if sorting by many to many relationship is a supported scenario

   This case is where the query is sorting by many to many AND not fetching that many to many. So when we look at the SQL we see:

   select distinct t0.userid, t0.user_name, t0.user_type_id, t1.roleid 
   from muser t0 
   left join mrole_muser t1z_ on t1z_.muser_userid = t0.userid 
   left join mrole t1 on t1.roleid = t1z_.mrole_roleid 
   order by t1.roleid;

   ... but as the M2M "roles" are not fetched the order by (and joins that are required to support the order by) is very academic.

   That is, for the query:

   DB.find(MUser.class)
         .orderBy("roles")
         .findList();

   ... the orderBy does not actually have utility value. The orderBy is useful when the query also includes a .fetch("roles").

   Options:

   So the options for a query with an orderBy() on a M2M relationship but with no associated fetch of the M2M.

   • Deem the result to be correct (even if it's odd to have duplicates, findSet() vs findList() difference, but it's an odd query anyway)
   • Deem the query to be invalid, detect it and throw an error saying why it's invalid
   • Run the query and de-duplicate the resulting beans
   • Deem the orderBy to be invalid and ignore the orderBy for this case

   Hmm.