aql::select

This is the select function that parses AQL and returns the query results.

Basic Usage

$aql = "
		artist {
		   name
		   where id = 10 
		}
	";
$rs = aql::select($aql);

Prases to this SQL

SELECT artist.name as "name", artist.id as "artist_id" FROM artist as artist WHERE artist.active = 1 AND artist.id = 10

It will return a nested row record array.

Array
(
	[0] => Array
		(
			[name] => Pink Floyd
			[artist_id] => 10
			[artist_ide] => BekjfoijeDZ
		)
)

Table idenitifer and an encrypted table identifier (IDE) is returned automatically with the results.

Note: All IDEs are made up.

AQL assumes that each table has the field active. Instead of deleting records in a database, you can set active = 0 and it will return the same results.

Left Joins

artist_album {
	name as album_name
}
artist {
	name as artist_name
}

AQL will figure out joins automatically based on foreign keys in the table, but you can also write them explicitly.

artist_album {
	name as album_name
}
artist on artist_album.artist_id = artist.id {
	name as artist_name
}

Select DISTINCT and Aggregate Functions

For these types of queries, record identifiers will not be automatically added, so you may have to explicitly state them if you want them in the results.

distinct artist_album {
        id as artist_album_id,
	name as album_name
}
artist {
	name as artist_name
}

For aggregate functions in queries, the appropriate fields are added to the group by clause if they are not already there.

Referencing models

When defining a model (see AQL Models), you can use queries that reference sub models.

Single

album {
    name,
    year
    artist_id,
    [artist]
    where id = 1
}

The constructor id needs to be in the results list, if necessary, you can specify which field to use to generate the query: [artist(other_field)] if in the fields for the album table, there exists, artist_id as other_field.

The example result of this query would be:

Array
(
    [0] => Array
        (
            [name] => The Wall
            [year] => 1979
            [album_id] => 1
            [album_ide] => Dfjkdjfopj
            [artist_id] => 10
            [artist_ide] => BekjfoijeDZ
            [artist] => Array
                (
                      [name] => Pink Floyd
                      [artist_id] => 10
                      [artist_ide] => uNWllh8Zatc
                )
        )
)

• NOTE: you can also use aliases. [artist] as singer

Plural

You can do the opposite and get many results for record.

artist{
    name,
    [album]s
    where id = 10
}

You can also use an alias here, the plural syntax figures out how the two tables would join and then executes a second query to get the results.

Subqueries

These aren't actual SQL subqueries, they are, in effect, post executed queries, like the example for the plural model referece, but without having to define the model externally.

This is an equivalent for the above query. It will return many album records for each artist record.

artist {
    name
    album {
         name,
         year
    }
    where id = 10
}

Multiple Subqueries and Subquery Joins

The syntax for doing joins is the same as for the main query, but if you want to do multiple subqueries, they must be separated by a comma.