How Activerecord Load Object Set In Complicated Relationship

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!


take a look at this:

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

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.

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 = user_subject.subject_id

  join user on user_subject.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?

question doesn’t have right answer yet


Keep debugging!

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.

Yeah, that’s great. :)