I just realised that since I need to get SQL that looks like:
SELECT * FROM vehicles as Vehicle
-- This is all the standard associations in the model:
LEFT JOIN businesses as Business ON
Vehicle.business_id = Business.id
LEFT JOIN vehicle_types as VehicleType ON
Vehicle.vehicle_type_id = VehicleType.id
LEFT JOIN vehicle_colors as VehicleColor ON
Vehicle.vehicle_color_id = VehicleColor.id
LEFT JOIN vehicle_manufacturers as
VehicleManufacturer ON Vehicle.vehicle_manufacturer_id =
VehicleManufacturer.id
-- And this is the Join i'd like to add:
LEFT JOIN cities as City on Business.city_id =
City.id
-- And the conditions as usual:
WHERE $conditions_go_here and (City.Province_id
= 7)
So I would do something like:
$this->Vehicle->bindModel(array('belongsTo' => array(
'City' => array(
'classname' =>
'City',
'foreignKey' =>
'Business.city_id')))); //Note the "foreign" foreign key...
Unfortunately this delivers SQL like:
SELECT * FROM vehicles as Vehicle
-- This is all the standard associations in the model:
LEFT JOIN businesses as Business ON
Vehicle.business_id = Business.id
LEFT JOIN vehicle_types as VehicleType ON
Vehicle.vehicle_type_id = VehicleType.id
LEFT JOIN vehicle_colors as VehicleColor ON
Vehicle.vehicle_color_id = VehicleColor.id
LEFT JOIN vehicle_manufacturers as
VehicleManufacturer ON Vehicle.vehicle_manufacturer_id =
VehicleManufacturer.id
-- And this is the Join I'd like to add:
LEFT JOIN cities as City on
Vehicle.Business.city_id = City.id --This is the line causing
trouble.
-- And the conditions as usual:
WHERE $conditions_go_here and (City.Province_id
= 7)
I can't use ::query(), because I use a Pagination component that uses
findAll to get the count(*)...
I'll try you idea though, thanks!
On Dec 11, 2:21 pm, "Jon Bennett" <[EMAIL PROTECTED]> wrote:
> Hi,
>
> > I have two models Vehicles and City. I want to FindAll Vehicles where
> > City.Province_id = 3. The problem is, Vehicle is related to city via
> > another table, "businesses" like so:
>
> > Vehicle $belongsTo Business $belongsTo City $belongsTo Province
>
> > or in the other direction:
>
> > Province $hasMany City $hasMany Business $hasMany VehicleI tihnk you might
> > want to try unbinding your Buiness and City model,
> then binding it again, adding a Condition to only retrieve Businesses
> for a certain city, eg:
>
> // unbind association
> $this->Vehicle->Business->unbindModel (array('belongsTo'=>array('City')));
> // create new association
> $this->Vehicle-> Business ->bindModel(array('belongsTo'=>array('City '
> =>array('className'=>'City ', 'conditions' => 'City.province_id = ' .
> $province_id))));
> // fetch vehicles
> $this->Vehicle->findAll ();
>
> The above is untested!
>
> One thing to remember is this technique does not reduce your SQL
> queries, though it does limit the data that's returned.
>
> I'm sure there are other techniques, only one else wanna chip in?
>
> Also very interested in peoples opinions of this technique.
>
> Cheers,
>
> Jon
>
> --
>
> jon bennett
> t: +44 (0) 1225 341 039 w:http://www.jben.net/
> iChat (AIM): jbendotnet Skype: jon-bennett
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Cake PHP" 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
-~----------~----~----~----~------~----~------~--~---
