Home » Database » DB_DataObject » Bug #6928

[2006-02-24 19:13 UTC] fnjordy at gmail dot com

Description:
------------
With two databases a join on two tables in the non-default database fail because the database name is not used in the query string.

--
Steve-o

Test script:
---------------
For example:

$items = DB_DataObject::factory('db1_items');
$styles = DB_DataObject::factory('db1_styles');
$items->join($styles);

$sizes = DB_DataObject::factory('db2_sizes');
$products = DB_DataObject::factory('db2_products');
$products->join($sizes);

$items->joinAdd($products);

Expected result:
----------------
What is expected is the following join instead:

SELECT * FROM `invoice_items`
INNER JOIN `can3`.`styles` ON `can3`.`styles`.`style_id`=`invoice_items`.`style_id`
INNER JOIN `can3`.`products` ON `can3`.`products`.`product_id`=`invoice_items`.`product_id`
INNER JOIN `can3`.`sizes` ON `can3`.`sizes`.`size_id`=`can3`.`products`.`size_id`

Actual result:
--------------
Results in the following trace output:

DataObjects_Invoice_items : QUERY : SELECT * FROM `invoice_items`
INNER JOIN `can3`.`styles` ON `can3`.`styles`.`style_id`=`invoice_items`.`style_id`
INNER JOIN `can3`.`products` ON `can3`.`products`.`product_id`=`invoice_items`.`product_id`
INNER JOIN `sizes` ON `sizes`.`size_id`=`products`.`size_id`
DataObjects_Invoice_items : Query Error : [db_error: message="DB Error: no such table" code=-18 mode=return level=notice prefix="" info="SELECT * FROM `invoice_items`

[2006-02-24 20:01 UTC] fnjordy at gmail dot com

This is because the subjoin has no knowledge of the database of the original query. As the join is generated on the fly, as opposed to at the query, this suggests one of two solutions:

a) Enable forcing of database.table combination, e.g.

DataObject.php:3075:

if ( in_array($DB->dsn['phptype'],array('mysql','mysqli')) &&
($this->_force_database || $obj->_database != $this->_database) &&
strlen($obj->_database)
)

b) Re-write join on adding to a query with a different base database.

--
Steve-o

[2006-02-24 20:26 UTC] fnjordy at gmail dot com

As a workaround I am modifying the _join string to add the database. Here 'colours' & 'sizes' are part of the subjoin, and 'products' is the parent join to the original query.

$products->_join = str_replace(
array('`colours`', '`sizes`', '`products`'),
array('`can3`.`colours`', '`can3`.`sizes`', '`can3`.`products`'),
$products->_join);

I'm not sure about getting SelectAs() working, by magic I can get the columns I need without renaming.

--
Steve-o