Hi eagle.
Once again, I think there is not enough information to help. From the
queries outputted, it looks like you do more in your controller method
than a single "find".
It looks like you find(all) Venues, not Contacts, and then find(all)
Contacts with the jobType condition, so that the Contacts associated
to the Venues you list aren't properly selected, because the jobType
condition is only defined later, in another 'find'...
Can you please give more detail, like the full controller action code,
including the way you set your variables for your view, and eventually
your Model definitions (associations).
Also, what is the result of changing "jobType"=>"main" to
"Contact.jobType"=>"main" ?
(I don't think your problem is here, but anyway it's still better practice...)
++++++++
Clément
On Sun, Aug 24, 2008 at 10:23 PM, eagle <[EMAIL PROTECTED]> wrote:
>
> 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
-~----------~----~----~----~------~----~------~--~---
