Skip Menu |
 

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

Report information
The Basics
Id: 104242
Status: open
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: JLEFLER [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Wishlist
Broken in: (no value)
Fixed in: (no value)



Subject: Grouping JOIN Clauses with Parentheses
Download (untitled) / with headers
text/plain 631b
As far as I can tell, it's not possible to group joining, as in the following SQL: SELECT * FROM A LEFT JOIN (D JOIN E ON E.d_id = D.id) ON (D.a_id = A.id); (Only grab D if it also has E.) The attached patch piggybacks off relationships' join attributes, allowing one relationship to include another, surrounding them both in parentheses: package Test::Schema::Result::A; ... __PACKAGE__->has_many( "D" => "Test::Schema::Result::D", { "foreign.a_id" => "self.id" }, { join => 'E' }, ); It seems to work for complex joins, as well, but it's only been lightly tested (though it does NOT fail the current test suite).
Subject: dbic_join_grouping.patch
diff -rupN DBIx-Class-0.082820/lib/DBIx/Class/ResultSource.pm DBIx-Class-0.082820.new/lib/DBIx/Class/ResultSource.pm --- DBIx-Class-0.082820/lib/DBIx/Class/ResultSource.pm 2015-03-20 12:31:00.000000000 -0400 +++ DBIx-Class-0.082820.new/lib/DBIx/Class/ResultSource.pm 2015-05-05 17:40:07.646500380 -0400 @@ -1700,6 +1700,12 @@ sub _resolve_join { or $self->throw_exception("No such relationship $join on " . $self->source_name); my $rel_src = $self->related_source($join); + + my @grouped_joins; + if ($rel_info->{attrs}{join}){ + @grouped_joins = $rel_src->_resolve_join($rel_info->{attrs}{join}, $as, $seen, [@$jpath, {$join => $as}]); + } + return [ { $as => $rel_src->from, -rsrc => $rel_src, -join_type => $parent_force_left @@ -1714,6 +1720,7 @@ sub _resolve_join { ), -alias => $as, -relation_chain_depth => ( $seen->{-relation_chain_depth} || 0 ) + 1, + @grouped_joins ? (-join => \@grouped_joins) : (), }, scalar $self->_resolve_condition($rel_info->{cond}, $as, $alias, $join) ]; diff -rupN DBIx-Class-0.082820/lib/DBIx/Class/SQLMaker.pm DBIx-Class-0.082820.new/lib/DBIx/Class/SQLMaker.pm --- DBIx-Class-0.082820/lib/DBIx/Class/SQLMaker.pm 2015-03-20 07:54:07.000000000 -0400 +++ DBIx-Class-0.082820.new/lib/DBIx/Class/SQLMaker.pm 2015-05-05 17:38:39.899603508 -0400 @@ -402,6 +402,9 @@ sub _gen_from_blocks { if (ref $to eq 'ARRAY') { push(@j, '(', $self->_recurse_from(@$to), ')'); } + elsif (ref $to eq 'HASH' and $to->{-join}){ + push(@j, '(', $self->_recurse_from($to, @{$to->{-join}}), ')'); + } else { push(@j, $self->_from_chunk_to_sql($to)); }
Download (untitled) / with headers
text/plain 1.5k
On Wed May 06 00:27:26 2015, JLEFLER wrote: Show quoted text
> As far as I can tell, it's not possible to group joining, as in the > following SQL: > > SELECT * > FROM A > LEFT JOIN (D JOIN E ON E.d_id = D.id) ON (D.a_id = A.id); > > (Only grab D if it also has E.)
Hello! I am terribly sorry it took so long to reply to this - it has not been a great year :/ To make it worse my reply to this will be negative: the patch is way too simplistic to be included in the core library as is. There is a lot of machinery dependent on being able to reliably introspect the join-chain, at least one such spot being [1], which may or may not need to be adjusted (but for sure needs to be tested). Additionally (if I remember correctly, it's been a while) - such "soft-subquery" use cases behave slightly differently on different engines, so additional testing would need to be done to check that such syntax is truly portable (if it isn't - the feature should not be considered). Last but not least the name is way too generic, and does not really describe what is being done. I am going to keep the ticket open for the time being, in case there is interest. But *WAY* more work is needed to get this into the core, because it will have to be supported virtually forever from that point on. As an alternative may I suggest you use (with adjustments) the technique described here: http://lists.scsys.co.uk/pipermail/dbix-class/2016-March/012147.html ? Once again - sorry for the late reply :/ Cheers [1] https://metacpan.org/source/RIBASUSHI/DBIx-Class-0.082821/lib/DBIx/Class/Storage/DBIHacks.pm#L354-538


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.