Thanks brian. I checked those out and after a few attempts I got it working....that is until I tried to add a 'contain'.
If I try to bring in some more data with the contain then the whole thing returns nothing. If I comment out the 'group' then I get data again, although this is the wrong data. I'm afraid my SQL skills are not good to know why it would do this...ah well. I do still have something that works using my previous way, even if it's not the best. Perhaps in the future when I get more time I'll spend several days (weeks more likely ;- ) trying to figure out that solution. Cheers, Mike On Apr 13, 6:54 pm, brian <[email protected]> wrote: > 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-...http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-... > > 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 -~----------~----~----~----~------~----~------~--~---
