I am trying to retrieve all the books not read by a particular user. The code I currently have is:
$books = Book::model()->with(array('bookRead'=>array('select'=>false,
'joinType'=>'LEFT JOIN',
'condition'=>"bookRead.user_id != $userId", //$userId is the user we are interested in.
),
)
)->findAll();
This seems to be returning all of the Book entries in my database. Am I misunderstanding how the ‘with’ function above is supposed to work?
First you have to make a list of the books that the user have already read (BookRead).
Then you get all Books that are NOT IN that list.
Try something like this:
public function getBooksNotInBookRead($userId)
{
$criteria=new CDbCriteria;
/* Make a list of BookRead->book_id where BookRead->user_id = $userId
Note: findColumn is in CAdvancedArFindBehavior */
$bookReadList = bookread::model()->findColumn(
'book_id', 'user_id = '. $userId);
/* Read books not in $bookReadList */
$criteria->addNotInCondition('book_id', $bookReadList);
$unreadBooks = CHtml::listData(book::model()->findAll($criteria),
'book_id', 'book_name');
asort($unreadBooks);
return $unreadBooks;
}
If you do not use CAdvancedArFindBehavior, you can use findAll() and then foreach() through the result.
Thanks very much for the help, I will give it a try.
So say for example I was interested in returning all of the books a user has read, would I need to follow the same pattern? I find that if I change the “LEFT JOIN” to an “INNER JOIN” in the ‘with()’ statement, it seems to work. I’m asking as I have various other queries in my application, some of which are quite complex and involve multiple relations in their ‘with()’ statements. They seem to work fine (they all contain an INNER JOIN), although I’m a little concerned that it’s not the right way to filter data out of a search.
If you only want to return the books the user has read, then it is easier than the example I showed above. In this case you want to read BookRead ‘with’ Book.
You want to concentrate on BookRead. You want to get all records in BookRead where BookRead->user_id = $userId.
The only reason to also include Book in this case, is to get the book’s name.
It will be something like this:
$criteria = new CDbCriteria;
// Also get Book record via eager loading
$criteria->with = array(
'relation_from_bookread_to_book' => array(
'select' => 'book_name',
),
);
// Filter records in BookRead
$criteria->compare('user_id', $userID, false);
$booksRead = bookread::model()->findAll($criteria);
// OR, if this code is in the search() function in the BookRead model:
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
));
If you use Active Record, you should not need to set the "JOIN". The relations should do that for you.
Always try to read from the child table towards the parent table i.e. read BookRead and then include Book - like in this example.
Try not to read Book and then include BookRead (has_many relation), because that can become a mission. See this link for examples on reading records via has_many relation.
If you absolutely must read records via a has_many relation, then it is often better to use lazy loading i.e. read all Book records first and then use foreach() on each Book record to get its related BookRead records - rather than trying to struggle through ‘with’ statements.
