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

[Byron Young]

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?

Thanks
Byron