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
