NAME

DBIx::Class::Manual::SQLHackers::SELECT - DBIx::Class for SQL Hackers - SELECT

Introduction
CREATE
INSERT
SELECT
UPDATE
DELETE
BEGIN, COMMIT

SELECTing data

Fetching rows from a query

SELECT id, username, dob, realname, password
FROM users;

In DBIx::Class queries (or more specifically query plans) are represented by ResultSet objects. These are created by calling search on existing resultsets, while passing new search conditions or attributes. A query is not run against the database until data is explicitly requested.

You can either fetch all the data at once, or iterate over the results:

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. The resultset method returns a ResultSet representing a query retrieving all columns of the given ResultSource without conditions:

my $user_resultset = $schema->resultset('User');

3. Fetch all users as Row objects using the all method:

my @users = $user_resultset->all();

4. OR, fetch each user as a Row object using next:

while( my $user = $user_resultset->next()) {
}

Fetching column values from a Row object

The Row object represents the results from a single data source in the query. The column values can be retrieved by using the accessor methods named after the column names. (By default that is, accessors can be changed in the Result Class if needed).

print $user->username;

See the DBIx::Class::Row documentation for more things you can do with Row objects.

Simple SELECT, one row via the primary key

SELECT id, username, dob, realname, password
FROM users
WHERE id = 1;

The find method on a ResultSet is a shortcut to create a query based on the inputs, run the query, and return a single row as a Row object result.

If passed a condition which matches multiple rows, a warning is given.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the find method on the resultset for the ResultSource you wish to fetch data from:

my $fred_user = $schema->resultset('User')->find({ id => 1 });

$fred_user is a now Row object.

Simple SELECT, one row via a unique key

SELECT id, username, dob, realname, password
FROM users
WHERE username = 'fredbloggs';

find also works well on unique constraints, for example the username of our user. Unique constraints can be defined on Result classes using add_unique_constraint (See CREATE).

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the find method on the resultset for the ResultSource you wish to fetch data from:

my $fred_user = $schema->resultset('User')->find(
  { username => 'fredbloggs' },
  { key => 'uniq_username' }
);

"uniq_username" is the name of a constraint defined on the User ResultSource which specifies that the username column is unique across the table. The second argument to find is a set of attributes, of which the "key" attribute defines which constraint to do a lookup on.

Simple SELECT, with WHERE condition

SELECT id, username, dob, realname, password
FROM users
WHERE dob = '1910-02-01';

To select all users born on the date '1910-02-01', we can use the search method to prepare a query. Search returns a new resultset with the search conditions stored in it, it does not run the query on the database.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the search method on the resultset for the ResultSource you wish to fetch data from:

my $dob_search = $schema->resultset('User')->search(
  { dob => '1910-02-01' }
);

To run the query, use the all or next methods shown at the beginning of this page.

SELECT with different WHERE conditions

Below are shown some common SQL where conditions. The syntax for these is parsed by a module called SQL::Abstract which DBIx::Class uses. They can all be passed to the search method as conditions.

SELECT id, username, dob, realname, password
FROM users
WHERE username LIKE 'fred%';

my $name_search = $schema->resultset('User')->search(
  { username => { '-like' => 'fred%' } }
);

SELECT id, username, dob, realname, password
FROM users
WHERE dob BETWEEN '1910-01-01' AND '1910-12-31';

my $year_dob_search = $schema->resultset('User')->search(
  { dob => { '-between' => ['1910-01-01', '1910-12-31'] } }
);

SELECT id, username, dob, realname, password
FROM users
WHERE dob IN ('1910-02-01', '1910-02-02');

my $feb_dob_search = $schema->resultset('User')->search(
  { dob => { '-in' => ['1910-02-01', '1910-02-02'] } }
);

SELECT id, username, dob, realname, password
FROM users
WHERE dob >= 1911-01-01;

my $next_year_dob = $schema->resultset('User')->search(
  { dob => { '>=', '1911-01-01' } }
);

SELECT with WHERE condition on JOINed table

