Yii relation through

Question

I have 3 tables:

House:
id
name
...

Activity:
id
Title
...

Category:
id
title
...

I want to make a relation like $category->houses to return all house which have an activity in the specifique category.

I tried something like:

public function relations() {
    return array_merge(array(
       'houses'=>array(self::HAS_MANY,'House',array('house_id'=>'id'),'through'=>'activite'),
            ), parent::relations());
}

That's gives an error because of my scope:

public function defaultScope() {
        return array(
            'condition' => "deleted='0'",
       );
}

It works if I comment my scope, but I wish make them work togeder!

This is the error message:

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'deleted' in where clause is ambiguous. The SQL statement executed was: SELECT `houses`.`id` AS `t1_c0`, `houses`.`titre` AS `t1_c1`, `houses`.`rabais` AS `t1_c2`, `houses`.`slug` AS `t1_c3`, `houses`.`situation` AS `t1_c4`, `houses`.`conseil_utile` AS `t1_c5`, `houses`.`telephone1` AS `t1_c6`, `houses`.`telephone2` AS `t1_c7`, `houses`.`address1` AS `t1_c8`, `houses`.`address2` AS `t1_c9`, `houses`.`address3` AS `t1_c10`, `houses`.`gmap` AS `t1_c11`, `houses`.`link_site_web` AS `t1_c12`, `houses`.`link_facebook` AS `t1_c13`, `houses`.`link_autre` AS `t1_c14`, `houses`.`region_id` AS `t1_c15`, `houses`.`create_id` AS `t1_c16`, `houses`.`create_time` AS `t1_c17`, `houses`.`update_id` AS `t1_c18`, `houses`.`update_time` AS `t1_c19`, `houses`.`deleted` AS `t1_c20` FROM `house` `houses` LEFT OUTER JOIN `activite` `activites` ON (`activites`.`house_id`=`houses`.`id`) WHERE (deleted='0') AND (`activites`.`categorie_id`=:ypl0)

Answer 1

The error message is telling you that you have more than one column called 'deleted' in your query. You need to specify which table the deleted column is to be compared against. You can do this by specifying in your scope like this;

public function defaultScope() {
        return array(
            'condition' => $this->getTableAlias(FALSE, FALSE).".deleted='0'",
       );
}