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

user_4632019 0
Viewed 30

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?

sql-abstract dbix-class perl
1 Answers

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.

user_46320190
Related