N1QL from the SDKs
Edit this article in GitHub
Version 2.4

N1QL from the SDKs

Perform Couchbase Query Language (N1QL) queries via the PHP SDK.

Note: See Couchbase Developer documentation for a quick intro to N1QL.
To issue N1QL queries, you should create a CouchbaseN1qlQuery object, and pass it to the query method in the CouchbaseBucket class. A few variants of such a query exist:
  • Simple queries, which are only strings and do not use placeholders;
  • Parameterized queries, which use numbered or named placeholders.
You can create each via the corresponding factory method on CouchbaseN1qlQuery, passing in the statement as a string.

The return value from query() is an instance of stdClass. Iterating over the rows property of the object will yield the rows returned by the server for the given query. Each row is either a stdClass instance (which is the default) or a nested array depending on the third argument $json_asarray.

Placeholders

You can use N1QL placeholders in the query. Placeholders allow you to specify variable constraints for an otherwise constant query. A named or positional parameter is a placeholder for a value in the WHERE, LIMIT or OFFSET clause of a query. To use placeholders, manually construct a CouchbaseN1QLQuery object with the base query string, and pass an array of arguments for named or positional placeholders to namedParams or positionalParams methods respectively:
N1QL Query with positional and named parameters
// Positional parameters example
$query = CouchbaseN1qlQuery::fromString('select * from `travel-sample` limit $1');
$query->positionalParams(array(9));
$result = $bucket->query($query);
foreach ($result->rows as $row) {
    // ...
}

// Named parameters example
$query = CouchbaseN1qlQuery::fromString('select * from `travel-sample` limit $limit');
$query->namedParams(array('limit' => 9));
$result = $bucket->query($query);
foreach ($result->rows as $row) {
    // ...
}
Tip: Be careful with placeholders and PHP interpolation, as they both use $ (dollar sign) for the substitution variable. If you want to use double quotes for your query string, make sure you escape N1QL placeholders properly.

Query Options

You can set other options in the query object as well:
  • consistency controls the version of the documents to be scanned, and can either be CouchbaseN1qlQuery::NOT_BOUNDED (which is the default) or CouchbaseN1qlQuery::REQUEST_PLUS (which ensures that only the most recent version of documents are used).
  • adhoc determines whether the query will be optimized as a prepared statement at the SDK level. If this is set to false then a prepared reference to this query will be stored on the SDK and used for subsequent queries.

Cross-bucket queries

Since version 2.3.0, the PHP SDK implements the \Couchbase\Authenticator interface, which allows you to perform N1QL queries that involve multiple buckets and joins. Such query objects have to change their behaviour using crossBucket(true) method. The following example demonstrates the workflow:

$authenticator = new \Couchbase\ClassicAuthenticator();
$authenticator->bucket('people', 'secret');
$authenticator->bucket('orders', '123456');

$cluster = new \Couchbase\Cluster("couchbase://localhost");
$cluster->authenticate($authenticator);

$bucket = $cluster->openBucket('orders');

$query = \Couchbase\N1qlQuery::fromString(
    "SELECT * FROM `orders` JOIN `people` ON KEYS `orders`.person_id ORDER BY `orders`.name");
$query->consistency(\Couchbase\N1qlQuery::REQUEST_PLUS);
$query->crossBucket(true);

$res = $bucket->query($query);
// $res inludes rows from orders and people buckets
            

When executed, it will render the result from $res, which contains documents from both buckets (orders and people):

object(stdClass)#14 (5) {
  ["rows"]=>
  array(3) {
    [0]=>
    object(stdClass)#6 (2) {
      ["orders"]=>
      object(stdClass)#5 (2) {
        ["name"]=>
        string(10) "Green Soap"
        ["person_id"]=>
        string(4) "john"
      }
      ["people"]=>
      object(stdClass)#7 (2) {
        ["city"]=>
        string(8) "New York"
        ["name"]=>
        string(8) "John Doe"
      }
    }
    [1]=>
    object(stdClass)#9 (2) {
      ["orders"]=>
      object(stdClass)#8 (2) {
        ["name"]=>
        string(8) "Red Soap"
        ["person_id"]=>
        string(4) "jane"
      }
      ["people"]=>
      object(stdClass)#10 (2) {
        ["city"]=>
        string(5) "Miami"
        ["name"]=>
        string(8) "Jane Doe"
      }
    }
    [2]=>
    object(stdClass)#12 (2) {
      ["orders"]=>
      object(stdClass)#11 (2) {
        ["name"]=>
        string(7) "Rope 5m"
        ["person_id"]=>
        string(4) "john"
      }
      ["people"]=>
      object(stdClass)#13 (2) {
        ["city"]=>
        string(8) "New York"
        ["name"]=>
        string(8) "John Doe"
      }
    }
  }
  ["requestId"]=>
  string(36) "820416d5-6535-4519-be50-a2e9f03d0005"
  ["status"]=>
  string(7) "success"
  ["signature"]=>
  array(1) {
    ["*"]=>
    string(1) "*"
  }
  ["metrics"]=>
  array(5) {
    ["elapsedTime"]=>
    string(11) "21.529512ms"
    ["executionTime"]=>
    string(11) "21.425838ms"
    ["resultCount"]=>
    int(3)
    ["resultSize"]=>
    int(691)
    ["sortCount"]=>
    int(3)
  }
}