damil/SQL-Abstract-More — pull request #4 by pvree (2013-12-30 08:21:13+00:00)
Title: Added possibility to use literal values for outer joins (and generate the right amount of placeholders)

Hi,

I really really like the SQL::Abstract::More module. Many thanks for writing/providing it! While using the module it feels like a stroll in the park when writing small scale scripts and sites. After a short while I got to a bump in the road, MySQL does a lot of query optimization which forces me to use slightly more complex join-conditions.

Therefore I changes a few tidbits within the sub _parse_join_spec to make it also support columns and literal value matches so I can get MySQL Optimizer to work the way it should treat my queries.

See http://dev.mysql.com/doc/refman/5.5/en/left-join-optimization.html for an in depth explanation to why people would need literal values matches using OUTER JOINS.

In order to keep the code backwards compatible (automatically add tablenames to columns) I added an additional token (the questionmark) to keep it clear it's about a literal value match instead of an column-based match which could use the automatically added tablenames.

The minor changes now make it possible to write queries such as:
- SELECT questionnaires.id, responses.id FROM questionnaires LEFT JOIN responses ON questionnaires.id = responses.questionnaire AND responses.date > '2013-01-01' WHERE questionnaires.name = 'Example Questionnaire';

I hope you like the minor changes I wrote and will accept them into the SQL::Abstract::More package. If there's a better way to achieve the change I wrote, please let me know!

With kinds regards,
Pascal Vree