Refactored to remove unnecessary table.
$joins = array(
array(
'table' => 'counties_users',
'alias' => 'CountiesUsers',
'type' => 'INNER',
'conditions' => array('Member.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')
)
);
On Jul 8, 7:35 pm, Robert P <[email protected]> wrote:
> 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
>
> ...
>
> read more »
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
