# NAME Teng::Plugin::SearchBySQLAbstractMore - use [SQL::Abstract::More](http://search.cpan.org/perldoc?SQL::Abstract::More) as Query Builder for Teng # SYNOPSIS package MyApp::DB; use parent qw/Teng/; __PACKAGE__->load_plugin('SearchBySQLAbstractMore'); __PACAKGE__->sql_abstract_more_new_option(sql_dialect => 'Oracle'); # If you want to pass SQL::Abstract::More new options package main; my $db = MyApp::DB->new(dbh => $dbh); my $itr = $db->search_by_sql_abstract_more('user' => {type => 3}); my @rows = $db->search_by_sql_abstract_more('user' => {type => 3}, {rows => 5}); # use pager my $page = $c->req->param('page') || 1; my ($rows, $pager) = $db->search_by_sql_abstract_more_with_pager('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'); # search_with_pager from SearchBySQLAbstractMore::Pager::Count __PACKAGE__->install_sql_abstract_more(pager => 'Pager::Count'); 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 table 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, }, ); Generate SQL by SQLAbstractMore ($sql, @binds) = $teng->create_sql_by_sql_abstract_more($table, $where, $opt); It returns SQL and bind values with same args of `search_bys_sql_abstract_more` method. # METHODS ## search\_by\_sql\_abstract\_more see SYNOPSIS. ## create\_sql\_by\_sql\_abstract\_more ($sql, @binds) = $teng->create_sql_by_sql_abstract_more($table, $where, $opt); This method returns SQL statement and its bind values. It doesn't check table is in schema. # CLASS METHOD ## sql\_abstract\_more\_instance YourClass->sql_abstract_more_instance; return SQL::Abstract::More object. ## sql\_abstract\_more\_new\_option YourClass->sql_abstract_more_new_option(sql_dialect => 'Oracle'); This method's arguments are passed to SQL::Abstract::More->new(). see [SQL::Abstract::More](http://search.cpan.org/perldoc?SQL::Abstract::More) new options. ## replace\_teng\_search If you want to replace `search` method of original Teng, call this. Teng::Plugin::SearchBySQLAbstractMore->replace_teng_search; It is useful when you wrap `search` method in your module and call Teng's `search` method in it and you want to use same usage with SQL::Abstract::More. ## install\_sql\_abstract\_more package YourClass; use Teng::Plugin::SearchBySQLAbstract::More; # no need to use ->load_plugin(); YourClass->install_sql_abstract_more; # search_sql_abstract_more is defined as YourClass::search YourClass->install_sql_abstract_more(alias => 1); # same as the above YourClass->install_sql_abstract_more(replace => 1); # Teng::Search is replaced by search_sql_abstract_more YourClass->install_sql_abstract_more(alias => 'complex_search'); # sql_abstract_more is defined as YourClass::complex_search YourClass->install_sql_abstract_more(alias => 'complex_search', pager => 1); # sql_abstract_more is defined as YourClass::complex_search # sql_abstract_more_pager is defined as YourClass::complex_search_with_pager YourClass->install_sql_abstract_more(alias => 'complex_search', pager => 1, pager_alias => 'complex_search_paged'); # sql_abstract_more is defined as YourClass::complex_search # sql_abstract_more_pager is defined as YourClass::complex_search_paged # use different pager YourClass->install_sql_abstract_more(pager => 1); # or pager => 'simple' / 'Pager' YourClass->install_sql_abstract_more(pager => 'mysql_found_rows'); # or pager => 'Pager::MySQL::FoundRows' YourClass->install_sql_abstract_more(pager => 'count'); # or pager => 'Pager::Count' It call replace\_teng\_search if replace option is passed and it is true and loads pager plugin with alias option if pager option is true. `search` and `search_with_pager` are installed to your class. This method can take the following options. ### replace If you want to replace Teng's search method, pass this option. YourClass->install_sql_abstract_more(replace => 1); ### alias YourClass->install_sql_abstract_more(alias => 'complex_search'); YourClass->install_sql_abstract_more(pager => 'Pager', alias => 'complex_search'); This is equals to: YourClass->load_plugin('Teng::Plugin::SearchBySQLAbstractMore', { alias => 'search_by_sql_abstract_more' => 'complex_search', }); YourClass->load_plugin('Teng::Plugin::SearchBySQLAbstractMore::Pager', { alias => 'search_by_sql_abstract_more_with_pager' => 'complex_search_with_pager', }); ### pager\_alias If you want to use different alias for pager search. YourClass->install_sql_abstract_more(pager => 'Pager', pager_alias => 'complex_search_with_pager'); This is equals to: YourClass->load_plugin('Teng::Plugin::SearchBySQLAbstractMore', { alias => 'search_by_sql_abstract_more' => 'search', }); YourClass->load_plugin('Teng::Plugin::SearchBySQLAbstractMore::Pager', { alias => 'search_by_sql_abstract_more_with_pager' => 'complex_search_with_pager', }); ### pager Pass pager plugin name or 1. YourClass->install_sql_abstract_more(pager => 1); # load SearchBySQLAbstractMore::Pager YourClass->install_sql_abstract_more(pager => 'Pager'); # same as the above YourClass->install_sql_abstract_more(pager => 'simple'); # same as the above YourClass->install_sql_abstract_more(pager => 'Pager::MySQLFoundRows');# load SearchBySQLAbstractMore::Pager::MySQLFoundRows YourClass->install_sql_abstract_more(pager => 'mysql_found_rows'); # same as the above YourClass->install_sql_abstract_more(pager => 'Pager::Count'); # load SearchBySQLAbstractMore::Pager::Count YourClass->install_sql_abstract_more(pager => 'count'); # same as the above # AUTHOR Ktat, `<ktat at cpan.org>` # BUGS Please report any bugs or feature requests to `bug-teng-plugin-searchbysqlabstractmore at rt.cpan.org`, or through the web interface at [http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Teng-Plugin-SearchBySQLAbstractMore](http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Teng-Plugin-SearchBySQLAbstractMore). I will be notified, and then you'll automatically be notified of progress on your bug as I make changes. # SUPPORT You can find documentation for this module with the perldoc command. perldoc Teng::Plugin::SearchBySQLAbstractMore You can also look for information at: - RT: CPAN's request tracker [http://rt.cpan.org/NoAuth/Bugs.html?Dist=Teng-Plugin-SearchBySQLAbstractMore](http://rt.cpan.org/NoAuth/Bugs.html?Dist=Teng-Plugin-SearchBySQLAbstractMore) - AnnoCPAN: Annotated CPAN documentation [http://annocpan.org/dist/Teng-Plugin-SearchBySQLAbstractMore](http://annocpan.org/dist/Teng-Plugin-SearchBySQLAbstractMore) - CPAN Ratings [http://cpanratings.perl.org/d/Teng-Plugin-SearchBySQLAbstractMore](http://cpanratings.perl.org/d/Teng-Plugin-SearchBySQLAbstractMore) - Search CPAN [http://search.cpan.org/dist/Teng-Plugin-SearchBySQLAbstractMore/](http://search.cpan.org/dist/Teng-Plugin-SearchBySQLAbstractMore/) # SEE ALSO - [Teng](http://search.cpan.org/perldoc?Teng) - [SQL::Abstract::More](http://search.cpan.org/perldoc?SQL::Abstract::More) # ACKNOWLEDGEMENTS # LICENSE AND COPYRIGHT Copyright 2012 Ktat. This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License. See http://dev.perl.org/licenses/ for more information.