CakePHP filter list by slug instead of ID

Question

I'm using CakePHP, this is the structure of my DB:

CarMakes
----------------------------------
ID     Slug     Name
16     ford     Ford

CarModels
----------------------------------
ID     Name     CarMake_ID
10     Escort   16

Cars
----------------------------------
ID     Name     CarModel_ID
1      My car   10

I want to view a list of cars by CarMakes.Slug

so the url would be: http://localhost/cars/ford

Any ideas or general directions of information?

Answer 1

You can use findBy() or findAllBy() to retrieve records based on something other than ID. If you need to supply conditions to the query, use regular find():

$this->Car->find(
   'all',
   array(
      'conditions' => array(
         'CarMake.Slug' => $slug,
         'Car.Name LIKE' => $name
      ),
   )
);

Also, for the URL you're trying to set up, you will need to create a route for /cars:

Router::connect(
   '/cars/:make',
   array('controller' => 'cars', 'action' => 'bymake'),
   array(
      'pass' => array('make'),
      'make' => '[A-Za-z]+'
   )
);

Edit:

The above works if your conditions are based on a direct association on your model. If your conditions are on a recursive association (i.e. Car->CarModel->CarMake), you need to use explicit joins:

$result = $this->Car->find('all', array(
    'joins' => array(
        array(
            'table' => 'car_models',
            'type' => 'inner',
            'foreignKey' => false,
            'conditions' => array('car_models.id = Car.car_model_id')
        ),
        array(
            'table' => 'car_makes',
            'type' => 'inner',
            'foreignKey' => false,
            'conditions' => array(
                'car_makes.id = car_models.car_make_id',
                'car_makes.slug' => $slug
            )
        )
    ),
    'conditions' => array(
        'Car.name LIKE' => $name
    )
));