Validate unique two table

Need Help
1

I have two table:

products_items table have column: id,site_id
products_items_texts have column: products_item_id, language_id, slug

How to buildRules so that it validates the unique record, three columns together - site_id, language_id, slug

It is possible?

Thank you

2

You’re going to have to write your own rule for this. The documentation for Rules shows an example which boils down to this:

public function buildRules(RulesChecker $rules): RulesChecker
{
    // the callable will receive the entity as an argument
    // $options are not important in this case. See documentation for details
    $custom = function($entity, $options) { /*function body*/ }

    $rules->add($custom , [
        'errorField' => '', /* what field would the error flag? */
        'message' => ',' /* the error message you want */
    ]);

    return $rules;
}

Read this for information about PHP Callables
For more information about custom rules read everything after Validating Data - 4.x

Your situation

The main problem with your situation is you want to check values on two different entities and by default the Rule you write will only get one entity as an argument.

So your $custom function would have to do additional queries to insure the pattern of data is unique.

Here is a similar rule I wrote that required an additional query (CakePHP 4.x project):

// in function buildRules() I added this rule
// I used a different style of php callable
        $rules->add(
            [BusinessRules::class, 'uniqueNameInScope'],
            'uniqueNameInScope',
            [
                'foreign_key' => 'warehouse_id',
                'errorField' => 'name',
                'message' => "This tenant name is not unique.",
            ]);

// the rule callable BusinessRules::uiqueNameInScope()

    /**
     * Is the field value unique within a similarly linked set of records
     *
     * This rule does a query to find all other same-type records that share a
     * common foreign key link. With the source record and all other members
     * linked to the same parent, we compare a single column value to see that
     * this new records column is unique.
     *
     * Collisions with records linked to other parents are allowed.
     *
     * Both new records and edited records are checked because an edit may
     * make a new collision. To do edit checks, this current record being
     * tested is removed from the list of found records and the check is
     * made against the remainder.
     *
     * Option keys
     *
     * Required:
     * 'foreign_key' the key to search on to find the possible collision-set
     * 'errorField' the field that will be tested for collisions
     *
     * Other keys:
     * 'repository' the current table, provided by the save() process
     * 'ignore_empty' boolean true to allow empty value without unique checking
     *
     * @param $entity Entity the entity that is a save/delete candidate
     * @param $options array options for the rule
     * @return bool result of the test
     */
    static public function uniqueNameInScope($entity, $options)
    {
        $name = $options['errorField'];

        if (($options['ignore_empty'] ?? false) && empty($entity->$name)) {
            return true;
        }

        $condition = isset($entity->id) ? ['id !=' => $entity->id] : [];
        $foreign_key = $options['foreign_key'];

        $names = $options['repository']
            ->find('list', ['valueField' => $name])
            ->where($condition + [$foreign_key => $entity->$foreign_key])
            ->toArray();

        return !in_array($entity->$name, $names);
    }

With all these ideas in hand, I recommend looking at your data schema to insure that it’s organized the way you want. You may be able to simplify the Rule by getting all the factors into one entity. But in any case, your Rule can be written using the techniques I’ve described.

1 Like