Adding Conditions To Join

Yii 1.1 General Discussion
1

My database structure is like this: Each book is listed in the table Books. Each book has many authors which are listed in the Authors table with a bookId. Each author has many children which are listed in the Children table with an AuthorId.

The relations are defined in the Yii models.

I want to find all Books with a blue cover. Of each book with a blue cover I want to fetch all the author’s children with blonde hair.

I tried this:




$books = Book::model()->with(array(

            'authors.children'=>array(

                'joinType'=>'LEFT JOIN',                

                'condition'=>'children.hairColor="blonde"'

                )

            )

        )->findAll();

But because of the condition, only books with authors with blonde children are listed. I want to list all books.

2

Dear i.amniels

Can You please try this?




$books = Book::model()->with(array(

            'authors.children'=>array(              

             'condition'=>"children.hairColor=:hairColor",

             'params'=>array(':hairColor'=>'blonde'),

             'together'=>true,

                  )

            )

        )->findAll('color=:color',array(':color'=>'blue'));//I assumed book.color as color of the book cover.
3

Try to use "on" in place of "condition":




$books = Book::model()->with(array(

            'authors.children'=>array(

                'joinType'=>'LEFT JOIN',                

                'on'=>'children.hairColor="blonde"'

                )

            )

        )->findAll();
4

Thanks phtamas, your suggestion solved my problem.