j4mie/idiorm — pull request #66 by hellogerard (2012-07-17 04:53:23+00:00)
Title: Handle ambiguous column names when joining two tables

I ran across a problem with Idiorm (great library BTW) when joining two tables via an intermediate join table (i.e. `has_many_through()`). Here is my setup (this isn't my exact setup - i tried to simplify for illustration). `Users` have many `Products`:

User
====
user_id ...

User_Products
============
user_id ...
product_id ...

Product
=======
product_id ...

I have this line in my User class (using Paris):

return $this->has_many_through('Product', 'User_Product', 'user_id', 'product_id');

Executing this (in Paris):

$product = $user->products()->find_one(5);

gives me a constraint violation due to ambiguous column names. The Idiorm generated query is:

SELECT `product`.* FROM `product` JOIN `user_products` ON `product`.`product_id` = `user_products`.`product_id` WHERE `user_products`.`user_id` = ? AND `product_id` = ? LIMIT 1

This pull request contains changes to add the table name to the ambiguous column name. I added a test for this scenario, though it doesn't really make sense in the test as there are only two tables. The remaining tests all still pass.

- `idiorm.php`: prepend table_name when in a WHERE clause
- `test_queries.php`: add test joining two tables with WHERE clause