Hi Robert,
Thanks for your help with this although it doesn't appear to be quite
working. It's certainly returning just members however it is returning
ALL members.
I'm assuming when you said AppController you meant AppModel for the
actsAs('Containable')?
The code I'm using is as follows:
App Model
class AppModel extends Model
{
var $actsAs = array('Containable');
var $recursive = -1;
}
Member Model
class Member extends AppModel
{
var $name = 'Member';
var $belongsTo = array(
'User' => array(
'className' => 'User',
'foreignKey' => 'user_id',
),
);
var $hasAndBelongsToMany = array('County');
}
County Model
class County extends AppModel
{
var $name = 'County';
var $belongsTo = array('Region');
var $hasAndBelongsToMany = array('Member');
}
Region Model
class Region extends AppModel
{
var $name = 'Region';
var $hasMany = array('County');
}
and my search action in the Member Controller
function search($id = null)
{
$this->Member->contain(array(
'County' => array(
'Region' => array(
'conditions' => array('Region.id' => $id)
)
)
));
$members = $this->Member->find('all');
$this->set('members', $members);
}
The SQL debug shows the following:
1 DESCRIBE `aros` 7 7 1
2 DESCRIBE `acos` 7 7 2
3 DESCRIBE `aros_acos` 7 7 1
4 DESCRIBE `members` 26 26 3
5 DESCRIBE `users` 7 7 1
6 DESCRIBE `groups` 4 4 2
7 DESCRIBE `pages` 10 10 3
8 DESCRIBE `posts` 8 8 1
9 DESCRIBE `counties` 3 3 2
10 DESCRIBE `regions` 2 2 1
11 DESCRIBE `counties_members` 3 3 1
12 SELECT `Member`.`id`, `Member`.`created`, `Member`.`modified`,
`Member`.`mem_no`, `Member`.`contact`, `Member`.`trade_name`,
`Member`.`address1`, `Member`.`address2`, `Member`.`address3`,
`Member`.`town`, `Member`.`county`, `Member`.`postcode`,
`Member`.`telephone`, `Member`.`mobiletelephone`, `Member`.`fax`,
`Member`.`email`, `Member`.`website`, `Member`.`apl_skills`,
`Member`.`apl_info`, `Member`.`trustmark`, `Member`.`summary`,
`Member`.`experience`, `Member`.`awards`, `Member`.`specialist_areas`,
`Member`.`published`, `Member`.`user_id` FROM `members` AS `Member`
WHERE 1 = 1 184 184 1
13 SELECT `County`.`id`, `County`.`name`, `County`.`region_id`,
`CountiesMember`.`id`, `CountiesMember`.`member_id`,
`CountiesMember`.`county_id` FROM `counties` AS `County` JOIN
`counties_members` AS `CountiesMember` ON
(`CountiesMember`.`member_id` IN (2030, 2031, 2032, 2033, 2034, 2035,
2036, 2037, 2038, 2039, 2040, 2041, 2042, 2043, 2044, 2045, 2046,
2047, 2048, 2049, 2050, 2051, 2052, 2053, 2054, 2055, 2056, 2057,
2058, 2059, 2060, 2061, 2062, 2063, 2064, 2065, 2066, 2067, 2068,
2069, 2070, 2071, 2072, 2073, 2074, 2075, 2076, 2077, 2078, 2079,
2080, 2081, 2082, 2083, 2084, 2085, 2086, 2087, 2088, 2089, 2090,
2091, 2092, 2093, 2094, 2095, 2096, 2097, 2098, 2099, 2100, 2101,
2102, 2103, 2104, 2105, 2106, 2107, 2108, 2109, 2110, 2111, 2112,
2113, 2114, 2115, 2116, 2117, 2118, 2119, 2120, 2121, 2122, 2123,
2124, 2125, 2126, 2127, 2128, 2129, 2130, 2131, 2132, 2133, 2134,
2135, 2136, 2137, 2138, 2139, 2140, 2141, 2142, 2143, 2144, 2145,
2146, 2147, 2148, 2149, 2150, 2151, 2152, 2153, 2154, 2155, 2156,
2157, 2158, 2159, 2160, 2161, 2162, 2163, 2164, 2165, 2166, 2167,
2168, 2169, 2170, 2171, 2172, 2173, 2174, 2175, 2176, 2177, 2178,
2179, 2180, 2181, 2182, 2183, 2184, 2185, 2186, 2187, 2188, 2189,
2190, 2191, 2192, 2193, 2194, 2195, 2196, 2197, 2198, 2199, 2200,
2201, 2202, 2203, 2204, 2205, 2206, 2207, 2208, 2209, 2210, 2211,
2212, 2213) AND `CountiesMember`.`county_id` = `County`.`id`) WHERE 1
= 1 536 536 14
14 SELECT `Region`.`id`, `Region`.`name` FROM `regions` AS `Region`
WHERE `Region`.`id` = 1 1 1 0
15 SELECT `Region`.`id`, `Region`.`name` FROM `regions` AS `Region`
WHERE `Region`.`id` = 1 1 1 0
.
.
200 SELECT `Region`.`id`, `Region`.`name` FROM `regions` AS `Region`
WHERE `Region`.`id` = 1 1 1 0
Lot of code but I thought I'd show you the whole lot :)
Any ideas what might be up?
Thanks again for all your help,
Alastair
On Jul 8, 6:02 am, Robert P <[email protected]> wrote:
> That sould have been:
>
> $this->Member->contain(array(
> 'County' => array(
> 'Region' => array(
> 'conditions' => array('Region.id' => $id)
> )
> )
> ));
> $members = $this->Member->find('all');
>
> On Jul 8, 12:58 pm, Robert P <[email protected]> wrote:
>
>
>
> > Matt Curry does a good job of providing a default model setup for any
> > new application. If AppController doesn't already exist, create /app/
> > app_controller.php and add the following class, or copy the attributes
> > into your current one:
>
> > class AppController extends Controller {
> > var $actsAs = array('Containable');
> > var $recursive = -1;
> > }
>
> > Doing so shouldn't require you to rework any of your code, but it will
> > reduce database overhead when dealing with hasMany and HABTM
> > relationships (provided you use the syntax of course). Then change
> > your find call to use Containable:
>
> > $this->Member->contain(
> > 'County' => array(
> > 'Region' => array(
> > 'conditions' => array('Region.id' => $id)
> > )
> > )
> > );
> > $members = $this->Member->find('all');
>
> > I'm flying blind here, but hopefully it works. You can also pass the
> > contain settings to Model::find() each time, though it's too heavy for
> > complex joins:
>
> > $members = $this->Member->find('all', array('contain' => array(
> > 'County' => array(
> > 'Region' => array(
> > 'conditions' => array('Region.id' => $id)
> > )
> > )
> > )));
>
> >http://book.cakephp.org/view/474/Containable
>
> > On Jul 8, 10:17 am, "Dave Maharaj :: WidePixels.com"
>
> > <[email protected]> wrote:
> > > Use "contain"
>
> > > There is a chapter in the cookbook how to get only the records from
> > > related
> > > models and not pull all the un-necessary info.
>
> > > Dave
>
> > > -----Original Message-----
> > > From: Alastair [mailto:[email protected]]
> > > Sent: July-07-09 10:31 PM
> > > To: CakePHP
> > > Subject: More HABTM questions - querying data
>
> > > Robert P kindly solved a problem I was having with updating records in a
> > > HABTM relationship so many thanks to him for that!
>
> > > I'm now trying to query the data but have added an additional model to my
> > > system, Regions. So it looks something like:
>
> > > Members HasAndBelongsToMany County
> > > County BelongsTo Region (and Region hasMany County)
>
> > > I'm trying to return members that belong to Region.id = 1 and am using
> > > this
> > > to do so:
>
> > > $members = $this->Member->County->Region->find('all', array ('conditions'
> > > =>
> > > array('Region.id' => $id), 'recursive' => 2));
>
> > > This does return all members belonging to Region 1 but it also returns a
> > > whole lot of other information with it. Is there any way I can construct a
> > > query that literally just returns the corresponding members?
>
> > > The above returns the following data:http://bin.cakephp.org/saved/48057
>
> > > which includes County and Region data and in this instance, I don't need
> > > it.
> > > I'll live with it if I need to but I'd rather I didn't have to! Removing
> > > 'recursive' => 2 results in just the Region and County data being
> > > returned.
>
> > > Many thanks,
>
> > > Alastair
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
