Hi
Hehe, now there's too much information :)
Your problem is here:
> function index() {
> $this->pageTitle = 'CTT::All venues';
> $this->set('venues',$this->paginate('Venue'));
> $venues = $this->Venue->Contact-
>>find('first',array('conditions'=>array('Contact.jobType'=>'main')));
> }
You actually paginate the Venues first, and fill the $venues view
variable with the result. Paginating generates all your queries but
the last one (it needs to count all venues, query for associated
models, etc).
Then you find() the 'first' Contact with jobType=main, but don't even
pass this variable to the view, so your last line of code, your
'find', your condition have no effect.
I don't know how good practice it is, but in this kind of situation, I
use to change temporarily the association conditions before
paginating, like this :
$this->Venue->hasMany['Contact']['conditions'] =
array('Contact.jobType'=>'main');
$this->paginate('Venue');
There may be more elegant solutions to do this, but ...
+++++++++
Clément
On Mon, Aug 25, 2008 at 12:09 AM, eagle <[EMAIL PROTECTED]> wrote:
>
> Hi Clemos,
> Thanks fro the reply ... more detail below ....
> I agree it does look like I am finding all venues, all contacts then
> all contacts with a condition but I do not believe I am - hence my
> confusion!
>
> The venues_controller.php:
>
> class VenuesController extends AppController {
>
> var $name = 'Venues';
> var $helpers = array('Html', 'Form');
> var $paginate = array(
> 'limit' => 10,
> 'page' => 1,
> 'order' => array(
> 'Venue.name' => 'ASC'
> )
> );
>
> function index() {
> $this->pageTitle = 'CTT::All venues';
> $this->set('venues',$this->paginate('Venue'));
> $venues = $this->Venue->Contact-
>>find('first',array('conditions'=>array('Contact.jobType'=>'main')));
> }
> }
>
> Venue MODEL:
> class Venue extends AppModel
> {
> var $name = 'Venue';
> var $hasMany = array(
> 'Contact' => array(
> 'className' => 'Contact',
> 'foreignKey' => 'venue_id',
> )
> );
> var $belongsTo = array(
> 'County' => array(
> 'className' => 'County',
> 'foreignKey' => 'county_id'
> ),
> 'VenueType' => array (
> 'className' => 'VenueType',
> 'foreignKey' => 'venueType_id'
> ),
> 'Flag' => array (
> 'className' => 'Flag',
> 'foreignKey' => 'flag_id'
> )
> );
> }
> ?>
> Contact MODEL
> class Contact extends AppModel
> {
> var $name = 'Contact';
> var $belongsTo = array(
> 'Venue' => array(
> 'className' => 'Venue',
> 'foreignKey' => 'venue_id'
> )
> );
> }
>
> Finally the VIEW:
> <!-- venues as a table - with principal contact information also
> displayed -->
> <table cellpadding="2" cellspacing = "2" border="0">
> <!-- headers -->
> <tr>
> <th><?php echo $paginator->sort('Flag', 'flag_id'); ?></th>
> <th><?php echo $paginator->sort('Venue Details', 'name'); ?> </th>
> <th><?php echo $paginator->sort('Contact','contact'); ?> </th>
> <th class="actions"><?php __('Actions');?></th>
> </tr>
> <?php
> $i = 0;
> foreach ($venues as $venue):
> $class = null;
> if ($i++ % 2 ==0) {
> $class = ' class="altrow"';
> }
> ?>
> <tr<?php echo $class; ?>>
> <td>
> <?php $flag = $venue['Flag']; echo $flag['name']; ?>
> </td>
> <td>
> <?php echo $html->link(__($venue['Venue']['name'],true),
> array('action'=>'view',$venue['Venue']['id']));?>
> </td>
> <?php debug($venue); ?>
> <?php foreach ($venue['Contact'] as $contact) : ?>
> <?php
> //if ($contact['jobType'] == 'main') {
> echo '<td>';
> $thisContact = $contact['fName'] . ' ' .
> $contact['lName'];
> echo $html->link($thisContact,
> 'mailto:'.$contact['email']);
> echo ' (' . $contact['position'] . ') T: ' .
> $contact['telephone'] . ' E: ' . $contact['email'];
> echo "</td>";
> //}
> ?>
> <?php endforeach; ?>
> </td>
> <td>
> <?php echo $html->link(__('Edit', true),
> array('action'=>'edit',
> $venue['Venue']['id'])); ?> |
> <?php echo $html->link(__('Delete', true),
> array('action'=>'delete',
> $venue['Venue']['id']), null, sprintf(__('Are you sure you want to
> delete # %s?', true), $venue['Venue']['name'])); ?>
>
> </td>
> </tr>
> <?php endforeach; ?>
>
> </table>
>
> <!-- paging elements -->
> <div class="paging">
> <?php echo $paginator->prev('<< '.__('previous', true), array(),
> null, array('class'=>'disabled'));?>
> | <?php echo $paginator->numbers();?>
> <?php echo $paginator->next(__('next', true).' >>', array(), null,
> array('class'=>'disabled'));?>
> </div>
>
> You should be able to view the data thus far at:
> http://ctt.homelinux.net/cttManager/venues
>
> Thanks again for your attention to this.
>
> Regards
>
> Eagle
>
> On Aug 24, 10:54 pm, clemos <[EMAIL PROTECTED]> wrote:
>> 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
-~----------~----~----~----~------~----~------~--~---
