Hi Cricket, thanks again for the extensive explanation. I gave it a try and finderQueries actually work. However, the result is again "flat" data (except for the first recursion level), which looks basically exactly like my SQL query (which I used before). Hence I think a SQL query in the model might even be the better solution for my case, since the finderQuery changes the model behaviour permanently (which is not desired for other queries).
Did some more research on the web on this, however, I think to get the required result there is only 2 ways: - either use a SQL query (or finder query) directly in your source code, which comes at the price of loosing the nested result arrays. - do a lot of unbind and bind calls to get the model bindings as required. Cakephp will still split up the recursice find (or read) in tons of queries, but it's far better than the "containable" behaviour. On the bottom line I think cakephp has a real problem when it comes to object relational mapping with a bit of data (my 750 lines is not a lot of data yet, maybe a few Kb). Just wondering how this looks in version 2.0. Thanks anyway, Chris Am Donnerstag, 12. Juli 2012 01:49:25 UTC+2 schrieb cricket: > > > http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#hasmany > > > For some associations, including hasMany, you can provide an SQL query > that Cake will use to fetch the associated data. When Cake fetches the > main model's data from a find() call, it then checks which > associations need to be included, based on the recursive setting. It > then runs through the data and grabs the appropriate associated data > given the main model's primary key. If there's a finderQuery, Cake > uses that instead of its conventional approach. This can be especially > helpful in situations where you're stuck with a legacy database. > > With contain, it's different. While it can be very handy it comes at > the price of (usually) many more separate queries. > > Here's an example from an app I'm working on now. The main application > doesn't use a framework and needs to be rewritten in a big way. But in > the meantime the client needs some data extraction tools. So I've > started doing that with Cake with an eye to eventually normalising the > database to Cake's conventions. Until then, I need to use > finderQueries for everything. > > 'Register' => array( > 'className' => 'Register', > 'foreignKey' => false, > 'finderQuery' => 'SELECT Register.id AS `id`, Trans.titre_en AS > title_en, Trans.titre_fr AS title_fr > FROM registre_langue AS Register > LEFT JOIN item_bilingue AS Trans ON Trans.id = > Register.item_bilingue_id > WHERE Register.id = > (SELECT e.registre_langue_id FROM expression AS e WHERE > e.id = > {$__cakeID__$})' > ) > > As you can see there's a special variable made available, > {$__cakeID__$}, which represents the current record's PK. > Unfortunately, there's no way to get other fields from the row, hence > the need for a sub-query. > > Note the Register & Trans aliases. By using aliases the same way Cake > does you'll get an array back in the normal way. > > In your case, what you could do is create a Category hasMany > ProductGroup association. Then come up with an SQL query which allows > you to fetch all of the data you need in one go using joins. Just > remember to use the aliases. > > The only problems I see, though, is that your query depends on a > country_id. If you can't figure out how to do this with just a single > SELECT call then finderQuery may not be the solution. > > Good luck. > > > On Wed, Jul 11, 2012 at 6:46 PM, Christian <[email protected]> > wrote: > > Hi, there is no way around having all data in this one page and it would > be > > just very convinient to get it as formatted "cake-array" (and it's > actually > > not too much data as the flat view proofs, only the separation into that > > many queries makes it that slow). What do you mean exactly by a > finderQuery? > > > > Thanks, > > Christian > > > > > > On Tuesday, July 10, 2012 10:31:44 PM UTC+2, cricket wrote: > >> > >> I guess the first question is, do you really need to fetch all 750 > >> rows at once? Second, do you need to have all of that associated data > >> (Color, Icon, etc.)? I can't think of why you would unless you're > >> creating a report (ie. CSV file) or generating a *really* long HTML > >> page. > >> > >> If yo really must grab all that data with your Categories, you could > >> try using a finderQuery in the Category model for the $hasMany > >> association. > >> > >> On Tue, Jul 10, 2012 at 3:03 PM, Christian <[email protected]> > >> wrote: > >> > Hi there, > >> > > >> > I have the following find query: > >> > > >> > > >> > > >> >> $categoryItems_all = $this->Category->find('all', array('contain' => > >> >> array('Productgroup' => array('Fee' => array('conditions' => > >> >> array('Fee.country_id' => $this->__getCountryId() > >> >> > >> >> ) > >> >> > >> >> ), > >> >> > >> >> 'Product' => array('Productoption' => array('Color', > >> >> > >> >> 'Material', > >> >> > >> >> 'Size', > >> >> > >> >> 'Icon', > >> >> > >> >> ), > >> >> > >> >> 'Vendor' > >> >> > >> >> ) > >> >> > >> >> ) > >> >> > >> >> ), > >> >> 'conditions' => > >> >> array('Category.parent_id' => $id, > >> >> > >> >> 'Category.active' => '1', > >> >> > >> >> 'Category.display' => '1' > >> >> > >> >> ) > >> >> ) > >> >> ); > >> > > >> > > >> > If I pack the same query in a flat SQL view, it has 750 rows (and > takes > >> > same > >> > milii seconds). My Problem with the containable in find query is, > that > >> > cake > >> > splits it up into ~10000!! queries which takes approximately 10 > seconds > >> > and > >> > the database is not yet half filled with all items. > >> > > >> > Is there any alternative to use the containable behaviour which > returns > >> > a > >> > similar result but with a better performance? As a workaround I'm > >> > currently > >> > using the view, but flat data was not was I was looking for. The > "joins" > >> > options doesn't work either, since the result just contains the data > of > >> > the > >> > model the find query was running on. > >> > > >> > Thanks, > >> > Christian > >> > > >> > -- > >> > 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 > >> > [email protected] For more options, visit this > group > >> > at > >> > http://groups.google.com/group/cake-php > > > > -- > > 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 > > [email protected] For more options, visit this > group at > > http://groups.google.com/group/cake-php > -- 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 [email protected] For more options, visit this group at http://groups.google.com/group/cake-php
