[Rt-devel] PATCH DBIx::SearchBuilder
Todd Chapman
todd at chaka.net
Tue Aug 15 22:37:50 EDT 2006
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.
-Todd
-------------- next part --------------
==== 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 ====
More information about the Rt-devel
mailing list