[Dbix-class] Does DBIx::Class support postgresql arrays and ANY syntax?

[Adam Witney]

On 18 Feb 2009, at 11:17, Joel Bernstein wrote:

> 2009/2/18 Adam Witney <awitney at sgul.ac.uk>:
>>> Does DBIx::Class support PostgreSQL array columns? I would  
>>> normally query
>>> using something like this:
>>>
>>> SELECT * FROM test1 WHERE 1 = ANY(my_array_col);
>>>
>>> I can't seem to find anything via google at this point.
>>
>>
>> following up from my own question, I have managed to get this  
>> working like
>> this:
>>
>> my $str = ' = ANY(my_array_col)';
>> my $expt_rs = $schema->resultset('test1')->search( { '1' => \ 
>> $str } );
>>
>> which seems to be a little bit of a hack... does anyone know if  
>> there is a
>> better way of approaching this?
>
> Why do you feel that is a hack? It seems equivalent to your original  
> query..
>  $rs->search({ 1 => \'sql literal here', })
>
> SQL::Abstract's POD says:
> "Finally, sometimes only literal SQL will do. If you want to include
> literal SQL verbatim, you can specify it as a scalar reference,
> namely:
>
>    my $inn = 'is Not Null';
>    my %where = (
>        priority => { '<', 2 },
>        requestor => \$inn
>    );
> "
> It's a perfectly common idiom so I'm not too sure why you feel it's  
> hacky?


only really because i was writing SQL in my script, rather than having  
it all hidden behind the scenes. I am fairly new to DBIx::Class so  
still getting to grips with that is considered the best way of doing  
things. Glad to hear that i am not doing anything daft though.

thanks for your help

adam