DBIx::Custom

Row fetch

I will explain how to "fetch rows" with DBIx::Custom.

Fetch rows

The return value of execute and select methods is a DBIx::Custom::Result object. The DBIx::Custom::Result class has many methods for fetching rows.

Fetch line by line (array)

Use the fetch method to fetch a row and fetch that row as an array reference. 
#fetch a row and put it into array refrence
my $row = $result->fetch;

You can get the following data. 

['Perl','Ken']

Generally, it is used together with while statement as follows. If there are no more rows that can be fetched, undef will be returned. 

# Fetch with while loop
while (my $row = $result->fetch) {
  my $title = $row->[0];
  my $author = $row->[1];
}

Fetch only one row (array)

Use the fetch_one method to fetch only one row and fetch that row as an array reference. The finish method of the statement handle is automatically called. 

# Fetch only a row and put it into array reference
my $row = $result->fetch_one;

You can get the following data. 

['Perl','Ken']

Fetch all rows (array)

Use the fetch_all method to fetch all the rows and get them as an array reference for the array. 

# Fetch all rows and put them into array of array reference
my $rows = $result->fetch_all;

You can get the following data. 

[
  ['Perl','Ken'],
  ['Ruby','Mike']
]

Fetch line by line (hash)

Use the fetch_hash method to fetch a row and fetch that row as a hash reference. 

# Fetch a row and put it into hash reference
my $row = $result->fetch_hash;

You can get the following data. 

{title =>'Perl', author =>'Ken'}

Generally used with a while loop as follows: If there are no more rows that can be fetched, undef will be returned. 

# Fetch with while loop
while (my $row = $result->fetch_hash) {
  my $title = $row->{title};
  my $author = $row->{author};
}

Fetch (hash) only one row

Use the fetch_hash_one method to fetch only one row and fetch that row as an array reference. The finish method of the statement handle is automatically called. 

# Fetch only a row and put it into hash reference
my $row = $result->fetch_hash_one;

You can get the following data. 

{title =>'Perl', author =>'Ken'}

The method called one is an alias for the fetch_hash_one method, which can be written shorter. 

#Alias ​​of fetch_hash_one
my $row = $result->one;

Fetch (hash) all rows

Use the fetch_hash_all method to fetch all rows and get them as a reference to an array of hashes. 

# Fetch all rows and put them into array of hash reference
my $rows = $result->fetch_hash_all;

Get the following data. 

  [
    {title =>'Perl', author =>'Ken'},
    {title =>'Ruby', author =>'Mike'}
  ]

The method called all is an alias for the fetch_hash_all method, which can be written shorter. 

#Alias ​​of fetch_hash_all
my $rows = $result->all;

Convenient fetch method

The DBIx::Custom::Result class has other useful methods for fetching.

value

The value method is useful for getting a single value. You can only get the first value in the first row. If it cannot be fetched, it returns undef. 

my $value = $result->value;

The value method is useful when you want to get the number of cases by using the count function. 

my $count = $dbi->select('count (*)')->value;

It has almost the same meaning as the following description. 

my $count = $dbi->select('count (*)')->fetch_one->[0];

values ​​

The values ​​method is useful for getting multiple values ​​in the first column. 

my $values ​​= $result->values;

For example, it can be used to get a list of tables by executing the "show tables" statement of MySQL. 

my $tables = $dbi->select('show tables')->values;

This has the same meaning as the following description. 

my $rows = $dbi->select('show tables')->fetch_all;
my $tables = [map {$_->[0]} @$rows];

flat

The flat method makes a flat list of the retrieved row values. 

my @list = $result->flat;

For example, suppose you want to perform the following process. 

my @list = $dbi->select(['id','title'])->flat;

And suppose the table looks like this: 

(Header) id title
         1 Perl
         2 Ruby

Then the flat method returns a list like the one below. 

(1,'Perl', 2,'Ruby')

You can easily get a key / value pair by assigning it to a hash. 

my%titles = $dbi->select(['id','title'])->flat;

kv

The kv method is useful for getting key / value pairs. 

my $key_value = $result->kv;
my $key_values ​​= $result->kv(multi => 1);

For example, to get a hash reference with the book ID as the key and the title and author values: 

my $books = $dbi->select(['id','title','author'])->kv;

This will give you the following data: 

{
  1 => {title =>'Perl', author =>'Ken'},
  2 => {title =>'Ruby', author =>'Taro'}
}

kvs

If you want to correspond multiple values ​​to one key, use the kvs method. 

my $books = $dbi->select(['author','title','price'])->kvs;

Values ​​similar to the following are returned. 

{
  Ken => [
    {title =>'Perl', price => 1000},
    {title =>'Good', price => 2000}
  ],,
  Taro => [
    {title =>'Ruby', price => 3000},
    {title =>'Sky', price => 4000}
  ]
}

Associated Information

Perl Club