DBIx::Class infinite results

Question

Before I describe the details, the problem is, I run a $c->model('ResultName')->search({k=>v}) and when I loop on the results of it's has_many relation, there's only one in the database, yet it loops forever. I've tried googling and found one person who solved the problem, but with too brief an explanation for me. His post was here.

Basically I have 3 tables

Orders, OrderItems and Items. Items are what's available. Orders are collections of Items that one person wants. So I can tie them all together with something like

select oi.order_item_id,oi.order_id,i.item_id from orders as o inner join order_items as oi on oi.order_id = o.order_id inner join items as i on i.item_id = oi.item_id where blah blah blah....

I ran DBIx::Class::Schema::Loader and got what seemed like proper relationships

• MyApp::Schema::Result::Order->has_many('order_items'...)

• MyApp::Schema::Result::Items->has_many('order_items'...)

• MyApp::Schema::Result::OrderItems->belongs_to('items'...)

in a test I try

my $orders = $schema->resultset('Order')->search({
 'user_id'=>1
});

while(my $o = $orders->next) {
  while(my $oi = $o->order_items->next) {
    warn('order_item_id: '.$oi->order_item);
  }
}

It loops infinitely on the inner loop

Answer 1

Your solution works but it loses the niceties of next in that it is an iterator. You are in effect loading all the rows as objects into memory and looping over them.

The issue, as you said is that $o->order_items->next recreates the order_items resultset each time. You should do this:

my $orders = $schema->resultset('Order')->search({
 'user_id'=>1
});

while(my $o = $orders->next) {
  my $oi_rs = $o->order_items;
  while(my $oi = $oi_rs->next) {
    warn('order_item_id: '.$oi->order_item);
  }
}

Answer 2

Reading more carfully in the ResultSet documentation for "next" I found

"Note that you need to store the resultset object, and call next on it. Calling resultset('Table')->next repeatedly will always return the first record from the resultset."

from here

When I changed the loops to

for my $o ($orders->all) {
  for my $oi ($o->order_items->all) {
     # stuff
  }
}

all is well.