Where there is only 1 level in the 'contain':
SELECT `Photographer`.`id`, `Photographer`.`first_name`,
`Photographer`.`last_name`, `Photographer`.`company_name`,
`Photographer`.`email`, `Photographer`.`telephone`,
`Photographer`.`address_1`, `Photographer`.`address_2`,
`Photographer`.`country_id`, `Photographer`.`state_id`,
`Photographer`.`city`, `Photographer`.`zip_code`, [[[... plus a bunch
of other fields ... ]]] `Country`.`id`, `Country`.`name`,
`Country`.`created`, `Country`.`created_by`, `Country`.`modified`,
`Country`.`modified_by`, `State`.`id`, `State`.`name`,
`State`.`country_id`, `State`.`created`, `State`.`created_by`,
`State`.`modified`, `State`.`modified_by` FROM `photographers` AS
`Photographer` LEFT JOIN `countries` AS `Country` ON
(`Photographer`.`country_id` = `Country`.`id`) LEFT JOIN `states` AS
`State` ON (`Photographer`.`state_id` = `State`.`id`) WHERE
`Photographer`.`id` = 1 LIMIT 1
SELECT `PrintSize`.`id`, `PrintSize`.`name`, `PrintSize`.`created`,
`PrintSize`.`created_by`, `PrintSize`.`modified`,
`PrintSize`.`modified_by`, `PhotographersPrintSize`.`id`,
`PhotographersPrintSize`.`photographer_id`,
`PhotographersPrintSize`.`print_size_id`,
`PhotographersPrintSize`.`price`, `PhotographersPrintSize`.`comment`,
`PhotographersPrintSize`.`created`,
`PhotographersPrintSize`.`created_by`,
`PhotographersPrintSize`.`modified`,
`PhotographersPrintSize`.`modified_by` FROM `print_sizes` AS
`PrintSize` JOIN `photographers_print_sizes` AS
`PhotographersPrintSize` ON
(`PhotographersPrintSize`.`photographer_id` = 1 AND
`PhotographersPrintSize`.`print_size_id` = `PrintSize`.`id`) WHERE 1 =
1
Then as soon as I add in the extra level in 'contain' I get the above,
plus these extras:
SELECT `Country`.`id`, `Country`.`name`, `Country`.`created`,
`Country`.`created_by`, `Country`.`modified`, `Country`.`modified_by`
FROM `countries` AS `Country` WHERE `Country`.`id` = 2
SELECT `State`.`id`, `State`.`name`, `State`.`country_id`,
`State`.`created`, `State`.`created_by`, `State`.`modified`,
`State`.`modified_by` FROM `states` AS `State` WHERE `State`.`id` = 2
I'm hoping to have some time tonight to really dig through the
Containable behaviour code.
On Nov 6, 3:03 pm, teknoid <[EMAIL PROTECTED]> wrote:
> What are the "extra" queries, which get generated for Country and
> State, when you add other models?
> Could you provide an example?
>
> On Nov 6, 5:41 pm, Brenton B <[EMAIL PROTECTED]> wrote:
>
> > Photographer:
> > Photographer belongsTo Country
> > Photographer belongsTo State
> > Photographer HABTM PrintSize: with PhotographersPrintSize (join
> > table
> > has extra data)
>
> > PrintSize:
> > PrintSize HABTM Photographer: with PhotographersPrintSize (again,
> > join table with data)
>
> > PhotographersPrintSize:
> > PhotographersPrintSize hasMany Photographer
> > PhotographersPrintSize hasMany PrintSize
>
> > yes, all in same db.
>
> > Also, forgot to mention, if I take off to recursive level (as below),
> > it does it fine without all the extra queries.
>
> > $this->set('photographer',
> > $this->Photographer->find('first',
> > array('contain' =>
> > array(
> > 'Country',
> > 'State',
> >
> > 'PrintSize'))));
>
> > On Nov 6, 2:18 pm, teknoid <[EMAIL PROTECTED]> wrote:
>
> > > what's the relation between all those models?
> > > and are they all in the same db?
>
> > > On Nov 6, 5:12 pm, Brenton B <[EMAIL PROTECTED]> wrote:
>
> > > > Aha!!!
> > > > I think I've come up with the reason ...
>
> > > > As I was trying to produce a nice a short example I realized that when
> > > > there's only 1 level it doesn't produce the extra queries ... as soon
> > > > as there's more than 1 level it has the extra queries.
>
> > > > So if I have:
>
> > > > $this->set('photographer',
> > > > $this->Photographer->find('first',
> > > >
> > > > array('contain' => array(
> > > >
> > > > 'Country',
> > > >
> > > > 'State'))));
>
> > > > Then there's no problem, but if I have
>
> > > > $this->set('photographer',
> > > > $this->Photographer->find('first',
> > > >
> > > > array('contain' => array(
> > > >
> > > > 'Country',
> > > >
> > > > 'State',
> > > >
> > > > 'PrintSize.PhotographersPrintSize'))));
>
> > > > Then it'll do extra queries on Country and State, in addition to
> > > > PrintSize and PhotographersPrintSize.
>
> > > > Even if I change it to
>
> > > > $this->set('photographer',
> > > > $this->Photographer->find('first',
> > > >
> > > > array('contain' => array(
> > > >
> > > > 'Country',
> > > >
> > > > 'State',
> > > >
> > > > 'PrintSize' => array('PhotographersPrintSize')))));
>
> > > > I still run into the issue.
>
> > > > BTW:
>
> > > > class AppModel extends Model {
>
> > > > var $actsAs = array('Containable');
>
> > > > }
>
> > > > I suspect it's something to do with recursive, but will keep digging.
>
> > > > On Nov 6, 8:33 am, teknoid <[EMAIL PROTECTED]> wrote:
>
> > > > > Can you show your use of containable?
> > > > > I think something might be amiss...
>
> > > > > On Nov 5, 6:21 pm, Brenton B <[EMAIL PROTECTED]> wrote:
>
> > > > > > After a bit of digging setting the 'recursive' property in the model
> > > > > > _partially_ solves this:
> > > > > > var $actsAs = array('Containable' => array('recursive' => false));
>
> > > > > > However, obviously won't help where some items need a 2nd or 3rd
> > > > > > level.
>
> > > > > > So I guess it comes back to: is there any way to be model-specific
> > > > > > for
> > > > > > recursion?
>
> > > > > > On Nov 5, 2:54 pm, Brenton B <[EMAIL PROTECTED]> wrote:
>
> > > > > > > That's the problem with 'contain' though ... even if I do use a
> > > > > > > hasOne
> > > > > > > bind, 'contain' will still do extra queries.
> > > > > > > In the example I mentioned, it will retrieve everything properly
> > > > > > > in 1
> > > > > > > big query, but then it'll go back again and find the models
> > > > > > > listed in
> > > > > > > 'contain'.
> > > > > > > On a default page of 20 records (since I'm doing pagination),
> > > > > > > where
> > > > > > > each record has 4 extra models to find you're looking at 80 extra
> > > > > > > unnecessary db hits ... multiply that by X users view the page and
> > > > > > > you're looking at a lot of unnecessary queries. Sure it's just
> > > > > > > milliseconds, but still quite concerning.
>
> > > > > > > On Nov 5, 2:48 pm, teknoid <[EMAIL PROTECTED]> wrote:
>
> > > > > > > > JOINS are only built for hasOne and belongsTo relationships.
> > > > > > > > A bunch of little selects will not harm your DB (how many
> > > > > > > > milliseconds
> > > > > > > > do those queries take?)
>
> > > > > > > > Are you displaying some list on the page? Maybe paginate is in
> > > > > > > > order
> > > > > > > > for this case?
>
> > > > > > > > All that being said, if you are not happy with that and prefer
> > > > > > > > a long
> > > > > > > > JOIN query, you can always "trick" cake into building one, by
> > > > > > > > using a
> > > > > > > > temporary hasOne bind.
>
> > > > > > > > On Nov 5, 5:08 pm, Brenton B <[EMAIL PROTECTED]> wrote:
>
> > > > > > > > > What might also help is a way to include recursion on only
> > > > > > > > > certain
> > > > > > > > > associations - which can be done with 'contain', but again,
> > > > > > > > > 'contain'
> > > > > > > > > is doing more db queries than it needs to.
>
> > > > > > > > > On Nov 5, 11:38 am, Brenton B <[EMAIL PROTECTED]> wrote:
>
> > > > > > > > > > This is getting quite confusing/frustrating ...
>
> > > > > > > > > > If I use 'contain' it's the equivalent of using 'unbind'
> > > > > > > > > > (on the fly)
> > > > > > > > > > on everything not listed in 'contain' (as described in the
> > > > > > > > > > Cookbook);
> > > > > > > > > > however, if I use 'contain', it seems to actually execute
> > > > > > > > > > an extra
> > > > > > > > > > query to get that corresponding information, regardless of
> > > > > > > > > > association
> > > > > > > > > > type.
>
> > > > > > > > > > For example, list of members with countries (belongsTo),
> > > > > > > > > > provinces
> > > > > > > > > > (belongsTo), plus a whole bunch of other stuff: webpages,
> > > > > > > > > > interests,
> > > > > > > > > > etc.
>
> > > > > > > > > > If I'm doing a listing, I just want name, country, and
> > > > > > > > > > province, so I
> > > > > > > > > > can either do unbind on everything I don't need, or use
> > > > > > > > > > 'contain' on
> > > > > > > > > > the things I do need .... 'contain' is the logical choice.
> > > > > > > > > > The resulting query does a LEFT JOIN on countries and
> > > > > > > > > > provinces just
> > > > > > > > > > fine, but it ALSO does an extra query for every single
> > > > > > > > > > country_id and
> > > > > > > > > > province_id to get the name. So on a big long list I'm
> > > > > > > > > > getting quite a
> > > > > > > > > > few extra database hits.
>
> > > > > > > > > > Is this supposed to do this? Because it goes against the
> > > > > > > > > > Cookbook's
> > > > > > > > > > comment of "Using Containable will help you cut down on
> > > > > > > > > > needless wear
> > > > > > > > > > and tear on your database".
>
> > > > > > > > > > (This gets slightly worse once I go a level deeper.)
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
