Select row

select row

DBIx::Custom Documents >

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's option

select method can use all of execute method options, and use the following ones.

column - column name

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 opiton

  If 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" 

id - ID

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 - join clause

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.

param - Another parameters

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'
]

prefix - prefix

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 - Table name

Table name.

table => 'book'

where - where clause

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.