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
-~----------~----~----~----~------~----~------~--~---
