Use CodeIgniter is_unique form validation with Postgres Table in a schema

Question

My database is Postgres and I have organized my tables in schemas. When I try to use the is_unique form validation rule it does not work. For instance, if the table I wish to use for the unique check is products.catalog and the column I want to use is name. When I run the validation like below.

$this->form_validation->set_rules("name", "Name", 'required|is_unique["products.catalog.name"]');

$this->form_validation->run();

I get an error like this

Error Number: 42P01/7

ERROR: relation "products" does not exist LINE 2: FROM "`products" ^

SELECT * FROM "products" WHERE "catalog" = 'bags' LIMIT 1

Filename: libraries/Form_validation.php

Line Number: 1122

I can do this check myself using raw PHP though but I was wondering if CodeIgniter provides a way around this.

Answer 1

you can make your own 'is_unique' function in '/system/libraries/Form_validation.php'

 public function is_unique_with_schemas($str, $field)
    {
        list($table, $field)=explode('_', $field);
        $query = $this->CI->db->limit(1)->get_where($table, array($field => $str));

        return $query->num_rows() === 0;
    }

i changed delimiter . to _

you can then use these new function 'is_unique_with_schemas[schema.tableName_columnName].

p.s don't forget to set new 'error message'