Teng::Plugin::SearchBySQLAbstractMore 0.08

Teng::Plugin::SearchBySQLAbstractMore is a Perl module to use SQL::AbstractMore as Query Builder for Teng

SYNOPSIS

  package MyApp::DB;
  use parent qw/Teng/;
  __PACKAGE__->load_plugin('SearchBySQLAbstractMore');

  package main;
  my $db = MyApp::DB->new(dbh => $dbh);
  my $page = $c->req->param('page') || 1;
  my ($rows, $pager) = $db->search_by_sql_abstract_more('user' => {type => 3}, {page => $page, rows => 5});

If you want to replace Teng search

  package MyApp::DB;
  use parent qw/Teng/;
  __PACKAGE__->load_plugin('SearchBySQLAbstractMore');
  __PACKAGE__->install_sql_abstract_more;
  # now, search method is replaced by search_by_sql_abstract_more

If you want to load pager at the same time

  # search_with_pager from SearchBySQLAbstractMore::Pager
  __PACKAGE__->install_sql_abstract_more(pager => 'Pager');
  # search_with_pager from SearchBySQLAbstractMore::Pager::MySQLFoundRows
  __PACKAGE__->install_sql_abstract_more(pager => 'Pager::MySQLFoundRows');

Create complex SQL using SQL::Abstract::More.

Compatible usage with Teng's search method.

 $teng->search_by_sql_abstract_more
    ('table1',
     { name => { like => '%perl%'},
       -and => [
                {x => 1},
                {y => [-or => {'>' => 2}, {'< ' = > 10}]},
               ],
     },
     {
      from     => ['-join',
                   'table1|t1',
                   't1.id=t2.id',
                   'table2|t2',
                   't1.id=t3.table1_id,t2.id=t3.table2_id',
                   'table3|t3',
                  ],
      columns  => ['x', 'y', 'min(age) as min_age', 'max(age) as max_age'],
      group_by => ['x', 'y'],
      having   => {'max_age' => {'< ' = > 10}},
     },
    );
 # SELECT x, y, min(age) as min_age, max(age) as max_age
 #   FROM table1 AS t1
 #     INNER JOIN table2 AS t2 ON ( t1.id = t2.id )
 #     INNER JOIN table3 AS t3 ON ( ( t1.id = t3.table1_id AND t2.id = t3.table2_id ) )
 #   WHERE ( ( ( x = ? AND ( y > ? OR y < ? ) ) AND name LIKE ? ) )
 #   GROUP BY x, y  HAVING ( max_age < ? );

SQL::Abstract::More original usage(as first argument, use hash ref instead of teble name):

 $teng->search_by_sql_abstract_more(
   {
     -columns  => ['x', 'y', 'min(age) as min_age', 'max(age) as max_age'],
     -from     => [-join,
                   'table1|t1',
                   't1.id=t2.id',
                   'table2|t2',
                   't1.id=t3.table1_id,t2.id=t3.table2_id',
                   'table3|t3',
                 ],
     -group_by => ['x', 'y'],
     -having   => {'max_age' => {'< ' = > 10'}},
     -where => {
        name => { like => '%perl%'},
        -and => [
            {x => 1},
            {y => [-or => {'>' => 2}, {'< ' = > 10}]},
        ],
      },
   },
 );
 # SQL is as same as the avobe code.

Using pager.

Compatible usage:

 $teng->search_by_sql_abstract_more(
   'table', {
     name => 1,
     age  => 10,
   },
   {
     -columns  => ['x', 'y'],
     -from     => ['table'],
     -page     => 2,
     -rows     => 20,
   },
 );

Originaly usage:

 $teng->search_by_sql_abstract_more(
   {
     -columns  => ['x', 'y'],
     -from     => ['table'],
     -where    => {
          name => 1,
          age  => 10,
     },
     -page     => 2,
     -rows     => 20,
   },
 );


Product's homepage

Requirements:

· Perl