DBIx::Custom

Dynamically create a where clause

I will explain how to "dynamically create a where clause" with DBIx::Custom.

Dynamic Where clause generation

DBIx::Custom can dynamically generate a Where clause. For example, if a given parameter contains only title, generate a Where clause that contains only the title condition, and if the parameter contains only price, then only the price condition is included. It's easy to generate a Where clause.

Basics of dynamic Where clause generation

DBIx::Custom::Where Object Creation

First, create a DBIx::Custom::Where object. You can use the DBIx::Custom where method to create a DBIx::Custom::Where object. 

my $where = $dbi->where;

Where clause settings

Write the Where clause using the clause attribute. (: title,: price are named placeholders. See the description of execute method.) 

$where->clause(['and','title like: title','price =: price']);

You need to specify an array reference, the first element being the operator. Operators can use "and" and "or". After the second element, you specify the condition, but you need to include only one named placeholder.

You can also specify a character string if there is only one condition. 

$where->clause('title like: title');

In this case, it has the same meaning as the following description. 

$where->clause(['and','title like: title']);

Where clause generation

To generate a Where clause, use the to_string method or evaluate the DBIx::Custom::Where object as a string. 

# to_string method
my $where_clause = $where->to_string;

#Evaluated as a string
my $where_clause = "$where";

You can generate a Where clause like this: 

where title like: title and price =: price

Dynamic Where clause generation

You can easily generate a Where clause dynamically. To generate a Where clause that contains only the price condition, pass the hash reference data that includes price in the param key. 

$where->clause(['and','title like: title','price =: price']);
$where->param({price => 1900});
my $where_clause = "$where";

A Where clause similar to the following is generated: 

where price =: price

To generate a Where clause that contains only the title condition, pass the hash reference data that includes the title in the param key. 

$where->clause(['and','title like: title','price =: price']);
$where->param({title =>'Perl'});
my $where_clause = "$where";

A Where clause similar to the following is generated: 

where title like: title

If neither is included, the Where clause will not be generated and you can get the empty string. 

$where->clause(['and','title like: title','price =: price']);
$where->param({});
my $where_clause = "$where"; #empty string

Use the column name prefixed with the table name

You can also use the column name prefixed with the table name. It also supports when you need to distinguish it from columns in other tables. 

$where->clause(['and','book.title like: book.title','book.price =: book.price']);

Application of Where clause generation

When two or more columns with the same name are included

For example, if you want to write a comparison condition, you need to write a condition that includes the same column name. We also support such cases. The parameter must have a value specified in the array reference. 

$where->clause(['and','price>: price','price <: price']);
$where->param({price => [1000, 2000]});

A Where clause similar to the following is generated: 

where price>: price and price <: price

If you want to dynamically generate only the first condition, do the following: The second element of the array reference is a DBIx::Custom::NotExists object that indicates it doesn't exist. This can be obtained with the not_exists method. 

$where->clause(['and','price>: price','price <: price']);
$where->param({price => [1000, $dbi->not_exists]});

A where clause similar to the following is generated. 

where price>: price

If you want to dynamically generate only the second condition, do the following: The first element of the array reference is a DBIx::Custom::NotExists object that indicates it doesn't exist. 

$where->clause(['and','price>: price','price <: price']);
$where->param({price => [$dbi->not_exists, 2000]});

A where clause similar to the following is generated. 

where price <: price

Complex conditions

You can also write complex conditions, such as nested conditions. 

$where->clause(
  [
    'and',
    'price =: price',
    ['or','title =: title','title =: title','title =: title']
  ]
);
$where->param({price => '1000', title => ['Perl','Ruby','Python']});
my $where_clause = "$where";

A Where clause similar to the following is generated: 

where price =: price and (title =: title or title =: title or title =: title)

Even if the nesting structure becomes complicated, the Where clause can be generated correctly and dynamically. For example, it will correctly generate a Where clause if the following parameters are passed: 

$where->param({price => 1900, title => ['Perl','Ruby']});

Since the array reference contains only two values, the following Where clause is generated: 

where price =: price and (title =: title or title =: title)

Conditions that do not include named placeholders

You can also write conditions that do not include named placeholders. 

$where->clause(['and', "title ='Perl'"]);

Regardless of the value of the parameter, this condition will always be included in the where section.

or search by condition

To search using the or condition, do the following. DBIx::Custom provides a DBIx::Custom::Where object to handle complex Where conditions when the Where condition becomes complicated. You can use the where method to create a new, well-configured DBIx::Custom::Where object. 

# where (title ='Perl' or title ='Ruby') and price> 1500
my $where = $dbi->where;
my $titles = [qw / Perl Ruby /];
my $clause = ['and',
  ['or', ('title =: title') x @$titles],
  'price>: price'
];;
my $param = {title => $titles, price => 1500};
$where->clause($clause);
$where->param($param);

The following is a sample using SQLite. The model is also used well, so the code is less duplicated. 

use strict;
use warnings;

use DBIx::Custom;

#Connect
my $dbi = DBIx::Custom->connect(dsn => "dbi: SQLite: dbname =: memory:");#Create table
$dbi->execute("create table book (title, price)");
my $model = $dbi->create_model(table =>'book');
$model->insert({title =>'Perl', price => 1900});
$model->insert({title =>'Ruby', price => 2000});
$model->insert({title =>'Python', price => 2100});

# Where, (title ='Perl' or title ='Ruby') and price> 1500
my $where = $dbi->where;
my $titles = [qw / Perl Ruby /];
my $clause = ['and',
  ['or', ('title =: title') x @$titles],
  'price>: price'
];;
my $param = {title => $titles, price => 1500};
$where->clause($clause);
$where->param($param);

#Select
my $result = $model->select(where => $where);

# Fetch
my $rows = $result->all;

Specify the date range in the Where clause

Retrieving data from a database date to a certain date is a common operation. Both dates may be specified, or only one date may be specified. In such a case, it is troublesome to write a conditional branch by yourself. If you use the DBIx::Custom::Where object, you can write it with a little ingenuity. 

my $start_date = '2010-01-01';
my $end_date = '2010-03-31';

# DBIx::Custom::Where object
my $where = $dbi->where;

#Where clause description
$where->clause(['and',': date {>}',': date {<}'}]);

# Parameter setting
my $param = {
  date => [
    $start_date? $start_date: $dbi->not_exists,
    $end_date? $end_date: $dbi->not_exists  ]
};;
$where->param($param);

Run #select
$dbi->select(table =>'book', where => $where);

First, create a DBIx::Custom::Where object with the where method. Next, in the clause, write the Where clause. This is described using the DBIx::Custom tag. In the above example 

['and',': date {>}',': date {<}'}]

Is expanded as follows. 

where date>? and date <?

This is dynamic, for example, if no start time is given, it expands as follows: 

where date <?

Next is the description of the parameters. Since you need to specify two dates, specify them in the array reference. If it does exist, specify its value, and if it does not exist, use the not_exists method to specify the DBIx::Custom::NotExists object. 

# Parameter setting
my $param = {
  date => [
    $start_date? $start_date: $dbi->not_exists,
    $end_date? $end_date: $dbi->not_exists  ]
};;
$where->param($param);

Finally, pass the created DBIx::Custom object to where in the select area. 

Run #select
$dbi->select(table =>'book', where => $where);

Associated Information

Perl Club