Re: Query with conditions in HABTM models
Hello David, the solution you came up with is rally BAD!!! people burn in hell for code like that. seriously look at what you are doing 1. you select all records from your linking table (which is double the size of your posts table. assuming every post has 1-4 tags on average) in one query. insane right? 2. then you go through entire record set extracting post ids (lot's of duplicates because every post has 2-4 tags on average) 3. you do full index scan on your posts table (2-4 time for every post, because you didn't do distinct select in previous insane steps) excluding virtually every record except few latest uncategorized posts. in the long run those 3 lines of code will crush your server. perhaps you should look into habtm countercache. although it is not implemented for habtm as core feature. i've come across some articles with workarounds do that and it will come down to one indexed select on countercache field where countercache is zero Cheers On Fri, Apr 29, 2011 at 5:53 AM, davidhc wrote: > Ignore my previous message... > > I have solved it with this http://bin.cakephp.org/view/471586323 > > Thank you again, cricket :D > David. > > On 29 abr, 10:17, davidhc wrote: >> Only one more doubt... >> >> Could I use the native cakephp paginator with this model function? > > -- > Our newest site for the community: CakePHP Video Tutorials > http://tv.cakephp.org > Check out the new CakePHP Questions site http://ask.cakephp.org and help > others with their CakePHP related questions. > > > To unsubscribe from this group, send email to > cake-php+unsubscr...@googlegroups.com For more options, visit this group at > http://groups.google.com/group/cake-php > -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
Re: Query with conditions in HABTM models
Ignore my previous message... I have solved it with this http://bin.cakephp.org/view/471586323 Thank you again, cricket :D David. On 29 abr, 10:17, davidhc wrote: > Only one more doubt... > > Could I use the native cakephp paginator with this model function? -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
Re: Query with conditions in HABTM models
Only one more doubt... Could I use the native cakephp paginator with this model function? Thanx! On 29 abr, 10:13, davidhc wrote: > Thank you very much, cricket! > > I have change your code: > $ids = Set::extract($this->PostCategory->find('all'), > '{n}.PostCategory.post_id'); > with: > $ids = Set::extract($this->PostsCategory->find('all'), > '{n}.PostsCategory.post_id'); > and it works fine :) -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
Re: Query with conditions in HABTM models
Thank you very much, cricket! I have change your code: $ids = Set::extract($this->PostCategory->find('all'), '{n}.PostCategory.post_id'); with: $ids = Set::extract($this->PostsCategory->find('all'), '{n}.PostsCategory.post_id'); and it works fine :) Thank you! David. On 28 abr, 21:22, cricket wrote: > On Thu, Apr 28, 2011 at 12:31 PM, david hc wrote: > > Hello, > > I have a table posts related with table categories with de habtm table > > posts_categories. > > > posts.id > > categories.id > > posts_categories.post_id > > posts_categories.category_id > > > I'm trying to get all the records WITHOUT category (because I have > > posts taken from RSS and not categorized yet). > > > My model Post has the habtm array, and it works fine (I get all the > > posts with there categories when I find all). > > > My question is how could I get all the records in posts table that are > > not included in related table. Any idea? > > Something like this should work. > > public function uncategorized() > { > $this->set( > 'data', > $this->Post->fetchOrphans() > ); > > $this->set( > 'categories', > $this->Post->Category->find('list') > ); > > } > > Post: > public function fetchOrphans() > { > $ids = Set::extract( > $this->PostCategory->find('all'), > '{n}.PostCategory.post_id' > ); > return $this->find( > 'all', > array( > 'conditions' => array( > 'NOT' => array( > 'Post.id' => $ids > ) > ) > ) > ); > > } > > Not the most elegant code as it it relies on two queries > and--worse--uses IN, which isn't handled well by MySQL at least. But > it should do the job and, given the use case, it's not like this > method is going to be called constantly. I generally don't worry too > much about performance for these sorts of little-used admin functions > (within reason, of course). > > Also, Cake's automagic joining puts the joined table names in > alphabetical order, so your table should be categories_posts and the > model CategoryPost. This isn't completely necessary if you've created > a model for the join but, in order to be sure you don't run into a > gotcha down the road, it may be worth following Cake's convention. -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
Re: Query with conditions in HABTM models
On Thu, Apr 28, 2011 at 12:31 PM, david hc wrote: > Hello, > I have a table posts related with table categories with de habtm table > posts_categories. > > posts.id > categories.id > posts_categories.post_id > posts_categories.category_id > > I'm trying to get all the records WITHOUT category (because I have > posts taken from RSS and not categorized yet). > > My model Post has the habtm array, and it works fine (I get all the > posts with there categories when I find all). > > My question is how could I get all the records in posts table that are > not included in related table. Any idea? Something like this should work. public function uncategorized() { $this->set( 'data', $this->Post->fetchOrphans() ); $this->set( 'categories', $this->Post->Category->find('list') ); } Post: public function fetchOrphans() { $ids = Set::extract( $this->PostCategory->find('all'), '{n}.PostCategory.post_id' ); return $this->find( 'all', array( 'conditions' => array( 'NOT' => array( 'Post.id' => $ids ) ) ) ); } Not the most elegant code as it it relies on two queries and--worse--uses IN, which isn't handled well by MySQL at least. But it should do the job and, given the use case, it's not like this method is going to be called constantly. I generally don't worry too much about performance for these sorts of little-used admin functions (within reason, of course). Also, Cake's automagic joining puts the joined table names in alphabetical order, so your table should be categories_posts and the model CategoryPost. This isn't completely necessary if you've created a model for the join but, in order to be sure you don't run into a gotcha down the road, it may be worth following Cake's convention. -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php
Query with conditions in HABTM models
Hello, I have a table posts related with table categories with de habtm table posts_categories. posts.id categories.id posts_categories.post_id posts_categories.category_id I'm trying to get all the records WITHOUT category (because I have posts taken from RSS and not categorized yet). My model Post has the habtm array, and it works fine (I get all the posts with there categories when I find all). My question is how could I get all the records in posts table that are not included in related table. Any idea? All my attempts (conditions, contain...) are wrong :( Thank you very much! David. -- Our newest site for the community: CakePHP Video Tutorials http://tv.cakephp.org Check out the new CakePHP Questions site http://ask.cakephp.org and help others with their CakePHP related questions. To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php