Skip to content

SQL Query

Jump to bottom
David Lidström edited this page Oct 27, 2017 · 11 revisions

Home » SQL Query

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

SELECT
    field_name [, field_name ...]
    FROM list_name
    [WHERE where_condition]
    [ORDER BY field_name [ASC | DESC], ...]
    [LIMIT row_count]

TBD GROUP BY TBD Joins

  • 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).

SELECT * FROM List
SELECT Title, Field 2 FROM List
SELECT Title, [Field 2] 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.

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.

Clone this wiki locally