[Rt-devel] PATCH DBIx::SearchBuilder
Jesse Vincent
jesse at bestpractical.com
Tue Aug 15 22:48:23 EDT 2006
On Tue, Aug 15, 2006 at 10:37:50PM -0400, Todd Chapman wrote:
> This patch for DBIx::SB allows you to do a LEFT JOIN with
> an EXPRESSION _and_ allow you to refer to the alias that
> is created in the expression. ( SB requires a TABLE2
> argument when doing a LEFT join. )
>
> Tests added. All tests pass.
It looks like you've added a new API. If so, you probably need to add
docs for it ;)
(I'd take the cleanup refactoring on its own ;)
>
> -Todd
> ==== Patch <left_join_expression> level 1
> Source: 6fc35107-3207-0410-b21e-9a2d7f624572:/local/bp/SB/left_join_with_expression:8477
> Target: e417ac7c-1bcc-0310-8ffa-8f5827389a85:/DBIx-SearchBuilder/trunk:5729
> (svn://svn.bestpractical.com)
> Log:
> r8402 at slah001: root | 2006-08-14 17:18:41 -0400
> Creating a patch so that left joins can refer to the alias
> created for TABLE2.
> r8474 at slah001: root | 2006-08-15 21:30:58 -0400
> mysql syntax fix
> r8475 at slah001: root | 2006-08-15 21:32:08 -0400
> Added functionality so you can refer to the generated alias
> in the EXPRESSION of a left join. Added tests for Join
> with EXPRESSION.
> r8476 at slah001: root | 2006-08-15 21:51:46 -0400
> Eliminate temp variables _and_ make the code easier to read.
> r8477 at slah001: root | 2006-08-15 22:19:19 -0400
> LEFT JOIN is broken, not natural join.
>
> === t/02searches_joins.t
> ==================================================================
> --- t/02searches_joins.t (revision 5729)
> +++ t/02searches_joins.t (patch left_join_expression level 1)
> @@ -7,7 +7,7 @@
> BEGIN { require "t/utils.pl" }
> our (@AvailableDrivers);
>
> -use constant TESTS_PER_DRIVER => 17;
> +use constant TESTS_PER_DRIVER => 24;
>
> my $total = scalar(@AvailableDrivers) * TESTS_PER_DRIVER;
> plan tests => $total;
> @@ -62,9 +62,33 @@
> is( $users_obj->Count, 1, "user is not member of any group" );
> is( $users_obj->First->id, 3, "correct user id" );
>
> - # JOIN via existan alias
> + # LEFT JOIN with EXPRESSION
> $users_obj->CleanSlate;
> ok( !$users_obj->_isJoined, "new object isn't joined");
> + $alias = $users_obj->Join( TYPE => 'LEFT',
> + EXPRESSION => 'main.id',
> + TABLE2 => 'UsersToGroups',
> + FIELD2 => 'UserId' );
> + ok( $alias, "Join with EXPRESSION returns alias" );
> + $users_obj->Limit( ALIAS => $alias, FIELD => 'id', OPERATOR => 'IS', VALUE => 'NULL' );
> + is( $users_obj->Count, 1, "user is not member of any group" );
> + is( $users_obj->First->id, 3, "correct user id" );
> +
> + # LEFT JOIN with EXPRESSION referring to TABLE2
> + $users_obj->CleanSlate;
> + ok( !$users_obj->_isJoined, "new object isn't joined");
> + $alias = $users_obj->Join( TYPE => 'LEFT',
> + EXPRESSION => 'main.id AND __TABLE2ALIAS__.Type = \'primary\'',
> + TABLE2 => 'UsersToGroups',
> + FIELD2 => 'UserId' );
> + ok( $alias, "Join with EXPRESSION returns alias" );
> + $users_obj->Limit( ALIAS => $alias, FIELD => 'GroupId', OPERATOR => 'IS', VALUE => 'NULL' );
> + is( $users_obj->Count, 2, "correct number of members not in primary group" );
> + #is( $users_obj->First->id, 3, "correct user id" );
> +
> + # JOIN via existing alias
> + $users_obj->CleanSlate;
> + ok( !$users_obj->_isJoined, "new object isn't joined");
> $alias = $users_obj->NewAlias( 'UsersToGroups' );
> ok( $alias, "new alias" );
> ok($users_obj->Join( TYPE => 'LEFT',
> @@ -75,7 +99,7 @@
> );
> $users_obj->Limit( ALIAS => $alias, FIELD => 'id', OPERATOR => 'IS', VALUE => 'NULL' );
> TODO: {
> - local $TODO = "JOIN with ALIAS2 is broken";
> + local $TODO = "LEFT JOIN with ALIAS2 is broken";
> is( $users_obj->Count, 1, "user is not member of any group" );
> }
>
> @@ -99,7 +123,8 @@
> CREATE TABLE UsersToGroups (
> id integer primary key,
> UserId integer,
> - GroupId integer
> + GroupId integer,
> + Type varchar(36)
> ) },
> q{
> CREATE TABLE Groups (
> @@ -120,7 +145,8 @@
> CREATE TEMPORARY TABLE UsersToGroups (
> id integer primary key AUTO_INCREMENT,
> UserId integer,
> - GroupId integer
> + GroupId integer,
> + Type varchar(36)
> ) },
> q{
> CREATE TEMPORARY TABLE Groups (
> @@ -141,7 +167,8 @@
> CREATE TEMPORARY TABLE UsersToGroups (
> id serial primary key,
> UserId integer,
> - GroupId integer
> + GroupId integer,
> + Type varchar(36)
> ) },
> q{
> CREATE TEMPORARY TABLE Groups (
> @@ -272,20 +299,22 @@
> {read => 1, type => 'int(11)'},
> GroupId =>
> {read => 1, type => 'int(11)'},
> + Type =>
> + {read => 1, write => 1, type => 'varchar(36)'},
> }
> }
>
> sub init_data {
> return (
> - [ 'GroupId', 'UserId' ],
> + [ 'GroupId', 'UserId', 'Type' ],
> # dev group
> - [ 1, 1 ],
> - [ 1, 2 ],
> - [ 1, 4 ],
> + [ 1, 1, 'primary' ],
> + [ 1, 2, 'secondary' ],
> + [ 1, 4, 'primary' ],
> # sales
> # [ 2, 0 ],
> # support
> - [ 3, 1 ],
> + [ 3, 1, 'primary' ],
> );
> }
>
> === t/03transactions.t
> ==================================================================
> --- t/03transactions.t (revision 5729)
> +++ t/03transactions.t (patch left_join_expression level 1)
> @@ -145,7 +145,7 @@
> Name varchar(36),
> Phone varchar(18),
> EmployeeId int(8),
> - PRIMARY KEY (id)) TYPE='InnoDB'
> + PRIMARY KEY (id)) TYPE=InnoDB
> EOF
>
> }
> === SearchBuilder/Handle.pm
> ==================================================================
> --- SearchBuilder/Handle.pm (revision 5729)
> +++ SearchBuilder/Handle.pm (patch left_join_expression level 1)
> @@ -911,12 +911,8 @@
> unless ($alias) {
>
> # if we can't do that, can we reverse the join and have it work?
> - my $a1 = $args{'ALIAS1'};
> - my $f1 = $args{'FIELD1'};
> - $args{'ALIAS1'} = $args{'ALIAS2'};
> - $args{'FIELD1'} = $args{'FIELD2'};
> - $args{'ALIAS2'} = $a1;
> - $args{'FIELD2'} = $f1;
> + ( $args{'FIELD1'}, $args{'FIELD2'} ) = ( $args{'FIELD2'}, $args{'FIELD1'} );
> + ( $args{'ALIAS1'}, $args{'ALIAS2'} ) = ( $args{'ALIAS2'}, $args{'ALIAS1'} );
>
> @aliases = @{ $args{'SearchBuilder'}->{'aliases'} };
> @new_aliases = ();
> @@ -959,6 +955,7 @@
> my $criterion;
> if ($args{'EXPRESSION'}) {
> $criterion = $args{'EXPRESSION'};
> + $criterion =~ s/__TABLE2ALIAS__/$alias/g;
> } else {
> $criterion = $args{'ALIAS1'}.".".$args{'FIELD1'};
> }
>
> ==== BEGIN SVK PATCH BLOCK ====
> Version: svk 1.07 (linux)
>
> eJyNVl1vG0UUXR5Ail+AB4RQhTSQRU4lO96Znf1yVZOkdYpLlJYmKUgFWbOzs8nS9a6ZHYdY3aA6
> EvBUigAJhHgBXpAKiHfEP4D/xMyskzpu+rGyVuuZe889987MubPOty+swLLTsUoTWuXWzXfb7etE
> 0L23IC5Nt2RRInJuOmXK9llq2mWa75q4zMiAydkiH3GqPgThu0yoj4TeZqLTgRLOr+C6GuIYVqOG
> RORZYQYavi84YyYs3RWvXLHVr2/CoCyYnNGwfc72kyLJMxnYx54nLaQ5lO75kGV9nudCzjie56wg
> 5WuVNM0L1lfgCtBR5siUCWn7KOGMSkZjOSo00NRZ2zln2dnlFiOc7q2NkjRifNZJs3WmEWeh4Ulo
> +5hpnKTMRFYpWhYqNCAr+h/nSVYsC01xlgg+5SWLKb1swUlWECpkMeZ8HssDyaJIRD1LhsN03Bfs
> QEQsFURzs1HpIzuwI0ZlbX3GPEwc5HiuHYRhoD4CM7BlBW5uGsZf7/0Nb3xxxZicM46M+4PJ6z8Y
> k5fuge+M97+6cN+YjO+//IcxefHeG/8YO19dvHuEjeTeG8bRyjcLnxtHh9/1vjWO3v5+Xb7xj89/
> a3yd/RTcPVo2jl79s43wRnd9G1y91tsEnyZiD3Q/uH6ju7XVu3bysQkudkB9QJJs+bTF5uNcNwFn
> MeM8yXaByMH26tpGF52Fl0RgdfMy6Pcrk9WN3upWv7+8PR4ycBF8WB/yZED4+MP6o/hixLMCkDQh
> xZvg/IUamD7mqGC86Ofhx83ORjJIxBLQsCqoqc0bYL3X3bisWVzh+WjYi+oNcO1698bq9rUberi3
> JUdurm7sdPXfzZ2NjfpskKRYOhXoUj7KRAOgBniT5lztAZCNBiHjII/BgKmvAmS5AEkGpjmBXRX6
> FPXFedj1hBei2UmiBrBnoJUFSCLtW1tYrFZgPyGAHSSFUEXXeerVadQWdDX31a4nfMl2zz/bkCJU
> rUPnhOAdzkikKgIb4FOeCDb9FpUdqM/41w8bQDse1g5rtWIUytyl5kREEHBHT1RrCJZqC7dmFmKh
> viPT00tSV/Hr4KNGbRFEbL+qmLKGjYUFGXb6HO8SbTmdRQ9nC0bzLJqbx2d7L4KCpKyoLUozJM2s
> 6eBoOMy5UM722aFX8KwAVspRnfHYiiDDDqIsYBgHFoXUiZwIY2oFEYH+E1UCK4QwpHYkvaBPrMjC
> lFBIIttxIbF92/UDE3mVSDw49+C12gvGb8j4wjgiv7V7WZZfXjuTmgZ2MYwihiGyI8d1bQf5LAiI
> xZzYDTyf4Iqa6hCnhXROlVvvkCxK2fJwoGX8abroPDFjVxGDrscCauOY4hA51EchRAH2sB9jJ7Q9
> mTGe6uLkv4XJv4evTK4bk1+eM+5OTOPL9ya/0t+Nn29NHizL0yQbWXGnrg88VFtydgDVD8F5qTNL
> 86ONeb/ZU6qeEw8tLbPAeuAR4OPRxrwf0OfMpOowcdlcwMXPQNGa08NWpVut3TOXUlcsiDF2kRfS
> KPSR71MXur4b2R6OiQPj0NKd0/HdEnAfW2ilSMmeZcG2PIWyi4MSIMtym5bfhBhArw39NoagaWHL
> qoFL8tRXqgKG6ioBihyIPSJAymIBdBsFlGSV5iu9F3usUqAaoMqXRSDO+bQNLNcUBQ8/gYIDEGzb
> VtvxjykMxsUnKSjGmSAHIE4OKgznqRiobZ1grMq9LomMMt3FJT8xVpmM89Gj7HdZxrgmPs1D6rYa
> n+lAUtjJwwosT+EFK0Shs70qR2vzfXGavPs04g5sY/eYeDeVbSyTbCT6YKg0OiGhVCnQl+euDwbk
> NtPkaB4xwEiRVHkorZ7G854cD7VhIH/H8R529aQAIc9vs6yhm5fkMOIkrRKuqQuVyl9fpPrsYMhZ
> oS6LnQ4qTYSqO+i2vki22ztZsi/bGknfckupEHIf7ck7rbxfyj+jURKZtl+20pyStBUOW1trrYfI
> qoQz8NO7qGm7pRtT24GW17SRfFkYWs0QQdYMCIq82EXY8VDnPCofFxM5ZevyWu+geVrOBB9lt7WM
> oUCFkQrpEerRJgwpbVq2DOPHMZEvx0ee7QfEd0zfeiY67WdLsq1S/B9wtak+
> ==== END SVK PATCH BLOCK ====
> _______________________________________________
> List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
--
More information about the Rt-devel
mailing list