[Rt-devel] PATCH DBIx::SearchBuilder
Todd Chapman
todd at chaka.net
Tue Aug 15 23:04:33 EDT 2006
On Tue, Aug 15, 2006 at 10:48:23PM -0400, Jesse Vincent wrote:
>
>
>
> 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 ;)
>
Updated patch attached and submitted. ;)
-------------- next part --------------
==== Patch <left_join_expression> level 2
Source: 6fc35107-3207-0410-b21e-9a2d7f624572:/local/bp/SB/left_join_with_expression:8478
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.
r8478 at slah001: root | 2006-08-15 22:49:29 -0400
Document the new API.
=== t/02searches_joins.t
==================================================================
--- t/02searches_joins.t (revision 5729)
+++ t/02searches_joins.t (patch left_join_expression level 2)
@@ -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 2)
@@ -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.pm
==================================================================
--- SearchBuilder.pm (revision 5729)
+++ SearchBuilder.pm (patch left_join_expression level 2)
@@ -1212,6 +1212,13 @@
Instead of ALIAS1/FIELD1, it's possible to specify EXPRESSION, to join ALIAS2/TABLE2 on an arbitrary expression.
+Combining the previous two points; if you are doing a left join and
+you are also supplying an expression, you may find that you need to refer
+to TABLE2 in your expression. But you can't do that because you don't know
+what the generated alias for TABLE2 will be. To solve this little dilema
+you can use the magic string B<__TABLE2ALIAS__> in the EXPRESSION and each
+occurence of it will be replaced with the generated alias.
+
=cut
sub Join {
=== SearchBuilder/Handle.pm
==================================================================
--- SearchBuilder/Handle.pm (revision 5729)
+++ SearchBuilder/Handle.pm (patch left_join_expression level 2)
@@ -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)
eJyNV81v48YVZw8tYB6a9lAURVBgslYqL6APcjj80nZV22s7dWp4nbW9LZAUwnA4tLmmSJUc2RaW
LlYqmp42WzQBWhQ59OMSYNui96LnXpJj/p++GUq2rPV+CIJMz7z3e783M+/3hlv5wZ1Vs+x2jbJm
GuX+w591OntUsON3TVLWnJKHscjyml0m/JQnNatMsqMaKVPa5zBbZMOcyQdB8yMu5EPMTrjodk2A
8yq4TQUxg1WoARVZWtR8Bd8TOec1s3RXvXLVkt9ezfTLgsOMgu3l/DQu4iyFwB5xPbAAcxPcswFP
e3mWCTljeXgVS1+jZElW8J4EXyXlqi3NcQ0SUvZhnHMGjEYwKhTQ1FnZ2TPYKE6AlVPuc5qz4/Vh
nIQ8bw36L3g4NyFb1/3mnVR+9pTjPBlTZq8grWsksFGKtoELBciL3qMsTouWUEnNEyHXqXvSyxI5
TQvKBCzfgs9LechlBEQ1SweDZNQT/FyEPBFUcbNw6WHLt0LOiOt6nLuE2th2HcsPAl8++DXfghV4
uKtp//7gP+aDj9/Txm9rE+1Zf/zDP2nj7zxFn2k//+TOM208evbdf2rjt56+81/t8JO7TyZEi5++
o01W/7D0W21y8dn2p9rkJ3/cgl/y529+qv0+/dx/Mmlpk+//q4PJzubWAXr//vYuOovFMdr8xd6D
zf397fuXD7vobhfV+zROW9ctdl/muotyHvE8j9MjJDJ0sLa+s4lvwotDtLa7gXq9ymRtZ3ttv9dr
HYwGHN1FH9UHedyn+eij+ov4YpinBaJJTItb6PYdHU0/tWHB86KXBY+a3Z24H4sVpGBl0Joyb6Ct
7c2dDcXivTwbDrbDegPd39t8sHZw/4Ea3t6HkYdrO4eb6t/dw52d+nyQuFi5FuheNkxFA+EGusWy
XJ4BlA77Ac9RFqE+l08FSjOB4hRNc0JHMvQ16suLsFtxXohmNw4byJqDlhYoDpWvvrRc7cBpTBE/
jwshF13lqXanoS+p1TyVp57mK5Zz+82GJKFqH7qXBB/nnIZyRcwGOstjwafPorJD9Tn/+kUDKccL
/ULXi2EAuYNKhVRQ9FhNVHuIVvSlD+c2Yql+COmpLanL+HX0y4a+jEJ+Wq2YtDYbS0sQdvqZnRJl
OZ3FV7MFZ1kaLsyTm72XUUETXujLYIbBzJgODgeDLBfS2bo5NAjknGRWylHVeGSEJic2ZtznhPgG
M5kd2iEhzPBDanqvVAkiEYKAWSF4mR41QoMwykwaWrZjUsuzHM+vYbcSiedvP/+B/i3tC6x9rE3o
F53tNM021m+kpoAdYoYhJya2QttxLBt73Pepwe3I8V2PkldSs5WAGQEmTmD7lk2swPA9N+BmQCIb
2FLIvEZso+I2+Xw0+cutb//mR9r46772BP7+763x14/uZf0AzoXUiWMOpQEtKhsWSJxlaAD6LIo7
KI7QKBsimnMUZupwo4RHAkn9RjQN9dksTYpMbVUyUmYp1AMgFrLnNRRGn45QFKchBKNCjaSch1Ki
lF7pl1ol6xSm8zmEFlofVj6MpnUBXCqUgDMKBalmwkzOnKTZmX4m52RORzzlORUQRhUlirJ8FuQs
ThLwb6ED4J0lpxwc4gIlsRAJJAs71af6NKIseoXXp0cxQ4VQ4rr+4wXp7Erm0mxOK2GNEKfsWM8Y
G+Y8ZVyqUixm8SH5QUIZMFQqewPpG4+QOgARcaKIGyQwgyjwuBdGtu9wMzJMLzCxw6ojJK8l13vx
QmNv/xRIJry6Fzivba3OK0+mK4mZjst9ZpGIkQDbzMNAxycu8SJiB5YLRUOmrXX81dL4y4vvjfe0
8d++oT0Z17TffTD+O/uH9tcPx89bIMhweyoe11XPMKWqzQ/g+gW6Da1qZXG0seg3L/Tyc+mhdm4e
WA28ADwbbSz6ISXVNSb1OIeDiu7+GhXthXPRrlpf++jGrVQr5keEONgNWBh42POYYzqeE1ouiaht
RoGhLj2O7Zco94iBV4uEHhuG2QEhh6sjKhE2DKdpeE2TINPtmF6HmKhpEMPQ0T1oHFVjQgN5f4Xj
XlXPZSUX6pCrMpT1KA+hYqwjJn3hJF4VTkuXFFzyCgo2wmbHMjq2N6PQHxW/SlAxSgU9BxE4rzDs
12LgjnGJsQZyCUSGqboIAj8xkpnMSvQa+4US0m8oTKjCOS1rTeEFL0QlE+/DqL54tZom77yOuG12
iDMjvpnATSgFNoDeH8g2H9MAGh3qQd31QFNOKnFhWchBK4q4ykO2+2k899XxcMf04TuLd3UxBD0L
8uyEgwLL+w9wGOY0qRKukL3XIRO/gy+RNzI27PO00taUn6G1ve2WLi/3ciHVpb53JdrdLi5rGFdv
UAfqNajTOUzjU7hi0eRdpwSpgQN5DG9k0Hrgn+EwDmuWV7aTjNGkHQza++vtK2S5F3Pw0zepmuWU
TsQs2zTcpoXhxyCm0QywyZs+xaEbOZjYLu7exuXLYmK7bG+sb583r+uiyIfpCdiAty/DQLd2KXNZ
0wwYaxoWhPGiiMKP7WHX8nzq2TXPeCM6nTdLsiNT/D/cjJIR
==== END SVK PATCH BLOCK ====
More information about the Rt-devel
mailing list