EntityFieldQuery with pager and db_or using addTag

Question

I'm having an issue that I hope some of you can help me with.

Drupal version: 7.36

I've created a list of all nodes of a certain content type using EntityFieldQuery. Since there are a lot of nodes with this content there's also pagination with 6 nodes pr page (not that it matters). Right above my list I have a little search field, where the user can search within this list of nodes with the specified content type. With this I'm using the addTag and db_or method described here: Using hook_query_TAG_alter to add 'OR' queries to EntityFieldQuery. With this the user searches both in the title and the content.

However. For some reason it doens't quite work while having $query->pager() or $query->range() and using the $query->leftJoin() function at the same time. If I outcomment the $query->leftJoin() and the connected db_or->condition() everything works fine. Or if I outcomment the $query->pager(6) everything works fine as well.

Here is all my code for getting the list of content types:

function node_load_by_type($type, $options = array()) {
    $settings = array(
        'limit' => array_key_exists('limit', $options) ? $options['limit'] : -1,
        'pager' => array_key_exists('pager', $options) ? true : false,
        'offset' => array_key_exists('offset', $options) ? $options['offset'] : 0,
        'orderDirection' => array_key_exists('orderDirection', $options) ? $options['orderDirection'] : 'DESC',
        'keywords' => array_key_exists('keywords', $options) ? $options['keywords'] : false,
    );

    $query = new EntityFieldQuery();
    $query->entityCondition('entity_type', 'node')
        ->entityCondition('bundle', $type)
        ->propertyCondition('status' , 1)
        ->propertyCondition('nid', arg(1), '!=')
        ->propertyOrderBy('created', $settings['orderDirection']);

    if ($settings['limit'] !== -1 && $settings['pager']) {
        $query->pager($settings['limit']);
    } else if ($settings['limit'] !== -1) {
        $query->range($settings['offset'], $settings['limit']);
    }

    if ($settings['keywords']) {
        $query->addTag('search');
        $query->addMetaData('keywords', $settings['keywords']);
    }

    $results = $query->execute();

    if (is_array($results) && array_key_exists('node', $results)) {
        $results = node_load_multiple(array_keys($results['node']));

        if ($settings['limit'] !== -1 && $settings['pager']) {
            $results = theme('article_list', array('nodes' => $results, 'pager' => theme('pager', $query->pager)));
        }
    }

    return $results;
}

function THEME_query_search_alter(QueryAlterableInterface $query) {
    $keywords = $query->getMetaData('keywords');
    $words = "%" . $keywords . "%";

    $or = db_or()->condition('title', $words, 'LIKE');
    $query->leftJoin('field_data_body', 'n', 'node.nid = n.entity_id');
    $or->condition('n.body_value', $words, 'LIKE');
    $query->condition($or);
}

Has anyone experienced this before and found the solution? Or can simply see in my code somewhere that there's an error which is causing my problem. Ideas for doing it differently is also welcome.

Thanks in advance. :)

---

UPDATE:

More testing tells me that what triggers this is actually joining another database table into the $query. If I don't use pagination but still the $query->leftJoin(), $query->join() or any similar functions the search actually returns a count of a lot of results even though there might only be 1 result.

Using the global variable $pager_total_items I can see that when there's actually only 1 result, it tells me that there are 1917 results. Then the pagination goes mental and doesn't know what to do, so it creates a lot of pages with the 1 result on each page.

Answer 1

I found the answer to this issue.

Apparently the THEME_query_search_alter hook was triggered twice (also saw this happen when altering the standard Drupal user search query) and the new table was therefore joined into the final database query twice.

So when it tried to find results in the same table two times it kinda broke the search results, and returned these many many results instead of just a few.

The fix was then to check if the new table was already joined into the query, and this is how I did it (only changed the THEME_query_search_alter hook):

function hedeselskabet_query_search_alter(QueryAlterableInterface $query) {
    $keywords = $query->getMetaData('keywords');
    $words = "%" . $keywords . "%";

    if (!array_key_exists('n', $query->getTables())) {
        $or = db_or()->condition('title', $words, 'LIKE');
        $query->leftJoin('field_data_body', 'n', 'node.nid = n.entity_id');
        $or->condition('n.body_value', $words, 'LIKE');
        $query->condition($or);
    }
}

And now everything works. :)