[Solar-talk] where select in related tables

[Jeff Surgeson]

Hi All

I am battling to get a where clause to work in a query when the where clause 
is based on a related table.

I have two tables, an artical table with related category table.

#############################
$this->_table_name = 'articals';

$this->_table_cols = array(
    'id' => array(
        'type'    => 'int',
        'require' => true,
        'primary' => true,
        'autoinc' => true,
    ),
    'title' => array(
        'type'    => 'varchar',
        'size'    => 255,
    ),
    'description' => 'clob',
    'main_cat' => array(
        'type'    => 'int',         
    ),
);

$this->_hasOne('articalcat', array(
    'foreign_class' => 'ArticalCat',
    'native_col'    => 'main_cat',
    'foreign_col'   => 'id',
));

###########################
$this->_table_name = 'articalcat';

$this->_table_cols = array(
    'id' => array(
        'type'    => 'int',
        'require' => true,
        'primary' => true,
        'autoinc' => true,
    ),
    'main_cat_name' => array(
        'type'    => 'varchar',
        'size'    => 255,
        'require' => true,
    ),
    'sub_cat_name' => array(
        'type'    => 'varchar',
        'size'    => 255,
        'require' => true,
    ),
);

$this->_belongsTo('articals', array(
    'foreign_class' => 'Articals',
    'foreign_col'   => 'main_cat',
    'native_col'    => 'id',
));


############################
$this->_articals = Solar::factory('Bulldog_Model_Articals');        

public function actionList($category)
{
    $params = array(
        'where'  => array(
            'articalcat.sub_cat_name = ?' => $category,
        ),
    );

    // get the list and assign to view
    $this->list = $this->_articals->fetchAll($params);

}

I know that the relationships between the table are working, because without 
the above where clause

foreach ($this->list as $item) {
    echo $item->articalcat->main_cat_name; 
}

in my view produces correct results.

How do I specify a where clause based on the related category table.
$category passed to actionList() is \'Using the Sun\' which is a sub_cat_name 
field.

-- 
...........::::::...........
Jeff Surgeson / South Africa