SELECT id, username, dob, realname, password
FROM users;
In DBIx::Class queries are represented by ResultSet objects. These are created by calling search on existing resultsets, passing new search conditions. 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:
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
The resultset method returns a ResultSet representing a query with no conditions on the given [Source]:
my $user_resultset = $schema->resultset('User');
Fetch all users as Row objects using the all method:
my @users = $user_resultset->all();
OR, fetch each user as a Row object using next:
while( my $user = $user_resultset->next()) {
}
The Row object represents the results from a single data source table 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.
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.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the find method on the resultset for the [Source] you wish to fetch data from:
my $fred_user = $schema->resultset('User')->find({ id => 1 });
$fred_user is a now Row object.
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 Standard basic table creation in SQL.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the find method on the resultset for the [Source] 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 [Source] 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.
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.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the search method on the resultset for the [Source] 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 show at the beginning of this page.
Below are shown some common SQL where conditions. The syntax for these is parsed by a module called L
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 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.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the search method on the resultset for the [Source] 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 setup 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 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.
find will always pull all the columns for the found row, so use the search method for this.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the search method on the resultset for the [Source] you wish to fetch data from:
my $post_titles = $schema->resultset('Post')->search(
{ },
{ columns => [qw/id title/] }
);
// Check what happens if you now call ->created_date or ->user here and write it up.
SELECT COUNT(*)
FROM users;
To find out how many users exist. This simple one can be achieved with a built-in method, count.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
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 L
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the get_column method on the resultset for the [Source] 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.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the search method on the resultset for the [Source] you wish to fetch data from:
my $sum_prices_rs = $schema->resultset('Price')->search(
{ },
{ select => [ { SUM => 'amount'} ],
as => [ '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 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 select and as attributes to select and name a subset of columns.
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the search method on the resultset of the [Source] you wish to group data on:
my $posts_count_per_user = $schema->resultset('User')->search(
{ },
{ select => [ qw/id username/, { count => 'posts.id' } ],
as => [ qw/id username post_count/ ],
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');
}
NB: Remember to disambiguate the columns when joining two tables with identical column names.
SELECT users.id, username, dob, realname, password, posts.title
FROM users
JOIN posts posts ON posts.used_id = users.id
ORDER BY username, posts.title;
To sort the results, use the order_by attributes on a search method. Content can of course be ordered by columns in the current table, or in a joined table
Create a Schema object representing the database you are working with:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
Call the search method on the resultset of the [Source] you wish to group data on:
my $sorted_users = $schema->resultset('User')->search(
{ },
{ '+select' => [ qw/posts.id posts.title/ ],
'+as' => [ qw/posts.id posts.title/ ],
join => 'posts',
order_by => [qw/username posts.title/],
}
);
Here "posts" refers to the name of the relationship between the "User" source and the "Post" source.
The results will be ordered by username, then post title, ready for outputting.
Note how we have added the title of each post, this prevents us having to fire off a second query to fetch the post data to output it. The +select attribute specifies an extended set of columns to fetch, in addition to the columns of the main query table.
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";
}
Last modified: 2012-02-06T12:18:26