CODEWITHSUNDEEP
perldbix-class
Always_Beginner
Always_Beginner

Reputation: 2936

Corresponding Perl DBIx::Class for SQL LIKE AND NOT LIKE STATEMENT

I am just a newbie to Perl and I am trying to use Perl DBIx::Class ORM. I want to convert a SQL statement which is sometime like 

SELECT `COLUMN_1` FROM TABLE 
    WHERE `COLUMN_NAME` LIKE "regex1" 
    AND `COLUMN_NAME` NOT LIKE` "regex2".

I know of search_where statement to add conditions but I am not able to find equivalent of LIKE and NOT LIKE ?

Upvotes: 2

Views: 1597

Answers (1)

simbabque
simbabque

Reputation: 54371

search_like is just a convenient shortcut. You probably do not want to use that.

There are several ways to achieve this with search though. Remember that ResultSet calls with search can be chained, to build up the final ResultSet.

$rs->search(
    {
        column_name => { -like => 'foo' },
    }
)->search(
    {
        column_name => { -not_like => 'bar' },
    }
);

Alternatively, you can also just combine them, as shown in this older mailing list post.

$rs->search( 
    { 
        column_name => { 
            -like     => 'foo', 
            -not_like => 'bar', 
        } 
    }
);

Or you can chain them with an AND, which is more verbose.

$rs->search(
    {
        -and => [
            { column_name => { -like     => 'foo' } },
            { column_name => { -not_like => 'bar' } },
        ]
    }
);

All of these do the same thing.

You can find additional information about this syntax in SQL::Abstract, which is used by DBIx::Class under the hood.

Note that LIKE does not take a regular expression.

Upvotes: 3

Related Questions