[Dbix-class] Chaining resultsets that join to the same table

Peter Rabbitson rabbit+dbic at rabbit.us
Fri May 29 21:58:18 GMT 2009

Byron Young wrote:
> Peter Rabbitson wrote on 2009-05-29:
>> Peter Rabbitson wrote:
>>> Byron Young wrote:
>>>> Hey all,
>>>> Sorry if this is a FAQ, but I couldn't find any info in the cookbook
>>>> or the joining manual or other docs.
>>>> I have a table for test results, 'results', that has two paths
>> through relationships to join to another table for hostnames,
>> 'hostnames', like so:
>>>>   results --> map_results_tested_products --> tested_product -->
>>>>   hostname results --> map_results_tested_hosts    --> tested_host   
>>>>   -->
>> hostname
>>>>  If I want to search all results for tests that tested a product with
>>>> hostname 'XXXXX', the query looks like:
>>>>   $schema->resultset("Results")->search({ "hostname.name" =>
>> "XXXXX" },
>>>>                                         { join => {
>> map_results_tested_products => { tested_product => 'hostname' } }
>> });
>>>> However, if I want to chain that with another search so I get
>> all results for tests that tested a product with hostname 'XXXXX'
>> and included a host with hostname 'YYYYY', I have to use
>> 'hostname_2' in the search condition for the second query, since
>> 'hostname' has already been joined:
>>>>   $schema->resultset("Results")->search({ "hostname.name" =>
>> 'XXXXX' },
>>>>                                         { join => {
>> map_results_tested_products => { tested_product => "hostname" } }
>> })
>>>>                                ->search({ "hostname_2.name" =>
>> 'YYYYY' },
>>>>                                         { join => {
>> map_results_tested_hosts => { tested_host => "hostname" } } });
>>>> This is fine when the whole query is exposed in code like this,
>> but the problem comes when I'm chaining searches on resultsets that
>> come from somewhere else.  For example, if I add a custom resultset
>> method for the first search, the person writing the chained search
>> doesn't necessarily know that 'hostname' has already been joined
>> once and that they should append '_2':
>>>>   $schema->resultset("Results")->tested_product_name("XXXXX")
>>>>                                ->search({ "hostname.name" =>
>> 'YYYYY' },
>>>>                                         { join => {
>> map_results_tested_hosts => { tested_host => "hostname" } } });
>>>> The above looks right to the coder but wouldn't work how you
>> would expect from just looking at it, because it would search on
>> the wrong joined 'hostname' table.
>>>> The way this is coming up for me is that I have written a simple
>> list filtering system for my Catalyst app, where my Controller
>> classes can provide info about the where conditions and joins for
>> the filters in the controller config, and my List base controller
>> class chains the filters that the user selected into a query and
>> displays the filtered list.
>>>> What I've done to get around it is to inspect the 'join'
>> attributes and use a %seen hash to count the number of times a
>> table has been joined, and then fix up the where condition
>> appropriately.  It works, but the code is more convoluted than I
>> would like, and I'm worried that by messing with my queries like
>> this I'm relying on some internal DBIC behavior that may change at
>> some point.
>>>>  Is there a better way to solve this issue so that I can chain queries
>>>> that don't collide even if they join to the same table?
>>>  This is definitely not a faq, and concerns one of the hairier parts of
>>> DBIC. Since 0.08103 DBIC can do the counting for you properly, but with
>>> the caveat that you are accessing internal methods which can change
>>> without notice:
>>> my $aliases = $rs->_joinpath_aliases ($rs->_resolved_attrs-
>>> {from});
>>> If you Data::Dumper the resulting hashref you will see that the answer
>>> to your question is then as simple as:
>>> my $alias = $aliases-
>>> {map_results_tested_hosts}{tested_host}{hostname};
>> Sorry, my bad. What I sent will not work correctly if you have
>> ->search_related calls in your chain. The full code is:
>> my $rattr = $rs->_resolved_attrs_copy;
>> my $aliases = $rs->_joinpath_aliases ($rattr->{from}, $rattr-
>>> {seen_join});
> Thanks for the help, Peter.  I'll look into upgrading at some point so I can get those features.  I'm still running 0.08010.
> I'm curious, though - why can't search() do this internally with the search conditions?  If it sees 'table.column' in the condition and it knows it's joining to 'table' and that 'table' will have an alias 'table_X', couldn't it update the search condition and do that work behind the scenes?
> If it's something that could be done but nobody has had the time or inclination, I'd be happy to offer a patch provided I can get some guidance with the DBIC internals.  However, I'm going to guess that there are lots of implications to a change like this that I'm not aware of, and that it's probably been thought through and dumped a few times already.

Because how does search() know that you want to search on the
join you just supplied or on the join that appeared in a
search() before? Instead of making assumptions, dbic does
exactly what you told it to.

More information about the DBIx-Class mailing list