Composite SQL queries for Activerecord_table::find()

While you can pass a SQL statement to the find() method of Activerecord_table, you can also pass an array with the following profile:

Criteria Syntax

[
  table: TABLE,
  fields: [ FIELD [, FIELD, ...] ] ?
  join:  [ JOIN [, JOIN ...] ] ?
  where: [ EQUALITY  [, EQUALITY,...] ] ?
  order: [ FIELD [, FIELD...]  ] ?
  limit: LIMIT ?
  ]

example:

array( 'table' => 'leads',
'fields' => array('first_name', 'last_name', 'zip'),
'where' => array('state = "CA"', array('age', '>', 30),
'limit' => 50);

Note that the bold yellow terms in the above can be put in terms of strings or as more complex structures.

Field Syntax

By expressing fields as nested arrays, tick marks and Drupal table bracketing is done for you.

string | 
[
   table: TABLE,
   field: string,
   as: string ?,
   sort: string (ASC/DESC)?
]

Equality

Equalities are used to describe joins or where filters. They can be nested and compounded

[ EQUALITY_ITEM [,EQUALITY_ITEM ...] ]

EQUALITY_ITEM = FIELD | EQ | VALUE | EQUALITY | CONJUNCTION

EQ = < | = | > | LIKE ...

VALUE = string(quoted) | number | placeholder ('%s' | '%d')

CONJUNCTION = AND | OR | NOT

Join

Joins are a compound of a table and an euuality:

[ 
table: TABLE, 
on: EQUALITY
]

Note that these expressions lend themselves to YAML expression as in the following example:

table: leads
fields:
  - {table: leads, field: first_name}
  - {table: leads, field: last_name}
  - {table: states, field: name}
join:
  -
     table: states
     on: 
        - {table: states, field: state_code}
        -  LIKE
        - {table: leads, field: state}
order:
  - {table: states, field: name}
  - {table: leads, field: last_name}
  - {table: leads, field: first_name}
limit: 100

Note: this syntax is OPTIONAL. You can pass a full query if you want. However it is fairly advantageous in the Activerecord_node_domain in that that class will automatically include the node_revisions table as well as bundle in a module table if its name is passed in as a type.

This syntax has the following value:

• Queries can be expressed in implementation independant notation
• Criteria can be assembled in one place and modified in another
• fussy detail like back ticks and braces -- { } -- around table names are included by the assembler.

Most of the reconciliation of the array criteria is handled by the Activerecord_sql class which is a static library class.