Please let us know if it worked (and if you had to change any part of the code).. I think some people (myself included) would be interested in knowing if using EXISTS is as simple as that 2009/7/6 Yura Linnyk <[email protected]>
> > 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 -~----------~----~----~----~------~----~------~--~---
