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});
_______________________________________________
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]
