Hi,
I've run into a problem with constructing queries with joins in DBIC. I have a
page in my Catalyst app where you can view the contents of a DB table, and I'm
building a 'Filter Results' box in the page so you can search the table. My
code takes the values of the Filter form and generates a query depending on
what's in there.
But I've run into an problem in the case that two of the filled-out Filter
fields cause the same join to happen more than once. When that happens, the
joined table is given an alias of table_2, table_3, etc, so then my WHERE
condition needs to know what the alias is in order to build the right search.
As far as I can tell, there's no way to get that joined table alias that's
going to be used before I call search(), or to have search() automatically fix
my WHERE condition. Am I missing something? I can make it work in my app by
tracking the number of times a relationship has been joined and then generating
the alias myself, and fixing my WHERE conditions. Does this seem like an ok
solution? I'm a little wary of it because (1) I'm re-implementing some things
in my app that are already implemented in DBIC - namely, the join => []
attribute parsing and the join table aliasing, and (2) (this may be unlikely
because the aliasing scheme is documented) the table alias numbering behavior
might change in the future, and I would have to update my app to keep up with
DBIC changes.
In case I'm not explaining myself well, here's some of my code and the
resulting SQL:
The %joins entries describe how to build the query when the 'Filter Results'
form is submitted. It just grabs the entries for the form fields that were
actually entered. The two I show below both happen to end up with a join and a
search on the same table, 'hostname'.
my %joins = (tested_host => { where => 'hostname.name',
join => { 'map_results_tested_hosts'
=> { 'tested_host'
=> 'hostname' } },
'+select' => [ 'hostname.name' ],
},
tested_product => { where => 'hostname.name',
join => {
'map_results_tested_products'
=> { 'tested_product'
=> 'hostname' } },
'+select' => [ 'hostname.name' ],
},);
while (my ($filter, $join_attrs) = each(%joins)) {
if (exists $filters->{$filter}) {
my $filter_attr = {};
%where = ();
# set condition for where clause
my $cond_attr = $join_attrs->{where};
# $cond_attr can be an array ref to signify an operator other
# than '=' should be used when searching for the condition. the
# first item is the condition name and the second is the operator
if (ref $cond_attr && ref $cond_attr eq 'ARRAY') {
$where{$cond_attr->[0]} = { $cond_attr->[1] =>
$filters->{$filter} }
} else {
$where{$cond_attr} = $filters->{$filter};
}
# set join attributes
foreach my $key (qw/join +select +as/) {
my $val = $join_attrs->{$key};
$filter_attr->{$key} = $val;
}
$rs = $rs->search(\%where, $filter_attr);
}
}
If just one of the tested_host or tested_product filters are used, the query is
fine, but it both are used I get a collision in the WHERE condition. The
second join gives 'hostname' an alias 'hostname_2', but my WHERE condition for
both queries is still 'hostname.name'. Here's the query:
SELECT me.id, me.result_type, me.start_time, me.end_time, me.results_dir,
me.test_case, me.job, me.modified_ts, me.result_category, me.bug_ids,
me.comments, me.is_debug, hostname.name
FROM results me
LEFT JOIN map_results_tested_products map_results_tested_products ON (
map_results_tested_products.result = me.id )
JOIN tested_products tested_product ON ( tested_product.id =
map_results_tested_products.tested_product )
JOIN hostnames hostname ON ( hostname.id = tested_product.hostname )
LEFT JOIN map_results_tested_hosts map_results_tested_hosts ON (
map_results_tested_hosts.result = me.id )
JOIN tested_hosts tested_host ON ( tested_host.id =
map_results_tested_hosts.tested_host )
JOIN hostnames hostname_2 ON ( hostname_2.id = tested_host.hostname )
WHERE ( ( ( hostname.name = ? ) AND ( hostname.name = ? ) ) )
ORDER BY id ASC LIMIT 25: 'gen-cs167', 'gen-cs167'
As you can see, hostname_2.name is not included as a column and it's not
searched on. Is there a good way to get around this?
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]
