From: "jbiskofski" <jbiskof...@gmail.com>
> What happens when you need to make some really complex queries, with a few
> joins of different types, group by's and aggregates? Is it still possible?
>
Of course it is possible.
The query might look like:
my $users = $schema->resultset('Users')->search({
birthday => {-between => ['1990-01-01', '2000-01-01']},
}, {
join => {clients => 'orders'},
group_by => ['client.name', 'order.type'],
});
And there are possible even more complex queries.
Of course, the code above might not have any meaning because it is just an
example of how it looks, but in this case the link is between the table "user"
and "client" and the table "client" and "order" but there could be much more
tables involved in the relation.
Another advantage of using an ORM is that the same code will work if you change
the database. Of course, it will work without any change if you don't use
reserved words in the new database, but there are modules on CPAN that can help
us find the reserved words for many database types.
If the code is well written and it doesn't use SQL code specific for a database
(because DBIx::Class also allows using SQL if there is no other possibility for
doing it), then you can change very easy MySQL with PostgreSQL or Oracle by
just changing the connect() parameters.
And of course, the schema for the new database need to be generated, but
DBIx::Class::Schema::Loader can generate an Oracle or PostgreSQL schema type
from a MySQL database (for example) and need to be made only few corrections
sometimes.
For example, in MySQL is very simple to select something and limit the results
to 20 by using:
select ... from ... limit 20;
select ... from ... limit 40,20;
But as some of you know, Oracle needs a much complex query for doing the same
thing, however, with DBIx::Class it is the same:
my $users = $schema->resultset("Users")->search({
birthday => {'>=' => '2000-01-01'},
}, {
rows => 20,
page => $page,
order_by => ['last_name', 'first_name'],
});
Where $page contains the page (of 20 results each) of the current page.
And a pager is also offered:
my $pager = $users->pager;
Then you can use (in your templates probably):
$pager->first_page (the first page)
$pager->last_page (the last page)
$pager->previous_page (the previous page)
$pager->next_page (the next page)
... and all the other methods offered by Data::page module.
You can send only the $users variable to the template and you can do there:
[% pager = users.pager %]
Then use:
[% pager.next_page %]
...
You can even create a single template that create a list of links to the page 1
2 3 4 5 6, previous, next, first and last page which displays the links to a
few pages before and after the current page, then use that template in all your
templates under all the tables that need pagination.
You just need to define the pager in each template for the variable that holds
the result set that fills the table:
[% pager = the_name_of_the_variable.pager %]
[ % PROCESS table_navigation.tt %]
The variable "pager" defined this way will be used in the table_navigation.tt
template.
DBIx::Class::Schema::Loader creates a directory for the schema, and in it it
creates a "Result" directory where it stores the result classes which are Perl
modules.
You can also create a ResultSet directory next to Result and place Perl modules
that define the methods you want to use there.
So in your application you will use those methods everywhere. You don't need to
create code like the one I shown you above in every place you need it.
In the Result source classes you can also add methods you want to apply for
each result (for each found row).
For example if you have a table with 2 columns first_name and last_name that
you can access using:
$user->first_name and $user->last_name
You can add a subroutine named just "name" or "full_name" that concatenates the
2 names and you will be able to access it using $user->full_name
even if there is no column "full_name" in the that table.
This might not seem to be a big thing, but it is important if you'll want to
print the full name in a template by using just [% user.full_name %] and it
will be more elegant than to concatenate strings there.
DBIx::Class might be hard to learn, but after you'll learn it it will increase
the productivity very much.
Octavian
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
