[Kable]

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!

[ragua]

Hi,
take a look at this:
http://www.yiiframew...iteria-together

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

[Kable]

ragua, on 22 February 2013 - 08:23 AM, said:

Hi,
take a look at this:
http://www.yiiframew...iteria-together

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.

[softark]

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.

Kable, on 22 February 2013 - 08:12 AM, said:

table user (id int, ......)
table subject (id int, usn int, ......)
table user_subject (user_id int, subject_id int, role int)
...

$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.

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?

[Kable]

question doesn't have right answer yet

[softark]

Kable, on 22 February 2013 - 09:55 AM, said:

question doesn't have right answer yet

Keep debugging!

[Kable]

softark, on 22 February 2013 - 07:42 PM, said:

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.

[softark]

Yeah, that's great.