Please, help me to understand CActiveRecord

Archives Yii 1.0.x Discussions
1

Hello everyone,

I’m trying to figure out AR and to use it instead of hardcoded SQL queries.

So, I had a query:


SELECT

bt.*,btc.topic_text,btc.topic_extra,ua.username

FROM 

'.Table('blogs_topics').' bt,

'.Table('blogs_topics_content').' btc,

'.Table('users_accounts').' ua

WHERE 

bt.blog_id=:blog_id 

AND

bt.topic_status = "published"

AND 

btc.topic_id=bt.topic_id

AND 

ua.id=bt.user_id

After reading documentation, I used yiic to generate some models, and after few tries I ended up with:


$topics = blogs_topics::model()->published()->byBlog(14)->with('author','content')->findAll();

Which works great.

Next step was to do the same (take hardcoded SQL query and make it all cool with AR) with more complex SQL query:


SELECT

bt.*,btc.topic_text,btc.topic_extra,ua.username,

tr.comment_count_last,

b.*,

tqv.voted_user_id

FROM 

'.Table('blogs_topics').' bt 

LEFT JOIN '.Table('blogs_topics_read').' tr ON (tr.topic_id=bt.topic_id AND tr.user_id=:user_id)

LEFT JOIN '.Table('blogs_topics_question_vote').' tqv ON (tqv.topic_id=bt.topic_id AND tqv.voted_user_id=:user_id),

'.Table('blogs_topics_content').' btc,'.Table('users_accounts').' ua, '.Table('blogs').' b

WHERE 

bt.blog_id=:blog_id 

AND

((bt.user_id=:user_id AND (bt.topic_status = "published" OR bt.topic_status = "draft")) OR bt.topic_status = "published")

AND 

btc.topic_id=bt.topic_id

AND

ua.id=bt.user_id

AND b.blog_id=bt.blog_id ORDER BY bt.topic_date_add DESC

But i have no idea how to do it. Where should i move


LEFT JOIN '.Table('blogs_topics_read').' tr ON (tr.topic_id=bt.topic_id AND tr.user_id=:user_id)

and


LEFT JOIN '.Table('blogs_topics_question_vote').' tqv ON (tqv.topic_id=bt.topic_id AND tqv.voted_user_id=:user_id)

blogs_topics_read is a table where I keep track of which topics have been already read by user. It can be empty (left join). Te same with blogs_topics_question_vote table

But i just can’t get it - which models should use what kind of relations()?

Please, help me!

2

As far as I understand, blogs_topics_read contains children rows whose parent is a blogs_topics record. So you may want to add a relation to the latter’s method. The same thing goes to blogs_topics_question_vote.

You can define relation type via relations().

3

Thanks,

but the thing is - there is no primary key in tables blogs_topics_read and blogs_topics_question_vote.

I added relations:


/**

	 * @return array relational rules.

	 */

	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(

			'author'=>array(self::BELONGS_TO, 'users_accounts', 'user_id'),

			'blog'=>array(self::BELONGS_TO, 'blogs', 'blog_id'),

			'content'=>array(self::HAS_ONE, 'blogs_topics_content','topic_id'),




			//'questionVote'=>array(self::HAS_MANY, 'topicQuestionVote', 'topic_id_tqv','on'=>'??.voted_user_id=:user_id', 'joinType'=>'LEFT JOIN'),

			'blogs_topics_read'=>array(self::HAS_MANY, 'blogs_topics_read', 'topic_id','on'=>'??.user_id=:user_id'/*, 'joinType'=>'LEFT JOIN'*/),

		);

	}

But, it’s not working:

4

What do your tables look like? In a relational database every table should make use of primary keys.

5 

CREATE TABLE `blogs_topics_read` (

  `topic_id` int(11) unsigned NOT NULL,

  `user_id` int(11) unsigned NOT NULL,

  `time_read` int(10) default NULL,

  `comment_count_last` int(10) unsigned NOT NULL default '0',

  KEY `topic_id` (`topic_id`),

  KEY `user_id` (`user_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

As you see, the only way to add primary key is to add dummy "id" field wit AUTO_INCREMENT =\

This table is used to tell how many new comments a node has since the last time user read it

6

Its primary key should be (topic_id, user_id) – composite key containing multiple columns.

7

there can be multiple rows, so topic_id-user_id is not unique (