applying sql not function in where clause

[mohanprasad]

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] });

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

[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
>  

_______________________________________________
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@...

[Peter Rabbitson-2]

mohanprasad wrote:
> Hi,
>
> I am trying to prepare the below where clause
> where not(status ='deleted' and created < $timestamp)
>

SQL::Abstract 1.58 just went to cpan (still in incoming).
It allows you to do:

({ -not_bool => {
    status => 'deleted',
    created => { '<', $timestamp },
}})




_______________________________________________
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@...