Using Criteria::fromInput with Postgres ENUM type

Question

Using Criteria::fromInput with Postgres ENUM type

PutinVladimir Nov '17

Created Nov '17	Last Reply None	Replies 0	Views 473	Votes 0

PutinVladimir 1.5k

Nov '17

Is there an option to use criteria Criteria::fromInput with Postgres ENUM custom field?

            $query = Criteria::fromInput($this->di, 'Posts', $_POST);
            $this->persistent->parameters = $query->getParams();
            $parameters = $this->persistent->parameters;
            Posts::find($parameters);

SQLSTATE[22P02]: Invalid text representation: 7 ОШИБКА: неверное значение для перечисления lang: "%EN%"

var_dump($query);

object(Phalcon\Mvc\Model\Criteria)[68]
  protected '_model' => string 'Posts' (length=7)
  protected '_params' => 
    array (size=2)
      'conditions' => string '[lang] LIKE :lang:' (length=18)
      'bind' => 
        array (size=1)
          'lang' => string '%EN%' (length=4)
  protected '_bindParams' => null
  protected '_bindTypes' => null
  protected '_hiddenParamNumber' => int 0

var_dump($this->db->describeColumns(posts));
...
    object(Phalcon\Db\Column)[74]
      protected '_name' => string 'lang' (length=4)
      protected '_schemaName' => null
      protected '_type' => int 2
      protected '_typeReference' => int -1
      protected '_typeValues' => null
      protected '_isNumeric' => boolean false
      protected '_size' => int 0
      protected '_scale' => int 0
      protected '_default' => string 'RU' (length=2)
      protected '_unsigned' => boolean false
      protected '_notNull' => boolean true
      protected '_primary' => boolean false
      protected '_autoIncrement' => boolean false
      protected '_first' => boolean false
      protected '_after' => string 'word' (length=4)
      protected '_bindType' => int 2
...

Looks like model use enum field as text field and criteria then generate LIKE %field% instead of = field. Any chance to change it ?

PutinVladimir · Answer 1 · 2017-11-13T02:33:16-07:00

I used workaround. I create LIKE operator for ENUM type in postgresql


CREATE OR REPLACE FUNCTION enums_like_operator(_a lang, _b text)
    RETURNS boolean AS
    $func$
        SELECT _a = (TRIM(both '%' from _b))::lang;
    $func$
    LANGUAGE SQL IMMUTABLE STRICT;

CREATE OPERATOR ~~ (
    leftarg = lang,
    rightarg = text,
    procedure = enums_like_operator,
    COMMUTATOR = =,
    NEGATOR = !~~,
    HASHES, MERGES
);

but exploring phaclon source I see it sets string type for any unknown field in

public function getColumnDefinition(<ColumnInterface> column) -> string
    {
    ...

                default:
                if empty columnSql {
                    throw new Exception("Unrecognized PostgreSQL data type at column " . column->getName());
                }
    ...
    }

It is absolutly wrong. Custom types is not string and should generate something like '=value::type' instead of 'LIKE %value%'.