Sorting on a ManyToMany relationship

[Ichtil]

Expected behavior

Querying a bean with a Many to Many relationship and sorting on that relationship should not duplicate returned list. I am not sure if sorting by many to many relationship is a supported scenario.

Actual behavior

Query returns duplicate beans.

Steps to reproduce

Please see attached failing PR #3418

[rbygrave]

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.