Using Yii’s implementation of ActiveRecord makes querying a database much easier for simple queries. For more complex situations, you will still find yourself having to write some straight SQL on occasion. In this case, using a custom query was not necessary. I was trying to add another search parameter, using the AR compare() method, to search a relation with a HAS_MANY relationship, and it was displaying an “unknown column” error. Without a proper example, it wasn’t clear at first how to get this work.

In the controller that deals with the user’s search, I already use the Compare() method to search what was typed in a text box, and compare it again the title and description. When I tried to use this to compare another search parameter against the “peppers.name” field, I received this:

CDbCommand failed to execute the SQL statement: ___ Unknown Column ….

As the error indicates, the query is not including the fields for related tables. After some trial and error, and viewing the generated query, I saw that it did not contain any JOINs for the related table. My Google and forum searches were not turning up a solution, until I came across this post.

The trick is to use the WITH property/method, and the together property. The WITH method:

“Specifies which related objects should be eagerly loaded.”

The together property determines:

“Whether the foreign tables should be joined with the primary table in a single SQL. This property is only used in relational AR queries for HAS_MANY and MANY_MANY relations.”

This will essentially add JOINs to your query, allowing you to access the column/field. Then it can be used normally for the search in the compare method.

These are the Codes you are Looking For: Search Related HAS_MANY

Here is the working code for the additional search field (in the controller):

// Search by type of hot pepper
$p = $_GET['pepper'];
$criteria->compare('peppers.name', $p, true);
$criteria->with = array('peppers'=>array('select'=>'peppers.name','together'=>true));

More About This Example

Using the example of the site I am working on, each hot sauce can be made of many types of hot peppers. The peppers in each sauce are managed via the admin with an array of checkboxes. I am storing them in an associative table (SaucePeppers) that links the ID of the sauce to the ID of the pepper (also its own table/model).

Relations – For Reference

Here is the “peppers” relation I am using, for reference. Note that the “through” syntax in Active Record relations has changed slightly from Yii version 1.1.8, to Yii version 1.1.9. The newer versions use an array in the 3rd parameter:

public function relations()
{
    return array(
        'saucePeppers' => array(self::HAS_MANY, 'SaucePeppers', 'sauce_id'),
        'peppers' => array(self::HAS_MANY, 
            'Peppers', 
             array('peppers_id'=>'id'), 
             'through'=>'saucePeppers'
         ),
    );
}