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

Peter Rabbitson rabbit+dbic at rabbit.us
Fri May 29 08:42:55 GMT 2009


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};

Cheers



More information about the DBIx-Class mailing list