SELECT posts.id, created_date, title, post
FROM posts
JOIN users user ON user.id = posts.user_id
WHERE user.username = 'fredbloggs';

The second argument to search is a hashref of attributes to apply to the query. One of them is join, which is used to connect to other tables using the relationships defined in the Result classes.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the search method on the resultset for the ResultSource you wish to fetch data from:

my $freds_posts = $schema->resultset('Post')->search(
  { 'user.username' => 'fredbloggs' },
  { join => 'user' }
);

Note that the string "user", used twice here, refers to the name of the Relationship between the "Post" source and the "User" source. All dealings with related tables are refered to by relationship names, not table names.

To run the query, use the all or next methods show at the beginning of this page.

SELECT with fewer columns

SELECT id, title
FROM posts

There's usually little reason to do this sort of query, as fetching all the data in a row doesn't cost any more time than fetching some of it. Unless of course your source is a View with calculations, or has huge blobs, or.. Okay, you might well want to do this occasionally.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the search method on the resultset for the ResultSource you wish to fetch data from:

my $post_titles = $schema->resultset('Post')->search(
  { },
  { columns => [qw/id title/] }
);

Note that accessors for other columns not fetched will return undef, which is also the perl equivalent of the SQL NULL value. To disambiguate between an undef meaning "this column is set null" and "we never retrieved the value of this column" use "has_column_loaded" in DBIx::Class::Row.

SELECT with aggregates

SELECT COUNT(*)
FROM users;

To find out how many users exist. This simple one can be achieved with a built-in method, count.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the *count* method on the resultset for the [Source] you wish to fetch data from:

my $posts_count = $schema->resultset('Post')->count();

The result is not an object, just a number.

SELECT SUM(amount)
FROM prices;

A rather pointless exercise in summing an entire "amount" column from an imaginary "prices" table. This can be done in several ways, first, the built-in DBIx::Class::ResultSet::Column method, by calling get_column.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the get_column method on the resultset for the ResultSource you wish to fetch data from, then the sum method:

my $sum_prices = $schema->resultset('Price')->get_column('amount')
  ->sum();

The result is just a number.

The alternate way uses the search method and is easier to build further refinements into.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the search method on the resultset for the ResultSource you wish to fetch data from:

my $sum_prices_rs = $schema->resultset('Price')->search(
  { },
  { columns => { sum_amount => { SUM => 'amount'} } },
);

The result is a resultset. To fetch the one-row result, call single or all. The resulting Row object will not contain an accessor for the virtual "sum_amount" column, we'll need to fetch it using the Row method get_column.

print $sum_prices_rs->single->get_column('sum_amount');

SELECT from JOINed tables

SELECT users.id, username, posts.id, posts.title
FROM users
JOIN posts posts ON posts.used_id = users.id

To select data from other tables, use the join attribute to name the table relationships to create a JOIN clause to.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the search method on the resultset of the ResultSource you wish to group data on:

my $posts_count_per_user = $schema->resultset('User')->search(
  { },
  { columns => [ qw/id username posts.id posts.title/ ],
    join => 'posts',
  }
);

Here "posts" refers to the name of the Relationship between the "User" source and the "Post" source.

To retrieve the extra data, call the usual relationship accessor:

while( my $row = $sorted_users->next) {
  print "user/post: ", $row->username;
  print $_->title for $row->posts;
  print "\n";
}

SELECT with GROUP BY

SELECT users.id, username, COUNT(posts.id)
FROM users
JOIN posts posts ON posts.used_id = users.id
GROUP BY users.id, username;

To group your results, use the group_by attribute on a search method. We also use the columns attribute to select and name a subset of columns.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the *search* method on the resultset of the ResultSource you wish to group data on:

my $posts_count_per_user = $schema->resultset('User')->search(
  { },
  { columns => [ qw/id username/, { post_count => { count => 'posts.id' } } ],
    join => 'posts',
    group_by => [qw/id username/],
  }
);

Here "posts" refers to the name of the Relationship between the "User" source and the "Post" source.

