SQL Query

Home » SQL Query

The SQL query supported by camlsql-js is limited but hopefully powerful enough for your needs.

SELECT
    [SCOPE {DEFAULTVALUE | RECURSIVE | RECURSIVEALL | FILESONLY}]
    field_name [, field_name ...]
    FROM list_name
    [ [LEFT] JOIN list_alias ON list_name.field_name [...]]
    [WHERE where_condition]
    [GROUP BY field_name]
    [ORDER BY [data_type:]field_name [ASC | DESC], ...]
    [LIMIT row_count]

TBD Joins?

• The SCOPE operator will set the ViewScope.
• field_name is the Field Name in the list. This will be used in the FieldRef Name parameter and generate the <ViewFields> Element
• list_name is the name of the SharePoint List itself.
• where_condition indicates the condition or conditions that must evaluate to true for the row to be selected. This will generate the <Query>

Field Name

This is a comma separated list of Field Names (ID not supported yet). Note that these are the internal field names.

SELECT * FROM List
SELECT Title, Field2 FROM List
SELECT Title, [Field2] FROM List

• Using * will not create a ViewFields element at all
• [ and ] around a field name will be trimmed away

List Name

This is the name of the SharePoint list. It will be used internally in the exec method, and can be retreived using the getListName method as well.

• [ and ] around a field name will be trimmed away

Where Condition

These are the conditions that must be fullfilled for a row to be returned.

Any parameter to compare must be defined outside the query itself.

The parameter location should be marked using either the ? character which will be replaced with the value from the parameter array. The first ? will use the parameter at index 0, the second will use parameter at index 1 and so on.l

camlsql.prepare("SELECT * FROM [List] WHERE [Title] = ?", ["My title"]);

Or as named parameters:

camlsql.prepare("SELECT * FROM [List] WHERE [Title] = @title", {
 "@title" : "My title"
});

Read more about Parameters.

Order by

camlsql.prepare("SELECT Title FROM [List] ORDER BY [Title] ");
camlsql.prepare("SELECT Title FROM [List] ORDER BY [Title] DESC");
camlsql.prepare("SELECT Title FROM [List] ORDER BY [Title], [Created] DESC");

The conditions you set here will create the <OrderBy> element.

Order By - DataType

To set the Type attribute on the OrderBy FieldRef element, add it before the field name followed by a colon.

ORDER BY DateTime:Created

Would give the XML:

<OrderBy>
 <FieldRef Name="Created" Type="DateTime" />
</OrderBy>

Limit

camlsql.prepare("SELECT Title FROM [List] LIMIT 50");

This will set the <RowLimit> element.

Read up on Paging to know more of how you can handle pagination.