CakePHP

Fast alternative to "contains"?

Classic

List

Threaded

10 messages Options

Christian-2

Fast alternative to "contains"?

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
[hidden email] For more options, visit this group at http://groups.google.com/group/cake-php

lowpass

Re: Fast alternative to "contains"?

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 <[hidden email]> 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
> [hidden email] For more options, visit this group at
> http://groups.google.com/group/cake-php

Christian-2

Re: Fast alternative to "contains"?

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 <[hidden email]> 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
> [hidden email] For more options, visit this group at
> http://groups.google.com/group/cake-php

lowpass

Re: Fast alternative to "contains"?

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 <[hidden email]> 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 <[hidden email]>
>> 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
>> > [hidden email] 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
> [hidden email] For more options, visit this group at
> http://groups.google.com/group/cake-php

Christian-2

Re: Fast alternative to "contains"?

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 <[hidden email]> 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 <[hidden email]>
>> 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
>> > [hidden email] 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
> [hidden email] For more options, visit this group at
> http://groups.google.com/group/cake-php

Gremlin-2

Re: Fast alternative to "contains"?

Try this -

https://github.com/sams/linkable

It is a newer version of the old linkable behavior - uses relationships to create joins as well as allows you to create joins on non-default keys. This might be a fairly straightforward drop in replacement for containable in your case. It might not, but generating logical joins is a good start towards reducing the number of small queries usually generated by containable.
Good luck.

ProLoser

Re: Fast alternative to "contains"?

Actually you may want to hit up my fork https://github.com/ProLoser/linkable it has a very critical fix that's been missing for a while.

/end blatant namedropping

On Monday, July 16, 2012 4:11:31 PM UTC-7, gremlin wrote:

Try this -

https://github.com/sams/linkable

It is a newer version of the old linkable behavior - uses relationships to create joins as well as allows you to create joins on non-default keys. This might be a fairly straightforward drop in replacement for containable in your case. It might not, but generating logical joins is a good start towards reducing the number of small queries usually generated by containable.
Good luck.

Christian-2

Re: Fast alternative to "contains"?

In reply to this post by Gremlin-2

Also a good one to know for future, but only works for 'hasOne' or 'belongsTo'. Doesn't throw an error for 'hasMany' or 'HABTM', but will not fetch all related data sets (or did I just use it wrong?).

$this->Productgroup->find('first', array('link' => array('Category',
'Product' => array('Productoption' => array('Color',
'Size',
'Material'
),
'Productpresentation' => array('Productpresentationtype'
),

'Vendor'
),
'Fee',

),
'conditions' => array('Productgroup.id' => $id
)
)
);

Productgroup -> Product: HABTM

Productgroup -> Fee: HABTM

Productgroup -> Category: HABTM

Product -> Productoption: hasMany

Productoption -> Color/Size/Material: belongsTo

Cheers

Am Dienstag, 17. Juli 2012 01:11:31 UTC+2 schrieb gremlin:

Try this -

https://github.com/sams/linkable

It is a newer version of the old linkable behavior - uses relationships to create joins as well as allows you to create joins on non-default keys. This might be a fairly straightforward drop in replacement for containable in your case. It might not, but generating logical joins is a good start towards reducing the number of small queries usually generated by containable.
Good luck.

euromark (munich)

Re: Fast alternative to "contains"?

@ProLoser

your current version doesnt work for me anymore. seems like `$options['fields'] = str_replace($options['class'], $alias, $options['fields']);` is killing it:

"Column not found: 1054 Unknown column 'MainMainTag.name' in 'field list'"

removing this line and the tests run again.

are there any tests for it?

here is the version working for me

https://github.com/dereuromark/tools/blob/2.0/Model/Behavior/LinkableBehavior.php

Gremlin-2

Re: Fast alternative to "contains"?

In reply to this post by Christian-2

I believe the docs have this working alongside contains already - if you convert your habtm to hasMany through associations and use contains for the hasMany to generate the in( id, id2 ,id3 ) conditions then you should have it down to a single query or at most a small handful.

Thanks for pointing out that I grabbed the wrong repo - I was trying to find it for you in a hurry from a computer I don't use often.

On Tuesday, July 17, 2012 3:40:17 PM UTC-7, Christian wrote:

Also a good one to know for future, but only works for 'hasOne' or 'belongsTo'. Doesn't throw an error for 'hasMany' or 'HABTM', but will not fetch all related data sets (or did I just use it wrong?).

$this->Productgroup->find('first', array('link' => array('Category',
'Product' => array('Productoption' => array('Color',
'Size',
'Material'
),
'Productpresentation' => array('Productpresentationtype'
),

'Vendor'
),
'Fee',

),
'conditions' => array('Productgroup.id' => $id
)
)
);

Productgroup -> Product: HABTM
Productgroup -> Fee: HABTM
Productgroup -> Category: HABTM
Product -> Productoption: hasMany
Productoption -> Color/Size/Material: belongsTo

Cheers

Am Dienstag, 17. Juli 2012 01:11:31 UTC+2 schrieb gremlin:
Try this -

https://github.com/sams/linkable

It is a newer version of the old linkable behavior - uses relationships to create joins as well as allows you to create joins on non-default keys. This might be a fairly straightforward drop in replacement for containable in your case. It might not, but generating logical joins is a good start towards reducing the number of small queries usually generated by containable.
Good luck.