The results will contain two columns with the usual accessors, "id" and "username", and one with no accessor, as it is a virtual column.

while( my $row = $posts_count_per_user->next) {
  print "user: ", $row->username, " posts: ", $row->get_column('post_count');
}

Note: Remember to disambiguate the columns when joining two tables with identical column names.

SELECT with HAVING

SELECT users.id, username, dob
FROM users
JOIN posts posts ON posts.used_id = users.id
GROUP BY users.id, username, dob
HAVING count(posts.id) = 1

To add a having clause to your query, use the corresponding having attribute.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the search method on the resultset of the ResultSource you wish to filter data on:

my $filtered_users = $schema->resultset('User')->search(
  { },
  { 'columns' => [ qw/me.id me.username me.dob/ ],
    join => 'posts',
    group_by => [qw/me.id me.username me.dob/],
    having   => [{ 'posts.id' => 1 }],
  }
);

Here "posts" refers to the name of the Relationship between the "User" source and the "Post" source.

The results will be filtered by the HAVING clause.

SELECT with DISTINCT

SELECT DISTINCT(posts.title)
FROM posts

To produce DISTINCT clauses, we need to use a hashref argument to the list of items passed to the columns attribute.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the search method on the resultset of the ResultSource you wish to find distinct rows on:

my $distinct_posts = $schema->resultset('Post')->search(
  { },
  { columns => [{ 'd_title' => { distinct => 'me.title' } }],
  }
);

This can also be achieved by using the ResultSet method get_column. The method returns a ResultSetColumn object based on the given column name argument, which can call SQL aggregate functions based upon the column of that data.

So we can also do this, for single column DISTINCT clauses:

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the get_column method on the resultset of the ResultSource you wish to find distinct rows on:

my $rs_column = $schema->resultset('Post')->get_column('title');

3. Call the func method on the resultset column object and pass it the name of the function to apply:

my $titles = $rs_column->func('distinct');

The result will be an arrayref of the actual values. If a ResultSet object is needed for further refinement, use func_rs instead.

SELECT ... FOR UPDATE

SELECT users.id, users.username, users.dob
FROM users
FOR UPDATE

To fetch data and lock it for updating from other transactions, use the for attribute and pass it the value update. This should be done inside a Transaction.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the search method on the resultset of the ResultSource you wish to lock data on:

my $locked_posts = $schema->resultset('User')->search(
  { },
  { columns => [qw/me.id me.username me.dob/],
    for     => 'update'
  }
);

The resultset and rows will be returned as normal, and can be used to update the rows without worrying about other

SELECT with LIMIT and OFFSET

SELECT users.id, users.username
FROM users
ORDER BY user.dob DESC
LIMIT 10 OFFSET 11;

To reduce the set of rows fetched, use the rows and page attributes. The value of page will default to 1, which means no OFFSET will be applied.

1. Create a Schema object representing the database you are working with:

my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');

2. Call the search method on the resultset of the ResultSource you wish to limit data on:

my $limited_posts = $schema->resultset('User')->search(
  { },
  { columns  => [qw/me.id me.username/],
    order_by => { '-desc' => ['user.dob'] },
    rows     => 10,
    page     => 2,
  }
);

This will return exactly 10 row objects, sorted by descending date of birth of the users, starting at the 11th row of the sorted result.

To install DBIx::Class::Manual::SQLHackers::SELECT, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBIx::Class::Manual::SQLHackers::SELECT

CPAN shell

perl -MCPAN -e shell
install DBIx::Class::Manual::SQLHackers::SELECT

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

SELECTing data

Fetching rows from a query

Fetching column values from a Row object

Simple SELECT, one row via the primary key

Simple SELECT, one row via a unique key

Simple SELECT, with WHERE condition

SELECT with different WHERE conditions

SELECT with WHERE condition on JOINed table

SELECT with fewer columns

SELECT with aggregates

SELECT from JOINed tables

SELECT with GROUP BY

SELECT with HAVING

SELECT with DISTINCT

SELECT ... FOR UPDATE

SELECT with LIMIT and OFFSET

Module Install Instructions