anewt-developers team mailing list archive

[Wouter Bolsterlee <uws@xxxxxxxxx>]

Hey all (and especially Sander),

I think my recent feature addition to AnewtAutoRecord covers most of the
JOIN use cases I can think of without adding too much black magic to
AnewtAutoRecord that renders the whole thing into a black box full of
spaghetti code paths. The relevant revision is this one:

> revno: 1707
> committer: Wouter Bolsterlee <uws@xxxxxxxxx>
> branch nick: anewt.uws
> timestamp: Tue 2009-07-21 21:34:32 +0200
> message:
>   [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.
> modified:
>   autorecord/autorecord.lib.php

The relevant API docs can be seen here:

  http://anewt.uwstopia.nl/doc/manual/output/class-AnewtAutoRecord.html#function-db-find-all-by-sql

Regarding your recent SQL builder suggestion in one of the AutoRecord JOIN
related bug reports (https://bugs.launchpad.net/anewt/+bug/361805): I think
this is not needed, since you can do exactly what you describe with today's
code. Taking your example and rewriting that to use
AnewtAutoRecord::db_find_all_by_sql() yields something like this:

  $foo_instances_with_additional_join_stuff = FooClass::db_find_all_by_sql(
      array(
          'select' => '`other_table`.`some_column`, `yet_another_table`.`another_column`'',
          'table-alias' => 'foo',
          'join' => 'LEFT JOIN `other_table` ON (`foo`.`id` = `other_table`.`foo_id`) LEFT JOIN `yet_another_table` USING(`...`)
          'where' => 'online > NOW()',
          'limit' => '5',
      ));

Alternatively, without using AnewtAutoRecord db_find_*() methods, but still
building on its features:

  $connection = AnewtDatabase::get_connection();
  $connection->prepare_execute_fetch_all(
      'SELECT ?raw? FROM ?table? LEFT JOIN ... RIGHT OUTER JOIN ... WHERE ... LIMIT ...',
      array(
          FooClass::db_sql_select(),
          FooClass::db_sql_from(),
      ));

Even more complex queries can be built by using AnewtSQLTemplate directly
and using the (fill()ed!) result as a ?raw? field in a subsequent query
template:

  $really_complex_where_clause = $connection->create_sql_template(
      'foo = ?int? AND SOMEFUNCTION(`bar`, ?str?, ?str?)'
      )->fillv($your_custom_values_array);

  $instances = FooClass::db_find_all_by_sql(
      array(
          'where' => $really_complex_where_clause,
      ));

...or again without AnewtAutoRecord:

  $rows = $connection->prepare_execute_fetch_all(
      'SELECT `id` FROM `some_table` WHERE ?raw?', $really_complex_where_clause);


That's my $0.02 for now. Feedback on this is highly appreciated!

    — Wouter

Attachment: signature.asc
Description: Digital signature