Friday, April 29, 2011

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 <zijn.digi...@gmail.com> wrote:
> On Thu, Apr 28, 2011 at 12:31 PM, david hc <davi...@gmail.com> 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+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php

No comments: