Postgres full-text search w/ DBIX::Class
How to pass in a search term to postgres using DBIX::Class while still leveraging bind values to escape your input:
my $search_phrase = "snowden nsa"; my @rows = $rs->search(\[ 'to_tsvector(me.title) @@ plainto_tsquery( ? )', $search_phrase ])->all;
As is always the case with SQL::Abstract, TIMTOWTDI. In this case I don't particularly love the ArrayRef instead of the traditional HashRef, but that is how the cookbook had it, and it works. If anyone has a better method please leave it in the comments.
Adapted from the DBIX::Class Cookbook.
CTO / Codemonkey, Socialflow.com
I use
{ $field => \[q/@@ to_tsquery('english', ?)/, [ $field => "$term:*" ]] }Where $field is already a tsvector column maintained with a tsvector_update_trigger