Database Pick List in CakePHP?

I'm designing the database for a mental health agency where the clinicians choose from a list of pre-formatted choices for a lot of categories (employment status, housing status, relationship status, etc.) In a previous iteration of the database, we used a pick list similar to one described on stackoverflow here to avoid creating tables for each of these lists.

We're planning on using CakePHP as the framework for the new build and I'm concerned about CakePHP's "automagic" ability to save and display data easily not playing nicely with a database that uses the pick list structure. I'm considering creating a separate table for each list to take ad开发者_JS百科vantage of CakePHP's automagic.

Does anyone have experience implementing a pick list in CakePHP or have a suggestion regarding an alternative design approach?

EDIT: I'm trying to implement this using two tables -

value_lists                     list_values
-----------             -------------------------------
id | list_name          id | list_value | value_list_id   

Here is my model code:

<?php
class BirthplaceCounty extends AppModel {
var $name = 'BirthplaceCounty';
//The Associations below have been created with all possible keys, those that are not needed can be removed
var $useTable = 'list_values';
var $displayField = 'list_value';
var $hasMany = array(
    'Registration' => array(
        'className' => 'Registration',
        'foreignKey' => 'birthplace_county_id',
        'dependent' => false,
        'conditions' => array('BirthplaceCounty.value_list_id' => '8'),
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'exclusive' => '',
        'finderQuery' => '',
        'counterQuery' => '',
    )
);

}

My issue seems to be that no matter what conditions I try -

 $this->BirthplaceCounty->find('list');
   or
 $this->BirthplaceCounty->find('all');

always returns all of the records in list_values. The SQL that CakePHP is producing is

SELECT `BirthplaceCounty`.`id`, `BirthplaceCounty`.`list_value` 
FROM `list_values` AS `BirthplaceCounty` WHERE 1 = 1

No matter how I change the condition, the WHERE clause is always 1 = 1. How do I write the conditions correctly?

EDIT 2: Ok - I realized that setting conditions in the $hasMany variable won't affect the find() function. I created a new function that works.

function getActual() {      
    $conditions = array('BirthplaceCounty.value_list_id = 5');      
    return $this->find('list', compact('conditions'));
}

Is the only way to do this by overloading the find() function?

---

I don't see any problem in using that with CakePHP. I understand when @Anh Pham says that it could be "quite a hassle", but honestly almost anything database-related can become a hassle in Cake if your site is a little more complex than the blog tutorial.

Cake is my PHP framework of choice, but I believe you have to use it for a while and try to understand how the "magic" works. When you do, it's much easier to decide when you should let Cake handle some database operation by itself, and how to trick it into doing what you want in an elegant way.

That "pick list" structure can be made to play nice with Cake's model associations, it's just a matter of adding the right conditions and foreignKey when declaring the association.

UPDATE

I can see from the latest edits that you are on the right track. I believe you now understand why your $this->BirthplaceCounty->find('list') call was returning the whole table: the model doesn't know that you want to filter by value_list_id = 5 (5 or 8?), that condition only applies to its association with the other model.

Anyway, you do have another options beside overloading find(): you can define the default condition inside the model's beforeFind callback, like this:

function beforeFind($queryData) {
    $defaultConditions = array("BirthplaceCounty.value_list_id = 5");
    if(!empty($queryData['conditions'])) {
        // Make sure 'conditions' is an array, so we can array_merge
        $queryData['conditions'] = is_array($queryData['conditions']) : $queryData['conditions'] : array($queryData['conditions']);
        // Merge conditions passed to find with the default
        $queryData['conditions'] = array_merge($defaultConditions, $queryData['conditions']);
    } else {
        $queryData['conditions'] = $defaultConditions;
    }
    // Return the modified $queryData to be used by find
    return $queryData;
}

---

Creating separate tables would be the Cake way of doing it. Doing the other way would be quite a hassle.

To keep the overhead low, you can use the ArraySource in here so you don't have to query the database for these things, while still have all the convenience of using models.