How Activerecord Load Object Set In Complicated Relationship

Yii 1.1 General Discussion
1

Hi everybody

I’ve three DB tables for two objects with MANY-MANY relationship, as below

table user (id int, …)

table subject (id int, usn int, …)

table user_subject (user_id int, subject_id int, role int)

user_subject table links user and subject tables by foreign key user_id and subject_id.

I’d like to load all subjects associated with user 1 whose role as 1, and have usn between 1 to 20.

I use following code to do this:


$criteria = new CDbCriteria;

$criteria->with = array('users');

$criteria->condition = "user_id=:userID AND role=1 AND usn>=:startUSN AND usn<=:endUSN";

$criteria->params = array(':userID' => 1, ':startUSN' => 1, ':endUSN' => 20);

$subject = Subject::model()->findAll($criteria);

But the result I got for $subject is null.

Could anyone tell me where I did wrong?

Thanks for your time!

2

Hi,

take a look at this:

http://www.yiiframework.com/wiki/280/1-n-relations-sometimes-require-cdbcriteria-together

If it doesn’t work, try to set bad value to see the SQL request sent to database.

3

Thanks ragua.

I tried to add together=true to my code. The outcome is the same as without it. Then I tried to pass string value to an int parameter. But I still get same ‘null’ results and without any error report. So I suppose that the findAll returns before send any request to the database.

4

Hi Kalbe, welcome to the forum.

You don’t have to specify ‘together’ when there’s no ‘OFFSET’ or ‘LIMIT’ in the condition. ‘with’ is enough for the current scenario.

I would try the plain sql for debugging. Something like …




select * from subject

  join user_subject on subject.id = user_subject.subject_id

  join user on user_subject.user_id = user.id

where user_id = 1 and role = 1 and usn >= 1 and usn <= 20

Does it really return some records? Or, is empty result the correct outcome?

5

question doesn’t have right answer yet

6

:D

Keep debugging!

7

Hi softark,

Thank you for your advice!

The problem solved. It was my own mistake, passing wrong value to the criteria parameter.

Thanks again for your time! and have a nice day

This discussion is closed, problem solved.

8

Yeah, that’s great. :)