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.
_______________________________________________
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]
