Cakephp Validate uniqueness of name field w.r.t other field of same table

Question

I am a kind of noob in cakephp and While working with this validation rule`, I were unlucky to get the satisfactory response. In my project, I have to valiidate the name w.r.t foreign key and many other attribute like is_active Lets Say

Table t1 has attributes (id, name varchar, is_active Boolean)

table t2 has attribute (id, name varchar, t1_id (foreign key to table t1), is_active boolean)

Now I want to validate unique name of table t1 of is_active group

And validate uniqueness of t2.name w.r.t is_active=1 and t1_id=specific_value

I googled and found a link regarding this with no luck :( http://www.dereuromark.de/2011/10/07/maximum-power-for-your-validation-rules/

Any help will really be appreciated.

Answer 1

First some suggestions that may be worth considering;

Soft delete

Apparently you're trying to implement a 'soft delete' in your website. Soft-deletes are sometimes wanted in case you want to delete something, but being able to 'undelete' it in a later stage.

However, by allowing both active and inactive items to share the same name, 'name' is no longer unique (based on your question, unique names are a requirement).

This will prevent you from undeleting an item, because at that point, two items with the same name exist in your database, both active.

Here are some discussions on the subject of 'soft deletes';

Are soft deletes a good idea?

http://richarddingwall.name/2009/11/20/the-trouble-with-soft-delete/

Revisions/History

If you're trying to implement a 'revision history', not a 'soft delete', it's best to store revisions in a separate table. There are already some plugins for CakePHP that may handle this for you. I don't have links to them, but you'll be able to Google for that.

Custom validation

Back to your question; you are able to check if a record is unique within the 'active' group by creating your own validation-rule inside the Model, for example:

public function isNameUniqueActive()
{
    if (
        array_key_exists('is_active', $this->data[$this->alias])
        && 1 != $this->data[$this->alias]['is_active']
    ) {
        // Record to save is not 'active', so no need to check if it is unique
        return true;
    }

    $conditions = array(
        $this->alias . '.name'  => $this->data[$this->alias]['name'],
        $this->alias . '.is_active' => 1,
    );

    if ($this->id) {
        // Updating an existing record: don't count the record *itself*
        $conditions[] = array(
            'NOT' => array($this->alias . '.' . $this->primaryKey => $this->id)
        );
    }

    return (0 === $this->find('count', array('conditions' => $conditions)));
}

You'll be able to use this validation just like the built-in validation rules;

public $validate = array(
    'name' => 'isNameUniqueActive',
);

update

To also check if a name is unique within a group (based on foreign-key t1_id), try this:

public function isNameUniqueWithinGroup()
{
    if (
        array_key_exists('is_active', $this->data[$this->alias])
        && 1 != $this->data[$this->alias]['is_active']
    ) {
        // Record to save is not 'active', so no need to check if it is unique
        return true;
    }

    if (!array_key_exists('t1_id', $this->data[$this->alias])) {
        // No group specified - not valid?
        return false;
    }

    $conditions = array(
        $this->alias . '.name'  => $this->data[$this->alias]['name'],
        $this->alias . '.is_active' => 1,
        $this->alias . '.t1_id' => $this->data[$this->alias]['t1_id'],
    );

    if ($this->id) {
        // Updating an existing record: don't count the record *itself*
        $conditions[] = array(
            'NOT' => array($this->alias . '.' . $this->primaryKey => $this->id)
        );
    }

    return (0 === $this->find('count', array('conditions' => $conditions)));
}