The Porcupine OQL Select statement

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

Find the best way for you!
<a href="http://work-at-home.superdatastorage.info/work-at-home.html">work at home</a>
<a href="http://work-at-home.superdatastorage.info/at-home-work.html">at home work</a>

SELECT_STMT ::=
->"'''select'''" ("'''*'''"|SELECT_LIST) "'''from'''" [[OQLScopes|SCOPE_LIST]]
->["'''where'''" [[OQLExpressions|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 [[http://www.innoscript.org/api/porcupine.core.objectSet.ObjectSet-class.html|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:

||border=1
||!Data type||!Converted to||
||porcupine.datatypes.String||str||
||porcupine.datatypes.Integer||int||
||porcupine.datatypes.Float||float||
||porcupine.datatypes.Boolean||bool||
||porcupine.datatypes.Password||str||
||porcupine.datatypes.Datetime||porcupine.datatypes.Datetime||
||porcupine.datatypes.Date||porcupine.datatypes.Date||
||porcupine.datatypes.Reference1||porcupine.systemObjects.`GenericItem||
||porcupine.datatypes.`ReferenceN||porcupine.core.objectSet.`ObjectSet||
||porcupine.datatypes.Relator1||porcupine.systemObjects.`GenericItem||
||porcupine.systemObjects.`RelatorN||porcupine.core.objectSet.`ObjectSet||
||porcupine.datatypes.Composition||porcupine.core.objectSet.`ObjectSet||
||porcupine.datatypes.Text||str||
||porcupine.datatypes.File||str||

Sample queries:

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

%code%
[@select id, displayName from deep('/')
where memberof
@]

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

%code%
[@select * from 'users'
where contentclass = 'schemas.org.innoscript.security.User'
@]

Since the ''members'' attribute is an instance of the [[http://www.innoscript.org/api/porcupine.datatypes.RelatorN-class.html|RelatorN]] type, the ''members.displayName'' is returned as a list of strings containing all the members' display names of each group:

%code%
[@select id, displayName, members.displayName
from 'users'
where members
@]

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

%code%
[@select parentid, count(id)
from deep('/')
group by parentid
@]

Search object descriptions for 'ser':

%code%
[@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:

%code%
[@select id, displayName,
(
select * from this:members
where slice(displayName,0,1) = 'a'
) as amembers
from 'users'
where len(amembers)>0
@]

->["'''where'''" [[OQLExpressions|EXPRESSION]]

->["'''where'''" [[OQLExpressions|EXPRESSION]