[Dbix-class] Cookbook patch for paging and use of "from" attribute

[Matt S Trout]

Paging part looks good; the from stuff ... ok, but I'm pretty sure you
could do the same query using join => and appropriate relationships (and
I think you want belongs_to, not has_one) - maybe use join for some simpler
examples and then from for just the more insane ones?

On Thu, Jan 19, 2006 at 05:35:22PM +0000, Will Hawes wrote:
> Please take a few minutes to review. Error reports/improvements welcome.
> 
> Index: Cookbook.pod
> ===================================================================
> --- Cookbook.pod	(revision 508)
> +++ Cookbook.pod	(working copy)
> @@ -4,6 +4,37 @@
> 
>  =head1 RECIPES
> 
> +=head2 Paged results
> +
> +When you expect a large number of results, you can ask DBIx::Class for 
> a paged
> +resultset, which will fetch only a small number of records at a time:
> +
> +    $rs = $schema->resultset('Artist')->search(
> +        {},
> +        {
> +            page => 1, # page to return (defaults to 1)
> +            rows => 10, # number of results per page
> +        },
> +    );
> +
> +    $rs->all(); # return all records for page 1
> +
> +The "page" attribute does not have to be specified in your search:
> +
> +    $rs = $schema->resultset('Artist')->search(
> +        {},
> +        {
> +            rows => 10,
> +        }
> +    );
> +
> +    $rs->page(1); # return DBIx::Class::ResultSet containing first 10 
> records
> +
> +In either of the above cases, you can return a L<Data::Page> object for the
> +resultset (suitable for use in a TT template etc) using the pager() method:
> +
> +    $pager = $rs->pager();
> +
>  =head2 Complex searches
> 
>  Sometimes you need to formulate a query using specific operators:
> @@ -201,6 +232,115 @@
>      # WHERE liner_notes.notes LIKE '%some text%'
>      # AND author.name = 'A. Writer'
> 
> +=head2 Using "from"
> +
> +The "from" attribute gives you manual control over the "FROM" clause of SQL
> +statements generated by DBIx::Class, allowing you to express custom "JOIN"
> +clauses.
> +
> +The following examples utilize a "person" table in a family tree 
> application.
> +In order to express parent->child relationships, this table is self-joined:
> +
> +    # Person->has_one('father' => 'Person');
> +    # Person->has_one('mother' => 'Person');
> +
> +In simple terms, "from" works as follows:
> +
> +    [
> +        { <alias> => <table>, -join-type => 'inner|left|right' }
> +        [] # nested JOIN (optional)
> +        { <table.column> = <foreign_table.foreign_key> }
> +    ]
> +
> +    JOIN
> +        <alias> <table>
> +        [JOIN ...]
> +    ON <table.column> = <foreign_table.foreign_key>
> +
> +An easy way to follow the examples below is to remember the following:
> +
> +    Anything inside "[]" is a JOIN
> +    Anything inside "{}" is a condition for the enclosing JOIN
> +
> +You can use "from" to cascade joins. So we can include columns from the 
> mother
> +and father of a person in our search:
> +
> +    $rs = $schema->resultset('Person')->search(
> +        {},
> +        {
> +            alias => 'child', # alias columns in accordance with "from"
> +            from => [
> +                { child => 'person' },
> +                [
> +                    { father => 'person' },
> +                    { 'father.id' => 'child.father_id' }
> +                ],
> +                [
> +                    { mother => 'person' },
> +                    { 'mother.id' => 'child.mother_id' }
> +                ]
> +            ]
> +        }
> +    );
> +
> +    # equivalent SQL:
> +    # SELECT child.* FROM person child
> +    # JOIN person father ON father.id = child.father_id
> +    # JOIN person mother ON mother.id = child.mother_id
> +
> +Joins can be nested. Here we return all children with a father, then
> +search against all mothers of those children:
> +
> +    $rs = $schema->resultset('Person')->search(
> +        {},
> +        {
> +            alias => 'mother', # alias columns in accordance with "from"
> +            from => [
> +                { mother => 'person' },
> +                [
> +                    [
> +                        { child => 'person' },
> +                        [
> +                            { father => 'person' },
> +                            { 'father.person_id' => 'child.father_id' }
> +                        ]
> +                    ],
> +                    { 'mother.person_id' => 'child.mother_id' }
> +                ],
> +            ]
> +        },
> +    );
> +
> +    # equivalent SQL:
> +    # SELECT mother.* FROM person mother
> +    # JOIN (
> +    #   person child
> +    #   JOIN person father
> +    #   ON ( father.person_id = child.father_id )
> +    # )
> +    # ON ( mother.person_id = child.mother_id )
> +
> +The type of any join can be controlled manually. To search against only 
> people
> +with a father in the person table, we could explicitly use INNER JOIN:
> +
> +    $rs = $schema->resultset('Person')->search(
> +        {},
> +        {
> +            alias => 'child', # alias columns in accordance with "from"
> +            from => [
> +                { child => 'person' },
> +                [
> +                    { father => 'person', -join-type => 'inner' },
> +                    { 'father.id' => 'child.father_id' }
> +                ],
> +            ]
> +        },
> +    );
> +
> +    # equivalent SQL:
> +    # SELECT child.* FROM person child
> +    # INNER JOIN person father ON child.father_id = father.id
> +
>  =head2 Transactions
> 
>  As of version 0.04001, there is improved transaction support in
> 
> _______________________________________________
> List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +