-
Notifications
You must be signed in to change notification settings - Fork 7
Select row
Select row by select
.
my $result = $dbi->select(table => 'book');
table
option is set to a table name.
The following SQL is executed.
select * from book;
Return value is DBIx::Custom::Result, which can fetch rows.
my $rows = $result->all;
select
method can use all of execute method options, and use the following ones.
Column name.
my $result = $dbi->select(
table => 'book',
column => [
'author',
'title'
]
);
The following SQL is executed.
select author, title from book;
column
option has various way to specify columns.
-
Emit
column
opitonIf you emit
column
, Column name is set to*
.Example:
# select * from book; $dbi->select(table => 'book');
-
string - single column
column => 'author'
Example:
# select author from book $dbi->select(table => 'book', column => 'author');
-
array reference - multiple columns
column => ['author', 'title']
Example:
# select author, title from book $dbi->select(table => 'book', column => ['author', 'title']);
-
hash reference - full-qualified column name
column => {book => ['title', 'author']}
The following full-qualified column names is created.
book.author as "book.author", book.title as "book.title"
Example:
# select book.author as "book.author", book.title as "book.title" from book $dbi->select(table = 'book', column => {book => ['title', 'author']});
Corresponding to RDBMS, column alias is escaped correctly . Internally, "column" in DBIx::Custom method is used .
-
combination with array reference and hash reference
column => [ {book => [qw/title author]}, {company => [qw/id name/]} ]
The following full-qualified column names is created.
book.author as "book.author", book.title as "book.title", company.id as "company.id", company.name as "company.name"
Example:
# select # book.author as "book.author", # book.title as "book.title", # company.id as "company.id", # company.name as "company.name" # from book $dbi->select( table = 'book', column => [ {book => [qw/title author]}, {company => [qw/id name/]} ] );
Of course, Array referece contains both string and hash reference.
column => [ 'title', 'author', {company => [qw/id name/]} ]
-
column alias separater
Column alias separater default is
.
.book.author as "book.author"
This can be changed by "separator" in DBIx::Custom attribute.
$dbi->separator('-');
Column alias separater is changed.
book.author as "book-author"
Set parameter value without column name. primary_key
must be set to column names.
id => 4
id => [4, 5]
Where condition using primary_key
's column names is created.
$dbi->select(
primary_key => ['id1', 'id2'],
id => [4, 5],
table => 'book'
);
This is same as the following one.
$dbi->select(
table => 'book',
where => {id1 => 4, id2 => 5}
);
join clause.
join => 'left join company on book.company_id = company_id'
join => [
'left join company on book.company_id = company_id',
'left join location on company.location_id = location.id'
]
DBIx::Custom has a little special implementation in join
option. select
method will find needed join clause by searching table name that column clause and where clause contains. no needed join cluase is execuled.
For example, the following select
is executed.
$dbi->select(
table => 'book',
column => ['company.location_id as location_id'],
where => {'company.name' => 'Orange'},
join => [
'left join company on book.company_id = company.id',
'left join location on company.location_id = location.id'
]
);
column
option contain company
table in "company.location_id", but "location" table is not exists in where
option or column
option.
So, Only "company" table is needed to join, not "localtion" table.
The following SQL is executed.
select company.location_id as location_id
from book
left join company on book.company_id = company.id
where company.name = ?;
select
method search table name automatically in join
option, Table name must have the format TABLE_NAME.COLUMN_NAME
like "company.name".
but sometime join clause is so complex and can't find table name correctly. You can specify table name explicitly by hash reference.
$dbi->select(
table => 'book',
column => ['company.location_id as location_id'],
where => {'company.name' => 'Orange'},
# Explicitly table name
join => {
clause => 'left join location on company.location_id = location.id',
table => ['company', 'location']
}
);
Table name's order is very very important in join
option. For example, "book" table is based, and joined "company" table.
Based table must be left to =
, joined table must be right to =
.
# Correct: based table "book" is left, joined table "company" is right
left join company on book.company_id = company.id
# Wrong: based table "book" is right, joined table "company" is left
left join company on company.id = book.company_id
RDBMS think both join clause is same, but select
method think two join clause is different. Be careful. This implementation is needed to find only needed join clause.
If you specify paramters before where clause, use param
option.
param => {'table2.key3' => 5}
For example, a case that you want to use named place holder in join
clause.
join => [
'inner join (select * from table2 where table2.key3 = :table2.key3) as table2 on table1.key1 = table2.key1'
]
Add some strings between select
and COLUMN_NAMES
.
prefix => 'SQL_CALC_FOUND_ROWS'
Example:
$dbi->select(
table => 'book',
prefix => 'SQL_CALC_FOUND_ROWS',
column => ['title', 'author']
);
The following SQL is executed.
select SQL_CALC_FOUND_ROWS title, author from book;
Table name.
table => 'book'
Write where clause by where
option.
# (1) Hash reference
where => {author => 'Ken', 'title' => 'Perl'}
# (2) String
where => "author = 'Ken' and title = 'Perl'";
# (3) Array reference [ Array reference, Hash reference ]
where => [
['and', 'author = :author', 'title like :title'],
{author => 'Ken', title => '%Perl%'}
]
See where option detail and How to create where clause to know where
option usage.
column
option has various way to specify columns.