Eager Loading with criteria condition affecting main result ?

Yii 1.1 General Discussion
1

Hello All,

I’m trying to load a record with its related object. But when criteria condition applied, if the related object is not found, then the result model will be null. Please look at my code below:




$model = Post::model()->with(array(

			'inCategory.category.translatedCategories' => array('select' => 'categoryId,name', 'condition' => "language='" . Yii::app()->language . "'"),

			'attachments' => array('alias' => 'at', 'order' => 'at.orderNum ASC'),

			'projectCrews' => array('condition' => 'isUploaderApprove=1 AND isCrewApprove=1'),

		))->findByAttributes(array('id' => $postId));

I want to get the post with related ‘projectCrews’. The problem is when the post doesn’t have related ‘projectCrews’ it won’t appear in the result model.

Any idea?

Thank You

2

I guess you would need a LEFT OUTER JOIN instead of an INNER JOIN which is used by default, so try


$model = Post::model()->with(array(

    'inCategory.category.translatedCategories' => array('select' => 'categoryId,name', 'condition' => "language='". Yii::app()->language . "'"),

    'attachments' => array('alias' => 'at', 'order' => 'at.orderNum ASC'),

    'projectCrews' => array('joinType'=>'LEFT OUTER JOIN','condition' => 'isUploaderApprove=1 AND isCrewApprove=1'),))->findByAttributes(array('id' => $postId));

EDIT: Just saw that LEFT OUTER JOIN seems to be the default already, so the problem may be the condition. If you don’t have a post associated with a “projectCrew” the condition “isUploaderApprove=1 AND isCrewApprove=1” might implicitly fail cause these values are empty too (?). Because of the left join the values for isUploaderApprove and isCrewApprove will be NULL if there is no assignment so you might have to include that in an OR statement


isUploaderApprove=1 AND isCrewApprove=1 OR (isUploaderApprove IS NULL AND isCrewApprove IS NULL)
3

@Haensel

Thanks for your reply.

Yes, the default join type is LEFT OUTER JOIN. I also tried your idea, but it also didn’t work.

Maybe we need to use another approach to apply condition together with eager loading approach.

Anybody have any idea? Or maybe any documentation already exist about eager loading with condition?

Thank you

4

I think it would be easier if you could post your schema and how tables are related + the SQL code that’s actually created