<-Newbie: Querying... relations

Hey! I’m having trouble getting traction on making this query using AR. I’ve read docs, and I’ve searched forums, but nothing I’ve seen quite works.

I’m trying, through AR to get a property of a record that is two tables away.

Page table has collectionID

Collection table has suiteID

Suite has ‘title’, which is what I want.

There is one suite which has many collections, and each collection has many pages. Each collection belongs to one suite. Each page belongs to one collection.

In the page model, there is a relation that was automatically generated by gii based on my FKs:

‘collection’ => array(self::BELONGS_TO, ‘Collection’, ‘collectionId’),

and I was able to use collection.suiteId in a CGridView display. Based on what I saw in the "Relational Query with through" section of the guide, I thought that I would be able to create the following relation:

‘suite’=>array(self::HAS_ONE,‘Suite’,array(‘suiteId’=>‘id’),‘through’=>‘collection’),

and use suite.title in a CGridView display. I get the following error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘collection.collectionId’ in ‘where clause’. The SQL statement executed was: SELECT suite.id AS t1_c0, suite.title AS t1_c1, suite.permissions AS t1_c2, suite.description AS t1_c3, suite.thumbnail AS t1_c4 FROM Suite suite LEFT OUTER JOIN Collection collection ON (collection.suiteId=suite.id) WHERE (collection.collectionId=:ypl0)

I’m really not sure where it’s getting “collection.collectionId=:ypl0”

Should I be going about this in an entirely different way? Am I completely incorrect about the problem? Is it just something that I’m idiotically overlooking? If you could be as specific as possible, I’d really appreciate it. I’ve bean a procedural Perl and PHP developer for a while, and I’m attempting to get OO programming, MVC, AR and Yii all at once. Really loving some of the functionality, but some other stuff I’m finding, even after reading the documentation as a noob, to be a bit opaque.

Once again, I’m a nood and I really appreciate your help. I fully intend on spending plenty of time on the forums to answer questions once I get my leet skillz.

So what is your starting model? Suite?

Seems like you need a HAS_MANY relation for your suite:

‘collection’ => array(self::HAS_MANY, ‘Collection’, ‘suiteId’),

In your collection table, you should have a column that references the primary key of your suite table.

Thanks for the reply!

No, the starting model is Page. There are a few hundred thousand pages, each of them belongs to one of a few dozen collections, and each of those collections belongs to one of a handful of suites. So each suite has a handful of collections, and each collection will have up to several thousand pages, and each page belongs to one collection, and each collection belongs to one suite. I would like to access the information from ‘Suite’ from the page model. Is there some fundamental problem with doing that which I’m just being a moron about? (I certainly haven’t ruled out that possibility )

Page has a collectionID field, which is an FK that references the id of its Collection. Collection has a suiteID field which is an FK that references the id of its Suite.

Once again, thanks for taking the time to help me out.

Can you post the relations for each model?

Hi chefandy,

Model "Page" Relation:

'collection' => array(self::BELONGS_TO, 'Collection', 'collectionID'),

Model "Collection" Relation:

'suite' => array(self::BELONGS_TO, 'Suite', 'suiteID'),

And then you can access the title property of Suite in your page model:

$model = Page::model()->with('collection.suite')->findByPk($id);

echo $model->collection->suite->title;