Ah yes, I did mean AppModel. Either that or my local copy of Cake is
based on CVM. I can't seem to get ContainableBehaviour to work either.
It keeps trying to query the database using "contain" instead of SQL.
Time to fall back to a custom join perhaps?
$joins = array(
array(
'table' => 'users',
'alias' => 'User',
'type' => 'INNER',
'conditions' => array('Member.user_id = User.id')
),
array(
'table' => 'counties_users',
'alias' => 'CountiesUsers',
'type' => 'INNER',
'conditions' => array('User.id = CountiesUsers.user_id')
),
array(
'table' => 'counties',
'alias' => 'County',
'type' => 'INNER',
'conditions' => array('CountiesUsers.county_id = County.id')
),
array(
'table' => 'regions',
'alias' => 'Regions',
'type' => 'INNER',
'conditions' => array('County.region_id = Region.id')
)
);
$conditions = array('Region.id' => $id);
$this->Member->find('all', compact('joins', 'conditions'));
It takes more time to code, but the step-by-step process is fairly
obvious and it only uses one query.
On Jul 8, 6:07 pm, Alastair <[email protected]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
