Thursday, March 31, 2011

Re: Contain and SQL Statement Question

If you are returning this data to display on a view you wouldn't want to show this much data, so you'd use pagination which cuts down the number of rows returned. This won't apply if you are getting the data for some sort of processing, but it was worth a mention.

Jeremy Burns
Class Outfit

jeremyburns@classoutfit.com
http://www.classoutfit.com

On 31 Mar 2011, at 13:12, heohni wrote:

> What do you mean?
>
> On 31 Mrz., 14:03, Jeremy Burns | Class Outfit
> <jeremybu...@classoutfit.com> wrote:
>> Pagination?
>>
>> Jeremy Burns
>> Class Outfit
>>
>> jeremybu...@classoutfit.comhttp://www.classoutfit.com
>>
>> On 31 Mar 2011, at 13:01, heohni wrote:
>>
>>
>>
>>> If I use the contain method I am getting an array like:
>>> Array
>>> (
>>> [0] => Array
>>> (
>>> [Member] => Array
>>> (
>>> [id] => 5
>>> )
>>
>>> [Payment] => Array
>>> (
>>> [0] => Array
>>> (
>>> [id] => 3270
>>> [member_id] => 5
>>> )
>>
>>> [1] => Array
>>> (
>>> [id] => 3271
>>> [member_id] => 5
>>> )
>>
>>> [2] => Array
>>> (
>>> [id] => 3272
>>> [member_id] => 5
>>> )
>>
>>> )
>>
>>> )
>>
>>> Which is really perfect for me.
>>> I am just worried about the performance later when the number of
>>> member is raising.
>>
>>> If I do a LEFT JOIN, I get "nicer" statement, but "loosing" the sorted
>>> result, as the array will look like:
>>
>>> Array
>>> (
>>> [0] => Array
>>> (
>>> [Member] => Array
>>> (
>>> [id] => 5
>>> )
>>
>>> [Payment] => Array
>>> (
>>> [id] => 3270 // first payment
>>> )
>>
>>> )
>>> [1] => Array
>>> (
>>> [Member] => Array
>>> (
>>> [id] => 5
>>> )
>>
>>> [Payment] => Array
>>> (
>>> [id] => 3271 // second payment
>>> )
>>
>>> )
>>> etc etc etc...
>>
>>> It can't be the first time, someone need to select that kind of model
>>> assoc?
>>
>>> On 31 Mrz., 13:26, Eugenio <eugenioc...@gmail.com> wrote:
>>>> If you have that kind of need you are having database architecture
>>>> issues... its not a cakephp issue.
>>
>>>> Ask yourself how you do that query in plain SQL.
>>
>>>> Cheers
>>
>>>> 2011/3/31 heohni <heidi.anselstet...@consultingteam.de>:
>>
>>>>> Hi,
>>
>>>>> I have the following models:
>>
>>>>> Member hasMany Payments
>>>>> Payment belongs to a member
>>
>>>>> To get now all members and their payments I do the following
>>>>> statement:
>>
>>>>> $this->paginate = array(
>>>>> 'fields' => array(.....),
>>>>> 'conditions' => array(...),
>>>>> 'contain' => array(
>>>>> 'Payment' => array(
>>>>> 'fields' => array(...),
>>>>> 'conditions' => array(...)
>>>>> )
>>>>> )
>>>>> );
>>
>>>>> So far, the result is correct.
>>
>>>>> BUT!
>>>>> The second statement, where all payment are getting selected, that
>>>>> statement looks like:
>>>>> WHERE `Payment`.`member_id` IN (5, 10, 11)
>>
>>>>> This is for 3 members fine, but what will happen, if I have 10k
>>>>> members?
>>
>>>>> Do I have the chance to get this whole statement done in 1 single
>>>>> statement? To make sure I will not fail in perfomance in the future?
>>
>>>>> Please advice!
>>>>> Thanks!!
>>
>>>>> --
>>>>> Our newest site for the community: CakePHP Video Tutorialshttp://tv.cakephp.org
>>>>> Check out the new CakePHP Questions sitehttp://ask.cakephp.organdhelp others with their CakePHP related questions.
>>
>>>>> To unsubscribe from this group, send email to
>>>>> cake-php+unsubscribe@googlegroups.com For more options, visit this group athttp://groups.google.com/group/cake-php
>>
>>> --
>>> Our newest site for the community: CakePHP Video Tutorialshttp://tv.cakephp.org
>>> Check out the new CakePHP Questions sitehttp://ask.cakephp.organd help others with their CakePHP related questions.
>>
>>> To unsubscribe from this group, send email to
>>> cake-php+unsubscribe@googlegroups.com For more options, visit this group athttp://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
> cake-php+unsubscribe@googlegroups.com 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
cake-php+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php

No comments: