It will bring back all columns from “paragraph”, though. The select didn’t work as I expected. Without doubt somebody else in this forum can give a better answer.
For sure you have to define an alias in the model ChangeRequest
public function relations()
{
return array(
'paragraph'=>array(self::BELONGS_TO, 'Paragraph', 'paragraphId', 'alias'=>'paragraph'),
}
}
Don’t give up yet. I still think it’s possible, one way or another. For now, I have to continue with something else, but you may want to search for some older posts on relational AR or wait for a better answer from somebody else.
Just on a quick note. I was browsing through this thread and noticed that every one of Tri’s posts that did not solve the problem had been given a negative rating.
I just wanted to point out that these +/- buttons on the posts are to rate the usefulness of a response in regards to your topic but are used to record the reputation of the poster. By giving a negative mark, it decreases the posters reputation (thus making them look less knowledgeable and helpful to others).
As for my thoughts on this topic, have you considered using straight SQL and just passing it the parameters your want? Sometimes (most times) I find that with relational queries it can just be easier to write the SQL by hand and just execute it. This removes the extra overhead of what the framework has to do to generate the query and it guarantees you have the query you want.
I have the methods below in my base ActiveRecord which my User model extends, and can access all Paragraph and ChangeRequest instances that indirectly point to a User.
gazbond, that’s awesome! If this works like you say it does, I’ll develop an immediate man-crush on you I’m going to check this out soon! (Although I’ll probably get to it next week at the earliest, unfortunately)
One thing: A first look through the source makes me think this is a MySQL-only implementation (I see lots of backticks being used). Is this true? And would it somehow be possible to make this DB-independent (I use a lot of SQLite and even MSSQL and would love to use this in my projects!)
Hello again, sorry for the delay (I didn’t promise to have another look the next day).
I read the thread more thoroughly from the top and I think I understand the problem domain. Your relationship chain is primary->has_many->has_many->has_many. I also think I understand your need for an indirect relationship to accomplish the subquery (not tested).
Like I suggested in a previous post, you could start from ChangeRequest instead (my doubt was about which attributes was returned, not records). The relationship chain will be primary->belongs_to->belongs_to (with ChangeRequest as primary and not including the user table). This should work, at least it does in my configuration.
(Neither am I a DB specialist, so won’t comment on your latest post.)
The only thing is that all the Paragraphs are returned even though I changed CDbCriteria->select (I have just upgraded to Yii 1.1 though).
This is the generated SQL:
SELECT `ChangeRequest`.`id` AS `t0_c0`, `ChangeRequest`.`status` AS `t0_c2`,
`ChangeRequest`.`paragraphId` AS `t0_c3`, `Paragraph`.`id` AS `t1_c0`,
`Paragraph`.`text` AS `t1_c1`, `Paragraph`.`articleId` AS `t1_c2`
FROM `ChangeRequest`
LEFT OUTER JOIN `Paragraph` `Paragraph`
ON (`ChangeRequest`.`paragraphId`=`Paragraph`.`id`)
WHERE (Paragraph.articleId = 1)