perldbix-classsql-abstract

How to set sqlt_datatype at SQL::Abstract/DBIx::Class?


I can not write this condition: app_period => { '@>' => '2021-03-15' } because I get an error:

malformed range literal: "2021-03-15"

Here how my query looks:

->search( $cond, @_ )->as_query
  \[
      (SELECT "me"."id", "me"."order_id", "me"."resource_type_id", "me"."service_type_id", "me"."amount", "me"."allocated_resource_id", "me"."last_used", "me"."app_period", "me"."sort_order" FROM "order_detail" "me" WHERE ( ( "app_period" @> ? AND "order_id" = ? ) )),
      [
        {
          dbic_colname => app_period,
          sqlt_datatype => tstzrange,
        },
        2021-03-15,
      ],
      [
        {
          dbic_colname => order_id,
          sqlt_datatype => integer,
        },
        11961,
      ],
    ]

I have found this documentaion and tried:

{ app_period => { '@>' => [ \'timestamptz' => '2021-03-15' ] } }

But got error: column "timestamptz" does not exist

How to set correct sqlt_datatype for bind value?


Solution

  • Seems I found how to accomplish this. I should pass this hash to ->search(...):

    { app_period => \[ '@> ?::timestamptz', [ { sqlt_datatype => 'timestamptz ' },  '2021-03-15' ] ] }
    { app_period => \[ '@> ?::timestamptz', [ \'timestamptz'                     => '2021-03-15' ] ] }
    

    And then I will get desired result:

    \[
        (SELECT "me"."id", "me"."order_id", "me"."resource_type_id", "me"."service_type_id", "me"."amount", "me"."allocated_resource_id", "me"."last_used", "me"."app_period", "me"."sort_order" FROM "order_detail" "me" WHERE ( ( "app_period" @> ?::timestamptz ) )),
        [
          {
            sqlt_datatype => timestamptz,
          },
          2021-03-15,
        ],
      ]
    

    I should instead of value pass ARRAYREF: doc

    [ $name => $val ] === [ { dbic_colname  => $name }, $val ]
    [ \$dt  => $val ] === [ { sqlt_datatype => $dt   }, $val ]
    [ undef,   $val ] === [ {}, $val ]
    $val              === [ {}, $val ]
    

    Where [ {}, $val ] was notices at this section

    enter image description here

    Still I did not changes bindtype, probably DBIx::Class did that.