[Dbix-class] RE: Search with complex joins generating bad SQL

[Ihnen, David]

Have you considered going the other way, starting at the hostnames table constraint with your search and working back to the results table?

$result = $schema->resultset("hostnames")->search
  ( { name => 'gen-cs167' }
  , { join => 
      [ { tested_product => { map_results_tested_products => results } }
      , { tested_host => { map_results_tested_hosts => results } }
      ]
    }
  );

Foreach ( $result->tested_products ) {
  Foreach ( $_->map_results_tested_products ) {
     Foreach ($_->results ) {
       Print "Product Result: " . $result->result_field . "\n";
     }
  }
}

Foreach ( $result->tested_host ) {
  Foreach ( $_->map_results_tested_products ) {
     Foreach ($_->results ) {
       Print "Host Result: " . $result->result_field . "\n";
     }
  }
}

David


-----Original Message-----
From: Byron Young [mailto:Byron.Young at riverbed.com] 
Sent: Monday, March 29, 2010 5:54 PM
To: 'dbix-class at lists.scsys.co.uk'
Subject: [Dbix-class] Search with complex joins generating bad SQL

Hello,

I have a search that's generating SQL that causes MySQL to barf.  I'm using DBIC v0.08120.  Here is the search call:

$schema->resultset("Results")->search({
        'hostname_2.name' => {
            '=' => 'gen-cs167'
        }
    }, {
        join => [
            { 'map_results_tested_products' => {
                  'tested_product' => 'hostname'
               }
            },
            { 'map_results_tested_hosts' => {
                  'tested_host' => 'hostname' # references to this will
                                              # need to use 'hostname_2'
              }
            },
        ],
    }
);

And here is the generated SQL and exception:

DBI Exception: DBD::mysql::st execute failed: Unknown column 'tested_product.hostname' in 'on clause' [for Statement "SELECT COUNT( * ) FROM (SELECT `me`.`id`, `me`.`result_type`, `me`.`start_time`, `me`.`end_time`, `me`.`results_dir`, `me`.`test_case`, `me`.`modified_ts`, `me`.`result_category`, `me`.`bug_ids`, `me`.`comments`, `me`.`is_debug`, `me`.`regression`, `me`.`release` FROM `results` `me` LEFT 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` LEFT JOIN `tested_hosts` `tested_host` ON `tested_host`.`id` = `map_results_tested_hosts`.`tested_host` LEFT JOIN `hostnames` `hostname_2` ON `hostname_2`.`id` = `tested_host`.`hostname` WHERE ( `hostname_2`.`name` = ? ) GROUP BY `me`.`id`, `me`.`result_type`, `me`.`start_time`, `me`.`end_time`, `me`.`results_dir`, `me`.`test_case`, `me`.`modified_ts`, `me`.`result_category`, `me`.`bug_ids`, `me`.`comments`, `me`.`is_debug`, `me`.`regression`, `me`.`release`) `count_subq`" with ParamValues: 0='gen-cs167']


Here are the relevant relationship definitions:

Results
==

__PACKAGE__->has_many(
  "map_results_tested_hosts",
  "NBT::Schema::Icebox::MapResultsTestedHosts",
  { "foreign.result" => "self.id" },
);


__PACKAGE__->has_many(
  "map_results_tested_products",
  "NBT::Schema::Icebox::MapResultsTestedProducts",
  { "foreign.result" => "self.id" },
);



MapResultsTestedHosts
==
__PACKAGE__->belongs_to(
  "tested_host",
  "NBT::Schema::Icebox::TestedHosts",
  { id => "tested_host" },
  {},
);


TestedHosts
==
__PACKAGE__->belongs_to(
  "hostname",
  "NBT::Schema::Icebox::Hostnames",
  { id => "hostname" },
  {},
);


MapResultsTestedProducts
==
__PACKAGE__->belongs_to(
  "tested_product",
  "NBT::Schema::Icebox::TestedProducts",
  { id => "tested_product" },
  {},
);


TestedProducts
==
__PACKAGE__->belongs_to(
  "hostname",
  "NBT::Schema::Icebox::Hostnames",
  { id => "hostname" },
  {},
);


Any idea why this is happening?  I'm happy to provide more information if needed.

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/dbix-class@lists.scsys.co.uk