I have a media table/model that contains information about various media items, and a mediatags table that contains multiple tags per media item. I have defined a HAS_MANY relationship for media->tags and a BELONGS_TO relationship for mediatags->media, however when I try to run a Media::model()->with("tags")->find() query using a column in the mediatags table in the where clause, I get the following error (you can see that activerecord is not doing the join):
CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘tag’ in ‘where clause’. The SQL statement executed was: SELECT t.mediaId AS t0_c0, t.name AS t0_c1, t.thumbUrl AS t0_c2, t.fullUrl AS t0_c3, t.urlName AS t0_c4, t.dateadded AS t0_c5, t.type AS t0_c6, t.fileName AS t0_c7, t.parentTable AS t0_c8, t.parentId AS t0_c9, t.origUrl AS t0_c10, t.origwidth AS t0_c11, t.origheight AS t0_c12, t.fullwidth AS t0_c13, t.fullheight AS t0_c14, t.thumbwidth AS t0_c15, t.thumbheight AS t0_c16, t.contentLabel AS t0_c17, t.longDesc AS t0_c18, t.status AS t0_c19, t.featured AS t0_c20 FROM media t WHERE (tag = :tag AND type = ‘P’) ORDER BY dateadded DESC LIMIT 16
media table create script:
CREATE TABLE media (
`mediaId` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NULL DEFAULT NULL,
`thumbUrl` VARCHAR(255) NULL DEFAULT NULL,
`fullUrl` VARCHAR(255) NULL DEFAULT NULL,
`urlName` VARCHAR(255) NULL DEFAULT NULL,
`dateadded` DATETIME NULL DEFAULT NULL,
`type` CHAR(1) NULL DEFAULT NULL,
`fileName` VARCHAR(255) NULL DEFAULT NULL,
`parentTable` VARCHAR(255) NULL DEFAULT NULL,
`parentId` INT(11) NULL DEFAULT NULL,
`origUrl` VARCHAR(255) NULL DEFAULT NULL,
`origwidth` INT(11) NULL DEFAULT NULL,
`origheight` INT(11) NULL DEFAULT NULL,
`fullwidth` INT(11) NULL DEFAULT NULL,
`fullheight` INT(11) NULL DEFAULT NULL,
`thumbwidth` INT(11) NULL DEFAULT NULL,
`thumbheight` INT(11) NULL DEFAULT NULL,
`contentLabel` VARCHAR(255) NULL DEFAULT NULL,
`longDesc` TEXT NULL,
`status` VARCHAR(1) NULL DEFAULT NULL,
`featured` BIT(1) NULL DEFAULT NULL,
PRIMARY KEY (`mediaId`)
)
mediatags create script:
CREATE TABLE mediatags (
`mediatagId` INT(11) NOT NULL AUTO_INCREMENT,
`mediaId` INT(11) NOT NULL,
`tag` VARCHAR(255) NOT NULL,
`parentTableName` VARCHAR(255) NULL DEFAULT NULL,
`parentTableId` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`mediatagId`)
)
Media model relationships method:
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'tags'=>array(self::HAS_MANY, 'Mediatag', 'mediaId'),
);
}
Mediatag model relationship method:
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'media'=>array(self::BELONGS_TO, 'Media', 'mediaId'),
);
}
attempted query:
$photos = Media::model()->with(“tags”)->find(array(“condition” => “tag = :tag AND type = ‘P’”, “order” => “dateadded DESC”, “params”=>array(’:tag’=> $tag),
"limit" => (int) $pageSize,
"offset" => (int) $offset
));