User Guides

Custom Collection Queries

Assumed Configuration

In the examples on this page, the following content configuration is assumed, in this config we have a post collection and an author collection, authors are related to posts via a related item field in the posts.

content.yml

collections:
  post:
    fields:
      - { name: "title", type: "text", limit: 200, title: "Post Title" }
      - { name: "body", type: "html", title: "Post Contents" }
      - { name: "author", type: "related", collection: "author", collectionField: "name", title: "Author" }
  author:
    fields:
      - { name: "name", type: "text", limit: 100, title: "Author's Name" }

Query Object

Queries in LexasCMS are handled by the \LexasCMS\Classes\Content\ContentQuery class, this class is used by collections and paginators to run queries against specific collections.

You can gain access to the query object of a collection using the query() method on a collection.

$query = $cms->content->collection("post")->query();

If the query object is created through this helper function, it will automatically inherit the collections parent scope and filtering settings.

Query Structure

In order to provide a standard interface for queries, the collection table being queried is selected as root, any child collections or related fields will be automatically joined as root.$field when queried against.

Selecting a post by title:

`root`.`title` = "Post Title"

Selecting posts by author name:

`root.author`.`name` = "Author Name"

Query Building

setQuery

To configure the query object to run a custom query, the setQuery method is used, this allows us to pass a WHERE clause to the SQL statement and an array of parameter bindings to be used in a prepared statement.

$query->setQuery("`root`.`title` = :title", ["title"=>"Post Title"]);

As a shorthand, you can also pass the same parameters to the collections query() method mentioned above.

$query = $cms->content->collection("post")
                      ->query()
                      ->setQuery("`root`.`title` = :title", ["title"=>"Post Title"]);

// Is equal to

$query = $cms->content->collection("post")
                      ->query("`root`.`title` = :title", ["title"=>"Post Title"]);

order

The order method is used to set the ORDER BY clause in the SQL.

$query->order("`root`.`id`", "DESC");

Ordering defaults to `root`.`id`, ASC, although when a query object is generated from a collection bound to a parent or when using the setParent method, the order will be set to honour the ordering value of the relationship set.

Executing

After a query object has been configured, there are several functions we can then use to retrieve data from the query object.

count

The count method will run a COUNT query and return an integer value.

// Count the posts where author is "Steven"
$query->setQuery("`root.author`.`name` = :author", ["author" => "Steven"])->count();

find

The find method allows us to pass limit and offset parameters to the query.

// Find 10 posts by the author "Steven"
$query->setQuery("`root.author`.`name` = :author", ["author" => "Steven"])->find(10, 0);

findOne

The findOne method is a shorthand for find(1), but will only return a single result, rather than an array of results. If no results are found, NULL will be returned.

// Find one post where the author is "Steven"
$query->setQuery("`root.author`.`name` = :author", ["author" => "Steven"])->findOne();

paginate

The paginate method creates a new \LexasCMS\Classes\Content\ContentPaginator from the current query object and accepts a itemsPerPage parameter.

// Get a paginator containing all post by the author "Steven", with 10 items per page
$query->setQuery("`root.author`.`name` = :author", ["author" => "Steven"])->paginate(10);