nette/nette — pull request #605 by juzna (2012-03-25 21:54:22+00:00)
Title: Database: explicitly define table in where clause to make queries unambiguous

Problem explained:

I've got these three tables joined by foreign keys:

![DB schema](http://files.ukaz.at/images/full/28s.png)

When I want to select *requests* added by me which belong to a *movie* (transitive dependence via *subtitles*), I would use:
`$this->db->table('requests')->where(array('addedBy' => $userId, 'subtitle.movieId' => $movieId))`.
Look at this command. It is obvious that I want to have a condition on *addedBy* column in *requests* table and you probably won't even realize there is another column of the same name. And even if you do so, that column is somewhere else and I'm not using it in the *db command* at all. So I think this command is absolutely clear.

However, Nette Db will fail because of issuing ambiguous SQL query to the database:
`SELECT requests.* FROM requests INNER JOIN subtitles AS subtitle ON requests.subtitleid = subtitle.id WHERE (addedBy = ?) AND (subtitle.movieId = ?)`. Notice the *WHERE* clause - it says `addedBy` which is ambiguous for sql server.

I believe Nette Db should make it clear and tell the database what we wanted, but it has to translate it into the database language. Therefore it must make the sql query **unambiguous**, as what we said to Nette Db was **clear and unambiguous**. Therefore the sql query should be:
`SELECT requests.* FROM requests INNER JOIN subtitles AS subtitle ON requests.subtitleid = subtitle.id WHERE (requests.addedBy = ?) AND (subtitle.movieId = ?)` (explicitly define the table name in where clause).

(Discussion expected)