Re: Filtering HABTM associations using columns in both models
Hi, For a HABTM you would/could do the following: 1) Create a model for the join table. 2) bindModel a dummy hasOne association for the join table before you search 3) use a constraint that refers to the main model and the join table(not the other model) Working E.g. $this-Post-bindModel(array('hasOne'=array('PostsTag'=array(; $constraint['Post.published'] = ' '.date('Y-m-d H:i:s'); $constraint['PostsTag.tag_id'] = $tagId; // or pass an array to genereate IN (1,2,3..) in the sql. $this-Post-findAll($constraint); woah, lovely, elegant solution there, and nice explanation, cheers andy, will be using this technique a lot me thinks! cheers, jon -- jon bennett t: +44 (0) 1225 341 039 w: http://www.jben.net/ iChat (AIM): jbendotnet Skype: jon-bennett --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com 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 -~--~~~~--~~--~--~---
Re: Filtering HABTM associations using columns in both models
I might be daft but in these cases plain old SQL isn't all that bad is it... $posts = $this-Post-query(SELECT DISTINCT Post.title, Post.body FROM posts as Post LEFT JOIN posts_tags as pt ON Post.id = pt.post_id LEFT JOIN tags as Tag ON Tag.id = pt.tag_id WHERE Tag.id = '$id' AND Post.published NOW()); I haven't tested the actual SQL but something like that should work like a charm and the result would be a Cake compatible array. SQL != evil :) Tijs AD7six wrote: On Dec 19, 12:44 pm, Jon Bennett [EMAIL PROTECTED] wrote: Anyone has suggestions for this on? I'm having similar problems.hmm, I don't think you can with a vanilla HABTM, you need to fake it by creating a hasMany and belongsTo association with a JoinTable model, check out: http://www.thinkingphp.org/2006/10/26/modeling-relationships-in-cakep... hth jb -- jon bennett t: +44 (0) 1225 341 039 w:http://www.jben.net/ iChat (AIM): jbendotnet Skype: jon-bennett Hi All, With a bit of trickery, this problem is not very difficult to solve. If the following explanation isn't sufficient I'll put some examples on my blog (or maybe I'll do that anyway). To perform a search with a constraint in 2 models(tables) you need the two tables to be JOINed together. Key fact to bear in mind: JOINs only appear in the sql for hasOne or belongsTo associations. For a HABTM you would/could do the following: 1) Create a model for the join table. 2) bindModel a dummy hasOne association for the join table before you search 3) use a constraint that refers to the main model and the join table(not the other model) Working E.g. $this-Post-bindModel(array('hasOne'=array('PostsTag'=array(; $constraint['Post.published'] = ' '.date('Y-m-d H:i:s'); $constraint['PostsTag.tag_id'] = $tagId; // or pass an array to genereate IN (1,2,3..) in the sql. $this-Post-findAll($constraint); 1 caveat: If you get, or are at risk of getting, duplicate entries (IN(1,2,3..)), add DISTINCT to the main models id field in the field list (2nd findAll parameter) like so: $fields = array('DISTINCT id','body','published', etc.) HTH, AD7six Please note: The manual/bakery is a good place to start any quest for info. The cake search (at the time of writing) erroneously reports less/no results for the google group. The wiki may contain incorrect info - read at your own risk (it's mainly user submitted) :) You may get your answer quicker by asking on the IRC Channel (you can access it with just a browser here:http://irc.cakephp.org). -- View this message in context: http://www.nabble.com/Filtering-HABTM-associations-using-columns-in-both-models-tf2699228.html#a7988173 Sent from the CakePHP mailing list archive at Nabble.com. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com 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 -~--~~~~--~~--~--~---
Re: Filtering HABTM associations using columns in both models
On Dec 20, 12:03 pm, Tijs Teulings [EMAIL PROTECTED] wrote: I might be daft but in these cases plain old SQL isn't all that bad is it... $posts = $this-Post-query(SELECT DISTINCT Post.title, Post.body FROM posts as Post LEFT JOIN posts_tags as pt ON Post.id = pt.post_id LEFT JOIN tags as Tag ON Tag.id = pt.tag_id WHERE Tag.id = '$id' AND Post.published NOW()); I haven't tested the actual SQL but something like that should work like a charm and the result would be a Cake compatible array. SQL != evil :) Tijs SQL certainly isn't evil :) However if you use query you lose any afterFind, beforeFind stuff plus no ability to modify the recursiveness of the results (because there can be no recursive model fetching behavior if you use query). That's leaving aside that insecure sql is easy to write with query, whereas with cake calls it's very much more difficult to do so and if it's not just a select you'ld lose the validation, afterSave, beforeSave, afterDelete, beforeDelete stuff too. HTH, AD7six Please note: The manual/bakery is a good place to start any quest for info. The cake search (at the time of writing) erroneously reports less/no results for the google group. The wiki may contain incorrect info - read at your own risk (it's mainly user submitted) :) You may get your answer quicker by asking on the IRC Channel (you can access it with just a browser here:http://irc.cakephp.org). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com 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 -~--~~~~--~~--~--~---
Re: Filtering HABTM associations using columns in both models
Yikes, i never knew... i thought (most of) those things would work fine with query(). thanks for the enlightenment. tijs AD7six wrote: On Dec 20, 12:03 pm, Tijs Teulings [EMAIL PROTECTED] wrote: I might be daft but in these cases plain old SQL isn't all that bad is it... SQL certainly isn't evil :) However if you use query you lose any afterFind, beforeFind stuff plus no ability to modify the recursiveness of the results (because there can be no recursive model fetching behavior if you use query). That's leaving aside that insecure sql is easy to write with query, whereas with cake calls it's very much more difficult to do so and if it's not just a select you'ld lose the validation, afterSave, beforeSave, afterDelete, beforeDelete stuff too. HTH, AD7six Please note: The manual/bakery is a good place to start any quest for info. The cake search (at the time of writing) erroneously reports less/no results for the google group. The wiki may contain incorrect info - read at your own risk (it's mainly user submitted) :) You may get your answer quicker by asking on the IRC Channel (you can access it with just a browser here:http://irc.cakephp.org). -- View this message in context: http://www.nabble.com/Filtering-HABTM-associations-using-columns-in-both-models-tf2699228.html#a7989908 Sent from the CakePHP mailing list archive at Nabble.com. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com 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 -~--~~~~--~~--~--~---
Re: Filtering HABTM associations using columns in both models
On 12/20/06, AD7six [EMAIL PROTECTED] wrote: On Dec 20, 12:03 pm, Tijs Teulings [EMAIL PROTECTED] wrote: I might be daft but in these cases plain old SQL isn't all that bad is it... $posts = $this-Post-query(SELECT DISTINCT Post.title, Post.body FROM posts as Post LEFT JOIN posts_tags as pt ON Post.id = pt.post_id LEFT JOIN tags as Tag ON Tag.id = pt.tag_id WHERE Tag.id = '$id' AND Post.published NOW()); I haven't tested the actual SQL but something like that should work like a charm and the result would be a Cake compatible array. SQL != evil :) Tijs SQL certainly isn't evil :) However if you use query you lose any afterFind, beforeFind stuff plus no ability to modify the recursiveness of the results (because there can be no recursive model fetching behavior if you use query). Which makes an excellent case for beforeQuery and afterQuery OTOH if you use a query it is assumed that you are #1 aware as to what you are doing #2 doing it at your own risk. Cheers Tarique -- = PHP Applications for E-Biz: http://www.sanisoft.com Coppermine Picture Gallery: http://coppermine.sf.net = --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com 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 -~--~~~~--~~--~--~---
Re: Filtering HABTM associations using columns in both models
Anyone has suggestions for this on? I'm having similar problems. On Nov 24, 2:46 pm, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I've seen topics that describe how to do something like find all posts with a given tag: $this-Post-Tag-findAll(Tag.id = $id); I'm running into a problem now where I need to find all posts with a given tag and the posts must also have certain values of a certain field. So the models look like: Post - id - title - body - published (DATETIME field) Tag - id - name PostHABTMTag TagHABTMPost So what I'd like to do is find all the posts that match two conditions: 1) Tag.id = $id 2) Post.published = NOW() Any idea how to get both conditions into a single call to findAll? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com 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 -~--~~~~--~~--~--~---
Re: Filtering HABTM associations using columns in both models
Anyone has suggestions for this on? I'm having similar problems. hmm, I don't think you can with a vanilla HABTM, you need to fake it by creating a hasMany and belongsTo association with a JoinTable model, check out: http://www.thinkingphp.org/2006/10/26/modeling-relationships-in-cakephp-faking-rails-throughassociation/ hth jb -- jon bennett t: +44 (0) 1225 341 039 w: http://www.jben.net/ iChat (AIM): jbendotnet Skype: jon-bennett --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Cake PHP group. To post to this group, send email to cake-php@googlegroups.com 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 -~--~~~~--~~--~--~---