DBIx::Custom- DBIx::Custom
- here
Select a row with the select method
In DBIx::Custom, use the select method to select a row.
my $result = $dbi->select(table =>'book');
Specify the table name in table. The following SQL will be issued.
select * from book;
The return value is a DBIx::Custom::Result object, which can be used to fetch rows.
while (my $row = $result->fetch_hash) {
print $row->{author}. "\ n";
print $row->{title}. "\ n";
}
I'm using the while statement to retrieve all the lines line by line.
Select method options
You can use all the options of the execute method in the select method. In addition, the following options are implemented.
Column name column
Use the column option to specify the column name.
my $result = $dbi->select(
table =>'book',
column => [
'author',
'title'
]
);
Alternatively, you can specify the column name as the first argument of the select method.
my $result = $dbi->select(['author','title'], table =>'book');
The following SQL is issued:
select author, title from book;
Specify column name column
You can specify the column name in various ways using the column option. If the column option is omitted, "*" representing all columns is set as the column name.
(1) String
It is specified by a character string.
column =>'author'
(2) Array reference
You can specify multiple columns in the array reference.
column => ['author','title']
A column name similar to the following is generated.
author, title
(3) Hash reference
You can write a set of table and column names using the hash reference.
column => {book => [qw / title author]}
A column name similar to the following is generated.
book.author as "book.author", book.title as "book.title"
The alias part is properly escaped depending on the database. Internally, the column method is executed.
(4) Combination of array reference and hash reference
You can also specify multiple hash references in the array reference.
column => [
{book => [qw / title author]},
{company => [qw / id name /]}
]
A column name similar to the following is generated.
book.author as "book.author", book.title as "book.title", company.id as "company.id", company.name as "company.name"
You can also mix string and hash references as elements of an array reference as follows:
column => [
'title',
'author',
{company => [qw / id name /]}
]
Specifying conditions by ID id
Use the id option to specify the condition by ID. The primary_key must be set.
id => 4 id => [4, 5]
For example, suppose the following select is executed.
| $dbi->select( primary_key => ['id1','id2'], id => [4, 5], table =>'book' );
This is the same as the next select.
$dbi->select(
where => {id1 => 4, id2 => 5},
table =>'book'
);
Join clause join
Specify the join option to specify the join clause.
join =>'left outer join company on book.company_id = company_id' join => [ 'left outer join company on book.company_id = company_id', 'left outer join location on company.location_id = location.id' ]
Note that the DBIx::Custom join clause has a slightly special implementation. The select method searches for the table name specified in the column and where clauses to find the required join. Unnecessary join clauses are not included in the SQL.
In other words, if the table name is not found in the column option or where option, the join clause will not be concatenated.
For example, suppose you execute the following select.
$dbi->select(
table =>'book',
column => ['company.location_id as location_id'],
where => {'company.name' =>'Orange'},
join => [
'left outer join company on book.company_id = company.id',
'left outer join location on company.location_id = location.id'
]
);
In the above, the table name company appears in the column option. On the other hand, the table name location does not appear in the column or where options.
So you only need to concatenate the company table in the join clause, not the location table.
That will generate the following SQL:
select company.location_id as location_id from book left outer join company on book.company_id = company.id where company.name =?;
The select method tries to find the table name as automatically as possible, but it may not be possible with complex join clauses. In such cases, specify a hash reference instead of a string to specify the required table name, as follows:
$dbi->select(
table =>'book',
column => ['company.location_id as location_id'],
where => {'company.name' =>'Orange'},
join => {
clause =>'left outer join location on company.location_id = location.id',
table => ['company','location']
}
);
Generally speaking, the join clause should contain two table names in the form "table name.column name". Therefore, when using the using clause etc., the table name is not in the form of "table name.column name", so it is necessary to specify it explicitly.
Also, the order of the table names is important. The source table must be on the left and the destination table must be on the right.
# Correct example The source book (book.company_id) is on the left and the destination company (company.id) is on the right. left outer join company on book.company_id = company.id # Wrong example The source book (book.company_id) is on the right and the destination company (company.id) is on the left. left outer join company on company.id = book.company_id
Both have the same meaning for a normal SQL statement, but the parsing results are different for DBIx::Custom.
It was a little difficult explanation, but the advantage that join can be described as it is and that only necessary joins are selected is very big. Also, if you write the join clause naturally, it is unlikely that the analysis will fail.
Parameter specification param
Use the param option to specify a value for a named placeholder that appears before the where clause.
param => {'table2.key3' => 5}
For example, you can use it if you want to use named placeholders in join clauses.
join => ['inner join(select * from table2 where table2.key3 =: table2.key3)'.
'as table2 on table1.key1 = table2.key1']
prefix prefix
If you want to add a string immediately after select, use prefix.
prefix =>'SQL_CALC_FOUND_ROWS'
For example, you can issue the following SQL.
select SQL_CALC_FOUND_ROWS * from book;
table name table
Use the table option to specify the table name.
table =>'book'
Where clause where
Use the where option to write a Where clause.
where => {author =>'Ken','title' =>'Perl'}
where => [
['and',': author {=}',': title {like}'],
{author =>'Ken', title =>'%Perl%'}
]
where option
The where option can be specified in various formats as follows.
Specify by hash reference
When the Where clause is specified in the hash reference, it is a condition that the comparison by equivalent (=) is connected by and.
where => {author =>'Ken','title' =>'Perl'}
The above specification would be a Where clause like this:
where author =: author and title =: title
"Ken" and "Perl" are embedded in named placeholders (: author,: title).
If you specify an array reference as the value, you can write using in .
where => {author => ['Ken','Taro'],'title' =>'Perl'}
The above is the following Where ward.
where author in (: author,: author) and title =: title
The corresponding value is embedded in the named placeholder.
Specify by DBIx::Custom::Where object
You can specify a DBIx::Custom::Where object.
where => $where_obj
See Dynamic Where Clause Generation for the DBIx::Custom::Where object.
Specify by array reference
Specifying by array reference allows you to do the same without creating a DBIx::Custom::Where object.
where => [
['and',': author {=}',': title {like}'],
{author =>'Ken', title =>'%Perl%'}
];;
The above has the same meaning as the following description.
where => $dbi->where(
clause => ['and',': author {=}',': title {like}'],
param => {author =>'Ken', title =>'%Perl%'}
);
Specify by character string
If specified as a string, a simple Where clause will be generated.
where =>'title is null'
The above specification would be a Where clause like this:
where title is null
Get the number of
rows --count
Use the count method to get the number of rows.
my $count = $dbi->count(table =>'book', where => {title =>'Perl'});
The options you can specify are the same as for the select method. "Count (*)" is automatically set in the column.
Currently, it is recommended to use the select method rather than the count method.
my $count = $dbi->select(
'count (*)',
table =>'book',
where => {title =>'Perl'}
)->value;