How Can I Generate The Sql Query Using Sql::abstract?

How do I generate the WHERE clause for this query using SQL::Abstract: SELECT COUNT(*) FROM table WHERE id = 111 AND NOT FIND_IN_SET(type, '1,2,3,4') AND status = 'pending';

Solution 1:

See the not_bool unary operator option:

use SQL::Abstract;

my $sql = SQL::Abstract->new;

my $where = {
    id =>111,
    status =>'pending',
    -not_bool =>"FIND_IN_SET(type, '1,2,3,4')",
};

my ($query, @bind) = $sql->select( 
    'table',
    'count(*)',
    $where,
);

This is how $query looks:

SELECTcount(*) FROMtableWHERE ( ( (NOT FIND_IN_SET(type, '1,2,3,4')) 
AND id = ? AND status = ? ) )

Solution 2:

This code generates the WHERE clause:

my $sql = SQL::Abstract->new;    

my %where = (
    id =>111,
    -nest => \"NOT FIND_IN_SET(type, '1,2,3,4')",
    status =>'pending',
);

my ($stmt, @bind) = $sql->where(\%where, \@order);

Solution 3:

FIND_IN_SET isn't standard SQL, so SQL::Abstract doesn't have support for it. You can however put any literal SQL into an SQL::Abstract query. I expect your solution lies down that route.

Solution 4:

Take a look at: DALMP

Database Abstraction Layer for MySQL using PHP

0% fat and extremely easy to use. Only connect to database when needed.

http://code.google.com/p/dalmp/