Magento Advanced Collection Filters

I always seem to end up in a position where I need more specific information from a collection than addFilter() can give me. Functions like addAttributeToFilter() are great if you’re working with EAV-based collections like products and categories, but they don’t work with a lot of core Magento collections. In this post I’ll go into some detail on how to use the ->getSelect() function to filter collection results. This post comes with a warning: The methods detailed below are intended primarily for data output, not data manipulation. Be careful saving models returned from your original collection.

Calling $collection->getSelect() on your collection will return a model of class type Varien_Db_Select. This model represents the SQL query that is performed to select your collection. I’ve created a module called Mby_Testmodule, and within it I have a model of type testmodule/comment. Let’s get the Select element and play with it a bit.

<?php
        $collection = Mage::getModel('testmodule/comment')->getCollection();
        $select = $collection->getSelect();
        echo $select->__toString();

This will echo the following:

SELECT `main_table`.* FROM `mby_testmodule_comment` AS `main_table`

Essentially we’re returning all data. If I want to filter that result I can use a number of functions. Lets search for all comments by people whose name starts with “abcefg”:

<?php
    $name = "abcefg%";
    $select->where("author_name LIKE ?", $name);
    echo $select->__toString();
?>

Outputs:
SELECT `main_table`.* FROM `mby_testmodule_comment` AS `main_table` WHERE (author_name LIKE 'abcefg%')

If we want to reverse the order of the collection:

<?php
    $select->order("comment_id DESC");
    echo $select->__toString();
?>

Outputs:
SELECT `main_table`.* FROM `mby_testmodule_comment` AS `main_table` WHERE (author_name LIKE 'abcefg%') ORDER BY `comment_id` DESC

If we want to then limit the results to the first 20 comments, we can use:

<?php
    $select->limit(20);
    echo $select->__toString();
?>

Outputs:
SELECT `main_table`.* FROM `mby_testmodule_comment` AS `main_table` WHERE (author_name LIKE 'abcefg%') ORDER BY `comment_id` DESC LIMIT 20

Or if we wanted to limit the results to 20 comments, starting from comment 10:

<?php
    $select->limit(20, 10);
    echo $select->__toString();
?>

Outputs:
SELECT `main_table`.* FROM `mby_testmodule_comment` AS `main_table` WHERE (author_name LIKE 'abcefg%') ORDER BY `comment_id` DESC LIMIT 20 OFFSET 10

Say we also wanted to join these comments to the blog posts that they were made against, we could use an inner join like so:

<?php
    $select->joinInner(
        array(
            'blogpost_table' => 'mby_testmodule_blogpost'
        ),
        'blogpost_table.blogpost_id = main_table.blogpost_id'
    );
    echo $select->__toString();
?>

Outputs:
SELECT `main_table`.*, `blogpost_table`.* FROM `mby_testmodule_comment` AS `main_table` INNER JOIN `mby_testmodule_blogpost` AS `blogpost_table` ON blogpost_table.blogpost_id = main_table.blogpost_id WHERE (author_name LIKE 'abcefg%') ORDER BY `comment_id` DESC LIMIT 20 OFFSET 10

You get the idea.

Modifying the Select model will filter the data returned by your collection when you iterate through it. Implementing the process above will look something like the following:

<?php
        $name = "abcefg%";
        $collection = Mage::getModel('testmodule/comment')->getCollection();
        $collection->getSelect()
            ->where("author_name LIKE ?", $name)
            ->order("comment_id DESC")
            ->limit(20, 10)
            ->joinInner(
                array(
                    'blogpost_table' => 'mby_testmodule_blogpost'
                ),
                'blogpost_table.blogpost_id = main_table.blogpost_id'
            );
        foreach ($collection as $comment) {
            echo "Comment #".$comment->getCommentId();
            echo " by ".$comment->getAuthorName();
            echo " in response to ".$comment->getBlogpostTitle();
            echo "<br />";
            echo $comment->getContent();
        }