[Dbix-class] applying sql not function in where clause

[Matt Whipple]

mohanprasad wrote:
> Hi,
>
> I am trying to prepare the below where clause
> where not(status ='deleted' and created < $timestamp)
>
> in the above where clause status is varchar data type and created is
> timestamp data type.
>
> I am trying to achieve the same in my catalyst application using below code.
> my $sql = SQL::Abstract->new;
> my ($sub_stmt, @sub_bind) = $sql->where({status => 'deleted', created =>
> {"<" => $timestamp}});
> $sub_stmt =~ s/^ where //i;
> $c->model('Database::Product')->search({ \["NOT ($sub_stmt)" => @sub_bind]
> });
>
>   
There may be a better way to write this but I'd recommend to start:
    Turn on DBIC_TRACE to output the SQL.
    I'd guess in the line above you're looking for a scalar 
reference...not an arrayref reference (if that's even what what is).
    I believe the scalar reference will be more or less passed directly 
into the SQL, so the insertion of parameters should be done beforehand.
    Using SQL::Abstract seems like far more trouble than it's worth 
here.  Either find a structure that can be passed directly to DBIC or 
just start with the SQL (I'd recommend the latter at least initially). 

> i tried to use the example provided in
> http://search.cpan.org/~ribasushi/SQL-Abstract-1.56/lib/SQL/Abstract.pm
> section "Literal SQL with placeholders and bind values (subqueries)"
>
> Its giving error in where condition.
> could any one please let me know where i am doing wrong?
>
> Best Regards,
> Mohan
>