Child pages
  • Create iterator for N+1 problem solving [5.3.0-B1]
Skip to end of metadata
Go to start of metadata

Code like below is current solution for N+1 problem:

<?php
/* @var $item kDBItem */
$item = $this->Application->recallObject('item-prefix', null, array('skip_autoload' => true));
$where_clause = array(
	$item->TableName . '.' . $item->IDField . ' IN (' . implode(',', $item_ids) . ')',
);
$sql = $item->GetSelectSQL() . '
		WHERE (' . implode(') AND (', $where_clause) . ')';
$items = $this->Conn->Query($sql);
foreach ( $items as $item_data ) {
	$item->LoadFromHash($item_data);
	// do something with the $item
}

The problem is that 95% of that code is the same all the time and we need to type it again and again to iterate over items.

Solution

Create an iterator class, that would be created using $item and $where_clause and would internally do DB query and LoadFromHash method class. This way code from above is transformed to:

/* @var $item kDBItem */
$item = $this->Application->recallObject('item-prefix', null, array('skip_autoload' => true));
$where_clause = array(
	$item->TableName . '.' . $item->IDField . ' IN (' . implode(',', $item_ids) . ')',
);
 
foreach (new ItemIterator($item, $where_clause) as $item) {
    // do something with the $item
}

Internally the iterator will:

  • do DB query in "rewind" or maybe "__construct"
  • do "LoadFromHash" when "prev" or "next" methods are called

Maybe we can even move creation of $item object inside the iterator and just replace %1$s with $item->TableName in each WHERE clause given. In such case code will look like this:

$where_clause = array(
	'%1$s.Id IN (' . implode(',', $item_ids) . ')',
);
 
foreach (new ItemIterator('item-prefix', $where_clause) as $item) {
    // do something with the $item
}

Related Tasks

1 Comment

  1. Erik Snarski [Intechnic], over Skype, suggested another idea: we can give Filter object (not one, used with kDBList::SetCustomQuery method), that will determine final WHERE clause used. This way filters can be like this:

    $where_clause = array(
        '%1$s.Id IN (' . implode(',', $item_ids) . ')',
    );
      
    foreach (new ItemIterator('item-prefix', new RawItemFilter($where_clause)) as $item) {
        // do something with the $item
    }
     
    foreach (new ItemIterator('item-prefix', new IdsItemFilter($item_ids)) as $item) {
        // do something with the $item
    }