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
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
