[Dbix-class] joined table aliases

[Byron Young]

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