From Porcupine wiki

Developers: The Porcupine OQL Select statement

SELECT_STMT ::=

"select" ("*"|SELECT_LIST) "from" SCOPE_LIST
["where" EXPRESSION
["order" "by" ATTR_LIST ("asc"|"desc")]
["group" "by" ATTR_LIST]

SELECT_LIST ::=

FIELD_EXPR ("," FIELD_EXPR)*

FIELD_EXPR ::=

FIELD_SPEC ("as" NAME)

FIELD_SPEC ::=

NAME |
EXPRESSION |
"(" "if" EXPRESSION "then" EXPRESSION "else" EXPRESSION ")" |
"min" "(" FIELD_SPEC ")" |
"max" "(" FIELD_SPEC ")" |
"avg" "(" FIELD_SPEC ")" |
"sum" "(" FIELD_SPEC ")" |
"count" "(" FIELD_SPEC ")"

ATTR_LIST ::=

FIELD_SPEC ("," FIELD_SPEC)*

The SELECT statement always returns a porcupine.core.objectSet.ObjectSet object. If select * is used then the returned object set iteration returns Porcupine objects, else the object set iteration returns dictionaries. If a Porcupine object does not have one of the selected attributes then None is returned. When selecting object datatypes the type of the returned values are:

Data typeConverted to
porcupine.datatypes.Stringstr
porcupine.datatypes.Integerint
porcupine.datatypes.Floatfloat
porcupine.datatypes.Booleanbool
porcupine.datatypes.Passwordstr
porcupine.datatypes.Datetimeporcupine.datatypes.Datetime
porcupine.datatypes.Dateporcupine.datatypes.Date
porcupine.datatypes.Reference1porcupine.systemObjects.GenericItem
porcupine.datatypes.ReferenceNporcupine.core.objectSet.ObjectSet
porcupine.datatypes.Relator1porcupine.systemObjects.GenericItem
porcupine.systemObjects.RelatorNporcupine.core.objectSet.ObjectSet
porcupine.datatypes.Compositionporcupine.core.objectSet.ObjectSet
porcupine.datatypes.Textstr
porcupine.datatypes.Filestr

Sample queries:

Return all the objects' IDs and their display names only if they have a memberof attribute:

select id, displayName from deep('/')
where memberof

Return the user objects contained inside the 'Users and Groups' container:

select * from 'users'
where contentclass = 'schemas.org.innoscript.security.User'

Since the members attribute is an instance of the RelatorN type, the members.displayName is returned as a list of strings containing all the members' display names of each group:

select id, displayName, members.displayName
from 'users'
where members

Return all the containers IDs and how many children they contain:

select parentid, count(id)
from deep('/')
group by parentid

Search object descriptions for 'ser':

select id, displayName, description from deep('/')
where 'ser' in description

Subqueries are supported since the 0.0.2 version. Select the group's ID, display name, and members whose name starts with 'a' only if the group has at least such a member:

select id, displayName,
(
	select * from this:members
	where slice(displayName,0,1) = 'a'
) as amembers
from 'users'
where len(amembers)>0
Retrieved from http://wiki.innoscript.org/pmwiki.php/Developers/OQLSelect
Page last modified on December 16, 2006, at 11:02 PM