Thanks for the useful advice Clement.
The queries that are run are as follows:
DESCRIBE `venues` 15 15 3
DESCRIBE `counties` 2 2 2
DESCRIBE `venue_types` 2 2 2
DESCRIBE `flags` 2 2 2
DESCRIBE `contacts` 11 11 3
SELECT COUNT(*) AS `count` FROM `venues` AS `Venue` LEFT JOIN
`counties` AS `County` ON (`Venue`.`county_id` = `County`.`id`) LEFT
JOIN `venue_types` AS `VenueType` ON (`Venue`.`venueType_id` =
`VenueType`.`id`) LEFT JOIN `flags` AS `Flag` ON (`Venue`.`flag_id` =
`Flag`.`id`) WHERE 1 = 1
SELECT `Venue`.`id`, `Venue`.`name`, `Venue`.`add1`, `Venue`.`add2`,
`Venue`.`town`, `Venue`.`county_id`, `Venue`.`postcode`,
`Venue`.`telephone`, `Venue`.`boxOffice`, `Venue`.`fax`,
`Venue`.`website`, `Venue`.`publicity`, `Venue`.`venueNotes`,
`Venue`.`flag_id`, `Venue`.`venueType_id`, `County`.`id`,
`County`.`name`, `VenueType`.`id`, `VenueType`.`name`, `Flag`.`id`,
`Flag`.`name` FROM `venues` AS `Venue` LEFT JOIN `counties` AS
`County` ON (`Venue`.`county_id` = `County`.`id`) LEFT JOIN
`venue_types` AS `VenueType` ON (`Venue`.`venueType_id` =
`VenueType`.`id`) LEFT JOIN `flags` AS `Flag` ON (`Venue`.`flag_id` =
`Flag`.`id`) WHERE 1 = 1 ORDER BY `Venue`.`name` ASC LIMIT 10
SELECT `Contact`.`venue_id`, `Contact`.`id`, `Contact`.`title`,
`Contact`.`fName`, `Contact`.`lName`, `Contact`.`telephone`,
`Contact`.`mobile`, `Contact`.`email`, `Contact`.`contactNotes`,
`Contact`.`jobType`, `Contact`.`position` FROM `contacts` AS `Contact`
WHERE `Contact`.`venue_id` IN (7, 8, 9, 10, 11, 12, 13, 14, 15, 16)
SELECT `Contact`.`venue_id`, `Contact`.`id`, `Contact`.`title`,
`Contact`.`fName`, `Contact`.`lName`, `Contact`.`telephone`,
`Contact`.`mobile`, `Contact`.`email`, `Contact`.`contactNotes`,
`Contact`.`jobType`, `Contact`.`position`, `Venue`.`id`,
`Venue`.`name`, `Venue`.`add1`, `Venue`.`add2`, `Venue`.`town`,
`Venue`.`county_id`, `Venue`.`postcode`, `Venue`.`telephone`,
`Venue`.`boxOffice`, `Venue`.`fax`, `Venue`.`website`,
`Venue`.`publicity`, `Venue`.`venueNotes`, `Venue`.`flag_id`,
`Venue`.`venueType_id` FROM `contacts` AS `Contact` LEFT JOIN `venues`
AS `Venue` ON (`Contact`.`venue_id` = `Venue`.`id`) WHERE `jobType` =
'main' LIMIT 1
So I get the venues ... fine. Then CakePHP runs two SELECT queries
from the contacts table. The second is the one I want, not the
first. This, as far as I can see, is being produced from:
$this->Venue->Contact-
>find('all',array('conditions'=>array('jobType'=>'main')));
The output from <?php debug($venue); ?> is:
Array
(
[Venue] => Array
(
[id] => 7
[name] => Abbey Green
[add1] => Close Place
[add2] =>
[town] => Anytown
[county_id] => 7
[postcode] => I
[telephone] => 01222 222333
[boxOffice] =>
[fax] => 01222 222334
[website] =>
[publicity] => 0
[venueNotes] =>
[flag_id] => 2
[venueType_id] => 1
)
[County] => Array
(
[id] => 7
[name] => Suffolk
)
[VenueType] => Array
(
[id] => 1
[name] => National Trust
)
[Flag] => Array
(
[id] => 2
[name] => Maybe
)
[Contact] => Array
(
[0] => Array
(
[venue_id] => 7
[id] => 5
[title] => Ms
[fName] => Sally
[lName] => Jones
[telephone] => 01223 111222
[mobile] => 07897 123123
[email] => [EMAIL PROTECTED]
[contactNotes] =>
[jobType] => finance
[position] => Finance Manager
)
[1] => Array
(
[venue_id] => 7
[id] => 4
[title] => Mr
[fName] => Fred
[lName] => Smith
[telephone] => 01223 123123
[mobile] => 07678 123123
[email] => [EMAIL PROTECTED]
[contactNotes] =>
[jobType] => main
[position] => Marketing Manager
)
)
)
Pertinent here are the two contacts being fetched ... I want only the
one where the jobType = 'main'. I reckon the first SELECT query is
being used, not teh filtered one.....
I hope this provides some more detail ... I was concerned about
sending too long a message.
I rellay appreciate the help on here.
Thanks
Eagle
On Aug 24, 2:57 pm, clemos <[EMAIL PROTECTED]> wrote:
> Hi
>
> Why exactly do you want to "switch off the first query" ? Maybe you
> could provide us with these queries you get, so that we can tell if
> they really need to be "switched off" or not...
> What you say about "two queries" with one retrieving all the data, and
> the other "filtering" the previous ones result doesn't make sense at
> all, to me. That's definitely not how "find" works, I've never seen it
> act that way. And anyway, even from a SQL point of view, it doesn't
> make sense (how can a query "filter" the results of a previous query
> ?).
> Be more precise when you expose your problem, because we can't reply
> something meaningful with so few informations...
>
> Also, is "jobType" a field of the Contact model ? if so (just trying
> guess), you should write your condition like this :
> "conditions"=>array("Contact.jobType"=>"main")
> It should do what you're trying to do...
>
> Rather than asking two question at the same time, whereas they're not
> really related to each other, you should split your problem in two
> like : (1) try to get the data you want from the Model::find method,
> and once you get how "find" works, (2) see if there actually is still
> too many queries being done to the database, and provide us with more
> debug data so we can have a small idea of why this happens...
>
> +++++++
> clément
>
> On Sun, Aug 24, 2008 at 2:13 AM, eagle <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> > I'm still struggling with a problem - which should be easy (I think)
> > but .... :-( Any advice you more experienced folks could give ....?
>
> > I want to achieve something akin to the following (building on the
> > example in the manual):
>
> > $this->Recipe->Ingredient->find('all');
>
> > my code is
>
> > $this->Venue->Contact->find('all',
> > array('conditions'=>array('jobType'=>'main')));
>
> > I am expecting this to retrieve data for venues and contacts where
> > their jobtype is stated as 'main', but it fetches all venues and all
> > contacts irrespective of their 'jobType'. I have tried replacing
> > 'all' with 'first' but the same behaviour.
>
> > The SQL debug info shows the Venue query running then TWO Contact
> > queries - the first getting everything, the second filtering as I
> > want .... how can I switch off the first CONTACT query ...?
>
> > How can I get only the 'main' contacts for each venue and then display
> > in a view?
>
> > My latest fudge is to get them all and the filter in the view with:
> > if ($contact['jobType'] == 'main') { ... }
> > but I reckon I'm missing something much cleaner than this.
>
> > Thanks for your help
>
> > Eagle
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
