Incorrect NULL values on left joins in AutoRecord

[Sander van Schouwenburg (Sandworm)]

When using AutoRecord::_db_join_one() for a left join (default) and the right side doesn't exist, the key will be overwritten with NULL values. Example:
Table1:
+--+-----+
|id|name1|
| 1|foo1 |
| 2|foo2 |
+--+-----+

Table2:
+--+-----+
|id|name2|
| 1|bar1 |
+--+-----+

> SELECT t1.id, t1.name, t2.id, t2.name FROM table1 t1 LEFT JOIN table2 t2 ON (t1.id = t2.id)
+--+-----+--+-----+
|id|name1|id|name2|
+--+-----+--+-----+
| 1|foo1 | 1|bar1 |
| 2|foo2 | | |
+--+-----+--+-----+

This will result in the following data structure:
$data = array(
  array(
    'id' => 1,
    'name1' => 'foo1',
    'name2 => 'bar1',
  ),
  array(
    'id' => NULL
    'name1' => 'foo2',
    'name2' => NULL
  ),
)
This is because the second 'id' overwrites the first one.

[Sander van Schouwenburg (Sandworm) (sander-sinaasappel)]

This was partially fixed in the experimental join multi code in anewt.new.cxs 1440

The rest is fixed in 1460:
 1460 Sander van Schouwenburg 2009-04-15
      [autorecord] BUGFIX: Invalid NULL values in left joins (Bug #361805)

      This is the rest of the fix where a custom key was supplied.

[wouter bolsterlee (wbolster)]

My recent AnewtAutoRecord work nuked all ugly join and table relation code. My personal opinion is that AnewtAutoRecord should not have such tricky SQL generating code. That said, the new AnewtAutoRecord API allows developers to quickly build SQL queries by e.g. calling AnewtAutoRecord::db_sql_{select,from,order_by}. These can be used to quickly build complex queries while keeping total control over all joins.

I have a strong tendency to close this report as wontfix. Feel free to disagree with me, though. :)

[Sander van Schouwenburg (Sandworm) (sander-sinaasappel)]

Maybe we need something like an AnewtQueryBuilder. That query builder then takes care of creating (complex) sql queries. AnewtAutoRecord only provides information about tables and some simple factory methods for AnewtQueryBuilder (like find_all, find_one_by_id, etc.). Joins might be provided by simple joins classes or something.

Then you could do something like:
$builder = A::create_builder();
$builder->add_join('A', 'B');
$buider->add_join('B', 'C');
$builder->add_where('online > now()');
$builder->limit(5);
$result = $builder->execute();

[wouter bolsterlee (wbolster)]

This adds very simple (manual!) JOIN support to the revised AnewtAutoRecord in anewt.uws:

 1707 Wouter Bolsterlee 2009-07-21
      [autorecord] Support extra select columns and manual joins

      The db_find_one_by_sql() and db_find_all_by_sql methods()
      now support 'select', 'join', and 'table-alias' keys in the
      associative $sql array. Expanded documentation to describe
      how those can be used.

[wouter bolsterlee (wbolster)]

Okay, after a short mail conversation about this topic (on the mailing list) I think this issue is no longer valid, since the new AnewtAutoRecord code does not have this problem. (Additionally, I have removed the old autorecord module from Anewt recently.)