[Dbix-class] order_by interfering with has_many fetch

[Peter Rabbitson]

On 11/14/2015 10:46 PM, Darin McBride wrote:
> ...
>   order_by => ['me.name'],
> ...
>
...
> DBIx::Class::ResultSet::_construct_results(): Unable to properly collapse
> has_many results in iterator mode due to order criteria - performed an eager
> cursor slurp underneath. Consider using ->all() instead at /data/Lacuna-
> Server/lib/Lacuna/DB/Result/Empire.pm line 622
>
> The earlier message implied that it would be fixed in 0.082700_05, but I'm
> using 0.082810 at the moment.  So, either it wasn't fixed, or I'm encountering
> something different.

It is (I suspect) a different issue, and if the case - the warning (it 
is not an error) needs better wording. If 'name' does NOT have a unique 
non-nullable constraint declared on it - i.e. you can have several 'me's 
with identical 'name's - then DBIC can not rely on the results coming 
from the DBI cursor to be "grouped in slabs" representing every object 
hierarchy "hanging off" every individual 'me' instance. This is why it 
needs to exhaust the cursor to give you a "certifiably correct" answer.

> If it helps, Map::Body has 0 or 1 empires (can be null)

Your analysis is correct - the order on 'empire.id' ought to work, but 
this is extra (rather complicated) logic that currently is not 
implemented: note this TODO 
https://metacpan.org/source/RIBASUSHI/DBIx-Class-0.082820/t/prefetch/multiple_hasmany_torture.t#L131-132

> Any guidance on how to update this order_by to get DBIC to only fetch one row
> at a time would be appreciated.

Simply order by [ me.name, me.<primary_key> ] <--- this will ensure 
things are ordered in a *stable* manner on the leftmost side, and the 
collapse will proceed correctly. A PR/patch amending the warning text 
and/or the prefetch documentation to make this clearer will be *very* 
much appreciated.

> I'm expecting that once I roll this out into production and it gets wide use,
> we'll have a thousand rows ("Map::Body" objects) being returned, and up to 40
> or 50 empires, so I'd rather not resort to ->all to save some memory in the
> Plack processes.

Please note that depending on your DBD, DBI may *still* be loading the 
entire resultset into memory even if DBIC uses ->next on the surface. 
For example DBD::Pg does this invariably, and DBD::mysql does it by 
default unless you set https://metacpan.org/pod/DBD::mysql#mysql_use_result

Hope this is sufficient to get you going

Cheers!