I didn't realise it was HABTM. That changes things. See these posts: http://teknoid.wordpress.com/2008/08/06/habtm-and-join-trickery-with-cakephp/ http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find
On Mon, Apr 13, 2009 at 4:48 AM, Mike Cook <[email protected]> wrote: > > brian, when I try this though I get an " Unknown column > 'Book.genre_id' in 'where clause' " error. > > SELECT `Book`.`id`, `Book`.`title`, `Book`.`slug`, `Language`.`id`, > `Language`.`name` FROM `books` AS `Book` LEFT JOIN `languages` AS > `Language` ON (`Book`.`language_id` = `Language`.`id`) WHERE > `Book`.`genre_id` = ('1') ORDER BY `slug` ASC > > I guess then Cake 'should' be able to access genre_id from the JOIN > table, but I don't know why it is not. I believe the HABTM > relationships are set up correctly; > > // book.php > var $hasAndBelongsToMany = array( > 'Genre' => array( > 'className' => 'Genre', > 'joinTable' => 'books_genres', > 'foreignKey' => 'book_id', > 'associationForeignKey' => 'genre_id', > 'unique' => true, > 'order' => 'name ASC' > ), > ) > > // genre.php > var $hasAndBelongsToMany = array( > 'Book' => array( > 'className' => 'Book', > 'joinTable' => 'books_genres', > 'foreignKey' => 'genre_id', > 'associationForeignKey' => 'book_id', > 'unique' => true, > 'order' => 'slug ASC', > ) > ); > > > Mike > > On Apr 12, 9:25 pm, brian <[email protected]> wrote: >> There's no need to search on Genre as you have the foreign key already. >> >> function similar($genre_id) >> { >> return $this->find( >> 'all', >> array( >> 'fields' => array( >> 'Book.id', >> 'Book.title', >> 'Book.slug' >> ), >> 'conditions' => array( >> 'Book.genre_id' => $genre_id >> ), >> 'contain' => array( >> 'Author' => array( >> 'fields' => array( >> 'Author.id', >> 'Author.slug', >> 'Author.lastname' >> ) >> ), >> 'Language' => array( >> 'fields' => array( >> 'Language.id', >> 'Language.name' >> ) >> ) >> ) >> ) >> ); >> >> } >> On Sun, Apr 12, 2009 at 8:25 AM, Mike Cook <[email protected]> wrote: >> >> > Okay, so after more searching around I found a post by pkclarke who >> > makes use of a search on the join table, and then uses a find() with >> > those results. >> >> > I still have the feeling this is not the best way it should be done, >> > but I think this approach will allow me to move forward....and no >> > requestAction. >> >> > // in the book.php model >> > function similar($id) { >> >> > $books_genres = $this->BooksGenre->find('list', >> > array( >> > 'conditions' => array('genre_id' => $id), >> > 'fields' => array('book_id'), >> > 'limit' => 5, >> > 'order' => 'RAND()', >> > ) >> > ); >> >> > $similarBooks = array(); >> > foreach ($books_genres as $value) { >> > $book = $this->find('first', >> > array( >> > 'conditions' => array('Book.id' => $value), >> > 'contain' => array( >> > 'Author' => array('fields' => array('id', 'slug', >> > 'lastname')), >> > 'Language' => array('fields' => array('id', 'name')), >> > ), >> > 'fields' => array('id', 'slug', 'title') >> > ) >> > ); >> > $similarBooks[] = $book; >> > } >> > return $similarBooks; >> > } >> >> > // books_controller.php >> > // $genre_id is an array() of Genre ID's associated with the >> > current book being viewed >> > $similar = $this->Book->similar($genre_id); >> >> > I was also pleasantly surprised that I could pass the $id array to >> > 'conditions' and it would use it as an 'OR' :) >> >> > Thanks for your help Joe, your comments really helped to point on the >> > right path. >> >> > Mike >> >> > On Apr 11, 8:45 pm, Mike Cook <[email protected]> wrote: >> >> Hi Joe, >> >> >> During my experimentations I did come up with something similar, but >> >> one of the problems is how to set the conditions to work on the Genre >> >> ID. >> >> >> So, I have a book that has two genre, "Mystery" (id=5) and "Short >> >> Story" (id=20) and if I only try to search with say, "Mystery"; >> >> >> $id = 5; >> >> $this->Genre->Book->similar($id); >> >> >> The problem is that I don't know how to set the 'conditions' in the >> >> find() so that it only brings up Books that have a Genre ID of 5. >> >> >> I can't get 'conditions to work and I thought maybe I could use; >> >> >> 'contain => array('Genre.id' => $id), >> >> >> but I get the error; Model "Genre" is not associated with model "5" -- >> >> obviously I don't know what I'm doing. :-| >> >> >> How would I go about setting the conditions to grab books that only >> >> have that Genre ID? >> >> >> Mike >> >> >> On Apr 11, 7:31 pm, Joe Critchley <[email protected]> wrote: >> >> >> > You've currently got your similar() function in your >> >> > genres_controller, whereas the primary model for the query is the Book >> >> > model (as it's finding related *books*, not genres). >> >> >> > I believe the following would be a more scalable approach. >> >> >> > Place your similar() function into your Book model. (So it would >> >> > be ....... $similarBooks = $this->find('all')............ and just >> >> > return your results). >> >> >> > Then call this model's function from wherever you need it, whether it >> >> > be the genres_controller: >> >> >> > $this->Genre->Book->similar($id); >> >> >> > ... or your books_controller.... >> >> >> > $this->Book->similar($id); >> >> >> > .... or even from a completely unrelated model (use sparingly).... >> >> >> > $Book = ClassRegistry::init('Book'); >> >> > $similar = $Book->similar(); >> >> >> > It's possible that I'm missing the actual issue, but two important >> >> > points: 1) never use requestAction (as you probably know), and 2) >> >> > there should never be any variables in your views that aren't >> >> > available in your controllers, because there shouldn't be any new >> >> > variables assigned. If this is ever required, you're likely in need of >> >> > a Helper class. And yes, it would be worth looking at creating >> >> > elements for anything like "Similar books", or "5 Related Authors". >> >> > You can pass variables into elements. >> >> >> > I hope this helps. >> >> >> > On Apr 11, 5:00 pm, Mike Cook <[email protected]> wrote: >> >> >> > > In my books view.ctp, I want to have a list of 5 books that are >> >> > > related via the genres data, which has a HABTM relationship. Although >> >> > > I can do this by placing a function similar() in the >> >> > > genres_controller, I have to call it from the view with requestAction. >> >> >> > > // genres_controller.php >> >> > > function similar($id = null) { >> >> > > $similarbooks = $this->Genre->Book->find('all', array( >> >> > > 'contain' => array( >> >> > > 'Author' => array('fields' => array('id', 'slug')), >> >> > > ), >> >> > > 'fields' => array('id', 'slug', 'title'), >> >> > > 'limit' => 5, >> >> > > 'order' => 'RAND()', >> >> > > )); >> >> > > $this->set(compact('similarbooks')); >> >> > > } >> >> >> > > // Book view.ctp >> >> > > $results = $this->requestAction('/genres/similar/' . $id); >> >> >> > > Of course I don't want to really use requestAction (the only place so >> >> > > far I have used it!) but I would also like to be able to bring up 5 >> >> > > random books from ALL the genres the book is assigned to - at the >> >> > > moment I am just searching on one genre id. >> >> >> > > I also know that this approach is not very good if I wish to include 5 >> >> > > books which are related to say, both Genre and Author. >> >> >> > > At some point down the line I would also like to include a "5 Related >> >> > > Authors" list, so I guess I need a more modular approach (would >> >> > > elements be it?) - the problem is that I am completely stumped, even >> >> > > after a week looking at this! >> >> >> > > The solution I currently have is better than nothing, but I would be >> >> > > very grateful if anyone has an idea on how I could achieve a proper >> >> > > solution. >> >> >> > > Many thanks, Mike. > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
