Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 55579
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: mark.zealey [...] pipex.net
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 0.08120
Fixed in: 0.08121



Subject: [patch] add straight_join support
Download (untitled) / with headers
text/plain 943b
Hi there, I've been trying to do some query optimization when the database gets the join order the wrong way round, and it seems the easiest way to do this with mysql is to use the straight_join. However there's no way to support this in dbic at present as you assume a syntax like 'left join'. The below code will fix this at a SQLAHacks level, however it may want to go into just the MySQL hacks code, but in that case it would mean that the _recurse_from function would need splitting up a bit to support that. Change /usr/local/share/perl/5.10.0/DBIx/Class/SQLAHacks.pm :506 (v0.8120) from: my $join_clause = sprintf ('%s JOIN ', $join_type ? ' ' . uc($join_type) : '' ); to: my $join_clause = ' JOIN '; if( $join_type ) { if( $join_type =~ /^STRAIGHT$/i ) { $join_clause = ' STRAIGHT_JOIN ' } else { $join_clause = ' ' . uc($join_type) . $join_clause; } } Thanks, Mark
Download (untitled) / with headers
text/plain 739b
On Mon Mar 15 06:55:40 2010, mark.zealey@pipex.net wrote: Show quoted text
> Hi there, > > I've been trying to do some query optimization when the database gets > the join order the wrong way round, and it seems the easiest way to do > this with mysql is to use the straight_join. However there's no way to > support this in dbic at present as you assume a syntax like 'left join'. > The below code will fix this at a SQLAHacks level, however it may want > to go into just the MySQL hacks code, but in that case it would mean > that the _recurse_from function would need splitting up a bit to support > that.
Please do it the other way (the clean mysql-only support). It is ok if the patch becomes larger, it will however be easier to follow. Thank you!
From: mark.zealey [...] pipex.net
Download (untitled) / with headers
text/plain 112b
Please see attached patch against the 0.08120 tag. Not 100% sure I got the test in the right place though. Mark
Subject: dbic_straight_join.patch
Index: t/71mysql.t =================================================================== --- t/71mysql.t (revision 9034) +++ t/71mysql.t (working copy) @@ -192,6 +192,20 @@ [], 'overriden default join type works', ); + + # Test support for straight joins too + $schema->storage->sql_maker->{_default_jointype} = 'straight'; + is_same_sql_bind ( + $rs->as_query, + '( + SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track, + artist.artistid, artist.name, artist.rank, artist.charfield + FROM cd me + STRAIGHT_JOIN artist artist ON artist.artistid = me.artist + )', + [], + 'straight joins correctly supported for mysql' + ); } ## Can we properly deal with the null search problem? Index: lib/DBIx/Class/SQLAHacks.pm =================================================================== --- lib/DBIx/Class/SQLAHacks.pm (revision 9034) +++ lib/DBIx/Class/SQLAHacks.pm (working copy) @@ -485,6 +485,14 @@ } } +sub _generate_join_clause { + my ($self, $join_type) = @_; + + return sprintf ('%s JOIN ', + $join_type ? ' ' . uc($join_type) : '' + ); +} + sub _recurse_from { my ($self, $from, @join) = @_; my @sqlf; @@ -503,10 +511,7 @@ $join_type = $self->{_default_jointype} if not defined $join_type; - my $join_clause = sprintf ('%s JOIN ', - $join_type ? ' ' . uc($join_type) : '' - ); - push @sqlf, $join_clause; + push @sqlf, $self->_generate_join_clause( $join_type ); if (ref $to eq 'ARRAY') { push(@sqlf, '(', $self->_recurse_from(@$to), ')'); Index: lib/DBIx/Class/SQLAHacks/MySQL.pm =================================================================== --- lib/DBIx/Class/SQLAHacks/MySQL.pm (revision 9034) +++ lib/DBIx/Class/SQLAHacks/MySQL.pm (working copy) @@ -21,4 +21,14 @@ return $self->SUPER::insert (@_); } +# Allow STRAIGHT_JOIN's +sub _generate_join_clause { + my ($self, $join_type) = @_; + + if( $join_type && $join_type =~ /^STRAIGHT\z/i ) { + return ' STRAIGHT_JOIN ' + } + + return $self->SUPER::_generate_join_clause( $join_type ); +} 1;
Download (untitled) / with headers
text/plain 288b
On Mon Mar 22 04:32:14 2010, mark.zealey@pipex.net wrote: Show quoted text
> Please see attached patch against the 0.08120 tag. Not 100% sure I got > the test in the right place though. > > Mark
http://dev.catalystframework.org/svnweb/bast/revision?rev=9046 Applied with slight modifications to the test
DBIC 0.08121 shipped containing a fix for this bug.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.