Carlos, thank you for two insightful comments. I wasn't aware of EXISTS subquery and will try it, then your second comment pretty much answers my question. Thanks!
On Jul 6, 7:31 pm, Carlos Gonzalez Lavin <[email protected]> wrote: > As an extra.. as far as I know the best practice is to do the smallest > amount of querys you can to the database (even if the resulting dataset is a > bit bigger than needed).. since a big part of the overhead processing comes > from preparing those data to be returned and having the application receive > them > > 2009/7/6 Carlos Gonzalez Lavin <[email protected]> > > > 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 -~----------~----~----~----~------~----~------~--~---
