This might require a bit of tweaking, since I've never used the EXISTS
operator with cakephp... but here it goes:
*$categoriesForTree = $Category->find('all', *
* **array('order' => 'lft ASC',*
* **'conditions' => 'EXISTS (SELECT id FROM posts as Post WHERE Category.id
= Post.category_id AND Post.status > 0 AND Post.level <=
'.$loggedUser['Group']['level'].' )'));*The idea is to fetch all categories that where there exists a post that has a status >0 and a level <= to the logged user 2009/7/6 Yura Linnyk <[email protected]> > > Hi all! I'll ask a question first and then I'll give a background to > it for just in case that'd give any insight. > > Is it better to query MySQL table once for all posts (which may be > thousands of rows) or about 30 - 50 queries with limit 1? > > The reason I ask is that I build a menu of categories and I don't want > to show categories that have no posts that are published and the > logged user is allowed to see. Here's the code I use to get only those > categories that I want: > > $Category = ClassRegistry::init('Category'); > $categoriesForTree = $Category->find('all', array('order' => 'lft > ASC', > 'contain' => array( > 'Post' => array( > 'fields' => > array('id','level','status'), > 'conditions' > => array('Post.status >' => 0, > > 'Post.level <=' => > $loggedUser['Group']['level']), > //'limit' => > 1, // -- this doesn't work and just returns one > post of all categories > ), > ), > )); > > How 'contain' works is it queries for all categories first, and then > runs a second query to get all posts from all the categories, and that > feels like an overkill to me, because I don't need all posts, I just > need to know if there's at least one post under given criteria in > every category or not. > if I set "'limit' => 1", as commented out in the code above, it will > not return one post for every category (something I would expect it > did), but limits the query so only one lucky category happens to get > one post as a child (which doesn't make sense to me, as far as > Containable behavior design is concerned). > > Anyway, I end up having two options: either leave it with the above > code or modify it and iteratively check every category with minimum of > returned data. > > Thanks for any insight on this. > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
