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

[Byron Young]

For reasons I'd rather not get into right now, I can't do that without redesigning a large component I'm working on, which works very nicely except for this one hiccup.  This feature is purported to work, and in fact used to work before upgrading DBIC to the latest version.  Here's the SQL generated from the same query using DBIC v0.08010:

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 `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_2`.`name` = ? ): 'gen-cs167'


-----Original Message-----
From: Ihnen, David [mailto:dihnen at amazon.com] 
Sent: Monday, March 29, 2010 6:08 PM
To: DBIx::Class user and developer list
Subject: [Dbix-class] RE: Search with complex joins generating bad SQL

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

_______________________________________________
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