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 -~----------~----~----~----~------~----~------~--~---
