SELECT id, username, dob, realname, password
FROM users;
-In DBIx::Class queries are represented by ResultSet objects. These are created by calling B<search> on existing resultsets, passing new search conditions. A query is not run against the database until data is explicitly requested.
+In DBIx::Class queries (or more specifically query plans) are represented by ResultSet objects. These are created by calling B<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:
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
-=item 2. The B<resultset> method returns a ResultSet representing a query with no conditions on the given B<Source>:
+=item 2. The B<resultset> method returns a ResultSet representing a query retrieving all columns of the given B<ResultSource> without conditions:
my $user_resultset = $schema->resultset('User');
=head2 Fetching column values from a Row object
-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 L<Result Class|DBIx::Class::ResulSource> if needed).
+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 L<Result Class|DBIx::Class::ResulSource> if needed).
print $user->username;
-See the [DBIx::Class::Row]() documentation for more things you can do
+See the L<DBIx::Class::Row> documentation for more things you can do
with Row objects.
=head2 Simple SELECT, one row via the primary key
FROM users
WHERE username = 'fredbloggs';
-B<find> also works well on unique constraints, for example the username of our user. Unique constraints can be defined on Result classes using B<add_unique_constraint> (See L<CREATE|DBIx::Class::Manual::SQLHackers::CREATE>.
+B<find> also works well on unique constraints, for example the username of our user. Unique constraints can be defined on Result classes using B<add_unique_constraint> (See L<CREATE|DBIx::Class::Manual::SQLHackers::CREATE>).
=over
=back
-To run the query, use the B<all> or B<next> methods show at the beginning of this page.
+To run the query, use the B<all> or B<next> methods shown at the beginning of this page.
=head2 SELECT with different WHERE conditions
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.
-B<find> will always pull all the columns for the found row, so use the *search* method for this.
+B<find> will always pull all the columns for the found row, so use the B<search> method for this.
=over
=back
-Note that accessors for other columns not fetched will return B<undef>. To discover whether a columns data has been loaded or not, use the B<has_column_loaded> method.
+Note that accessors for other columns not fetched will return B<undef>, which is also the perl equivalent of the SQL C<NULL> value. To disambiguate between an C<undef> meaning "this column is set null" and "we never retrieved the value of this column" use L<DBIx::Class::Row/has_column_loaded>.
=head2 SELECT with aggregates
my $sum_prices_rs = $schema->resultset('Price')->search(
{ },
- { select => [ { SUM => 'amount'} ],
- as => [ 'sum_amount' ] }
+ { columns => { sum_amount => { SUM => 'amount'} } },
);
=back
my $posts_count_per_user = $schema->resultset('User')->search(
{ },
- { select => [ qw/id username posts.id posts.title/ ],
- as => [ qw/id username posts.id posts.title/ ],
+ { columns => [ qw/id username posts.id posts.title/ ],
join => 'posts',
}
);
JOIN posts posts ON posts.used_id = users.id
GROUP BY users.id, username;
-To group your results, use the B<group_by> attribute on a B<search> method. We also use the B<select> and B<as> attributes to select and name a subset of columns.
+To group your results, use the B<group_by> attribute on a B<search> method. We also use the B<columns> attribute to select and name a subset of columns.
=over
my $posts_count_per_user = $schema->resultset('User')->search(
{ },
- { select => [ qw/id username/, { count => 'posts.id' } ],
- as => [ qw/id username post_count/ ],
+ { columns => [ qw/id username/, { post_count => { count => 'posts.id' } } ],
join => 'posts',
group_by => [qw/id username/],
}
Note: Remember to disambiguate the columns when joining two tables with identical column names.
-=head2 SELECT with simple ORDER BY
+=begin comment
- 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;
+Commented out section as ordering by a related source does not work yet. Fix in progress, will comment back in when DBIC is updated.
-To sort the results, use the B<order_by> attributes on a B<search> method. Content can of course be ordered by columns in the current table, or in a joined table
+ =head2 SELECT with simple ORDER BY
-=over
+ 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;
-=item 1. Create a Schema object representing the database you are working with:
+ To sort the results, use the B<order_by> attributes on a B<search> method. Content can of course be ordered by columns in the current table, or in a joined table
- my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
+ =over
-=item 2. Call the B<search> method on the resultset of the L<ResultSource|DBIx::Class::ResultSource> you wish to sort data on:
+ =item 1. Create a Schema object representing the database you are working with:
- 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/],
- }
- );
+ my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
-=back
+ =item 2. Call the B<search> method on the resultset of the L<ResultSource|DBIx::Class::ResultSource> you wish to sort data on:
-Here "posts" refers to the name of the L<Relationship|DBIx::Class::Relationship> between the "User" source and the "Post" source.
+ my $sorted_users = $schema->resultset('User')->search(
+ { },
+ { '+columns' => [ qw/posts.id posts.title/ ],
+ join => 'posts',
+ order_by => [qw/username posts.title/],
+ }
+ );
-The results will be ordered by username, then post title, ready for outputting.
+ =back
-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 B<+select> attribute specifies an extended set of columns to fetch, in addition to the columns of the main query table.
+ Here "posts" refers to the name of the L<Relationship|DBIx::Class::Relationship> between the "User" source and the "Post" source.
-To retrieve the extra data, call the usual relationship accessor:
+ The results will be ordered by username, then post title, ready for outputting.
- while( my $row = $sorted_users->next) {
- print "user/post: ", $row->username;
- print $_->title for $row->posts;
- print "\n";
- }
+ 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 B<+columns> 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";
+ }
+
+=end comment
=head2 SELECT with HAVING
SELECT users.id, username, dob
FROM users
JOIN posts posts ON posts.used_id = users.id
- GROUP BY users.id, username;
+ GROUP BY users.id, username, dob
HAVING count(posts.id) = 1
To add a B<having> clause to your query, use the corresponding B<having> attribute.
my $filtered_users = $schema->resultset('User')->search(
{ },
- { '+select' => [ qw/me.id me.username me.dob/ ],
- '+as' => [ qw/me.id me.username me.dob/ ],
+ { 'columns' => [ qw/me.id me.username me.dob/ ],
join => 'posts',
- group_by => [qw/me.id me.username/],
+ group_by => [qw/me.id me.username me.dob/],
having => [{ 'posts.id' => 1 }],
}
);
The results will be filtered by the HAVING clause.
=head2 SELECT with DISTINCT
+^^ you may or may not want to mention the distinct => 1 flag, which is an automatic "group by the selection" thing.
SELECT DISTINCT(posts.title)
FROM posts
-DBIx::Class doesn't currently produce a B<DISTINCT> keyword, but will output a B<GROUP BY> to simulate the same effect.
+To produce DISTINCT clauses, we need to use a hashref argument to the list of items passed to the B<columns> attribute.
=over
my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
-=item 2. Call the B<search> method on the resultset of the L<ResultSource|DBIx::Class::ResultSource> you wish to group data on:
+=item 2. Call the B<search> method on the resultset of the L<ResultSource|DBIx::Class::ResultSource> you wish to find distinct rows on:
my $distinct_posts = $schema->resultset('Post')->search(
{ },
- { columns => ['me.title'],
- distinct => 1,
+ { columns => [{ 'd_title' => { distinct => 'me.title' } }],
}
);
=back
-This actually outputs:
+This can also be achieved by using the ResultSet method B<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.
- SELECT posts.title
- FROM posts
- GROUP BY posts.title
+So we can also do this, for single column DISTINCT clauses:
+
+=over
+
+=item 1. Create a Schema object representing the database you are working with:
+
+ my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db');
+
+=item 2. Call the B<get_column> method on the resultset of the L<ResultSource|DBIx::Class::ResultSource> you wish to find distinct rows on:
+
+ my $rs_column = $schema->resultset('Post')->get_column('title');
+
+=item 3. Call the B<func> method on the resultset column object and pass it the name of the function to apply:
+
+ my $titles = $rs_column->func('distinct');
+
+=back
+
+The result will be an arrayref of the actual values. If a ResultSet object is needed for further refinement, use B<func_rs> instead.
=head2 SELECT ... FOR UPDATE
The resultset and rows will be returned as normal, and can be used to update the rows without worrying about other
-=head2 SELECT with LIMIT
+=head2 SELECT with LIMIT and OFFSET
SELECT users.id, users.username
FROM users
ORDER BY user.dob DESC
- LIMIT 10;
+ LIMIT 10 OFFSET 11;
-To reduce the set or rows fetched, use the B<rows> attribute.
+To reduce the set of rows fetched, use the B<rows> and B<page> attributes. The value of B<page> will default to 1, which means no OFFSET will be applied.
=over
{ 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.
+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.
=back
projects / dbsrgits/DBIx-Class-Manual-SQLHackers.git / blobdiff