auraphp/Aura.SqlQuery — pull request #21 by mbrevda (2014-06-26 08:14:33+00:00)
Title: [proposal] Aliased columns

Problem
===

I'm writing a system that will allow a restful api consumer to lightly modify/restrict a query in a way that dose not require the author of a model to account for with any special behavior. The $crub object would be passed to a class that would auto-manipulate the query using passed url query parameters.

The proposed url query format would be like (w/ whitespace for readability):
```
api.example.com?
$limit=4|5 // limit | offset
&$sorts[]=col2 // column
&$fields[]=col1 // column, will only return listed fields
&$fields[]=col2 // column
&$filter[]=col1|>|6 // column | operator | comparator
&$filter[]=col2|=|hot // column | operator | comparator
```

While some params are simple to implement (i.e. adding a limit/offset is as simple as parsing their value and calling $select->limit($limit)) there are others that are proving quite difficult. Ignoring property visibility in sqlqury's CRUD classes, here are some of situations encountered.

Given the following sql query:

```sql
SELECT
foo,
bar,
fooblue,
yellow.baz as zzz
FROM
orange
LEFT JOIN yellow AS y ON orange.foo = y.foo;
```

1. Filtering on ```foo``` will cause mysql to complain of ambiguity
2. Adding a table to the filter field (```$filter[]=yellow.col2|=|hot```), while removing ambiguity, violates the [law of Leaky Abstraction](http://www.joelonsoftware.com/articles/LeakyAbstractions.html). Additionally, it causes some headaches when trying to escape the user-supplied values by using ```bindValue()```
3. Auto appending the table name to the column before calling ```where()``` limits filters to the main table, restricting columns from a join from being filtered
4. Removing columns to accommodate ```$fields``` requests is extremely difficult to do accurately without a complex convention (and even then).
5. The consumer must request a filter on the DB field name, and cannot use the field name returned to him by the api. This forces him to be fully aware of the DB schema, removing separation of concerns.

Solution
===

The proposed solution would be to allow columns to be aliased for further reference. By way of example, here is what the api would look like:

```php
$select
->cols([
'foo', // legacy/backwards compatible
'bar as bbb', // legacy/backwards compatible, will be auto parsed
['yellow.foo' => 'foo'], // new syntax, in the form of field => alias. Will be built as 'yellow.foo AS foo`,
['yellow.baz' => 'zzz']
])
```

This syntax will allow for any aliased field to be simply omitted from the query (a new ```removeCol()``` method will be necessary), and will allow for greater intelligence when trying to filter queries:

1. Filtering would be requested on the alias (```foo```) which can then be resolved to ```yellow.foo``` by looking up the real field name in the ```$cols``` array.
2. No need to reference the proper table
3. No need to append the table name or try to automagically guess it
4. Removing aliased fields would be a cinch (```$select->removeCol($alias)```)
5. By their very definition of being aliased, the modal will return fields by their aliased names. Should these be returned directly to the consumer, the consumer can use the same field for a ```$fields``` limitation.

I'm also open to other suggestions on how to go about this issue.