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?
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
Still I did not changes bindtype
, probably DBIx::Class
did that.