[Dbix-class] match against ...

[RAPPAZ Francois]

Thanks for replying !

My code is now

 my $sqlmaker = SQL::Abstract->new(special_ops => [  {regex => qr/^match$/i,
     handler => sub{
	my ($me, $field, $op, $arg) = @_;
       $arg = [$arg] if not ref $arg;
       my $label         = $me->_quote($field);
       my ($placeholder) = $me->_convert('?');
       my $placeholders  = join ", ", (($placeholder) x @$arg);
       my $sql           = $me->_sqlcase('match') . " ($label) " . $me->_sqlcase('against') . " ($placeholders) ";
       my @bind = $me->_bindtype($field, @$arg);
       return ($sql, @bind);
     },
     },
  ]);
#this line is suggested by the reply in this page 
#http://nailingjelly.wordpress.com/2009/05/11/extending-sqlabstract/

my ($where, @bind)= $sqlmaker->where ({"ti, ex, ad" => {-match =>  $bla}});

my $rs = $self->{schema}->resultset('Jrn')->search_rs(undef, {where=>\[$where, @bind], order_by => 'tri'});

I still have an error
***   DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st 
execute failed: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 

'WHERE ( MATCH (ti, ex, ad) AGAINST ('test')  ) ) ORDER BY tri' 

at line 1 [for Statement 
"SELECT me.nofm FROM jrn me WHERE (  WHERE ( MATCH (ti, ex, ad) AGAINST (?)  ) ) 
ORDER BY tri" with ParamValues: 0="test"] 
at ...

If one test the where clause in phpmyadmin, one see that there the parenthesis are not balanced ( ()() )).

Is it the way I'm calling using the sqlmaker object ?


Thanks for any suggestion

François

> -----Original Message-----
> From: Will Crawford [mailto:billcrawford1970 at gmail.com]
> Sent: jeudi, 3. avril 2014 11:56
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] match against ...
> 
> On 2 April 2014 10:47, RAPPAZ Francois <francois.rappaz at unifr.ch>
> wrote:
> > I'm struggling with code as
> ...
> >         my ($where, @bind)= $sqlmaker->where("ti, ex, ad", "tri",
> > $bla); $self->{log}->debug($where);
> >          my $rs = $self->{schema}->resultset('Jrn')->search_rs(undef,
> > {where=> $where});
> 
> Couple of things that might help:
> · you don't seem to be passing anything into $sqlmaker->where(...) that
> would match the regex you provided for special_ops (so how would it
> know to create your "match ... against" sql) · you'll probably need to
> pass the SQL into DBIC as { where => \[ $sql, @binds ] }
> 
> This may or may not be enough. A (much simpler) example that works for
> me is:
> 
>     my $user = $rs->search( undef, { where => \[ 'id = ?', [ id =>
> 52521 ] ] } )->first;
> 
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-
> class at lists.scsys.co.uk