PHP SDK: Running query against 2 buckets

PHP SDK
1

Hi,

I’m new to Couchbase and trying to run a query against 2 buckets. I have built a query which looks like:

select {columns_list} from {bucket1}
UNION 
select {columns_list} from {bucket2}

Real query looks much bigger but the question is about accessing two buckets within n1ql query from PHP SDK.

My query works fine in the management GUI and command line interface. But I have issues with PHP SDK.

Here is what I do:

$cluster = new CouchbaseCluster($config['hostname'], $config['username'], $config['password']);
$bucket = $cluster->openBucket($bucket_name, $bucket_pass);
$bucket->query(CouchbaseN1qlQuery::fromString($n1ql))->rows;

I was initially running Couchbase 4.1.0.xxx and I was getting random “Idle connection expired” error. Which means sometimes it worked fine.

After upgrading to 4.5.1 I’m getting

Failed to perform N1QL query. 401: {
    "requestID": "899df7...6a",
    "signature": {
        "campaign-id": "json",
        "post_cnt": "number",
        "red_cnt": "number"
    },
    "results": [
    ],
    "errors": [
        {
            "code": 10000,
            "msg": "Authorization Failed Keyspace bucket_2 - cause: Authentication failure."
        }
    ],
    "status": "stopped",
    "metrics": {
        "elapsedTime": "42.49382ms",
        "executionTime": "42.4334ms",
        "re. I=0x5594e68e5260

It fails authorising the second bucket though for creating a cluster I’m using Administrator user.

I really can’t find a way to run my union query. Is it really limitation of PHP SDK?

Any help will be greatly appreciated.

2

You could pass your credentials directly to query:

<?php

$cluster = new CouchbaseCluster('couchbase://localhost');
$bucket = $cluster->openBucket('test1', 'secret1');
$query = CouchbaseN1qlQuery::fromString('SELECT * FROM `test1` UNION SELECT * FROM `test2`');
$query->options['creds'] = [
    ['user' => 'test1', 'pass' => 'secret1'],
    ['user' => 'test2', 'pass' => 'secret2']
];
$res = $bucket->query($query);
1 Like
3

Thanks Sergey! You saved me so much time! It works.

As a newbie I wonder if it is recommended to use multi bucket queries or combine buckets in one and use simple queries? What pros and cons?

4

it really depends on your application and update rates of your data. For example if one dataset is almost static and large, and the second one is small but very volatile, it make sense to split them in the buckets so that the indexes won’t need to be rebuilt for first dataset each time you change documents in the second.

1 Like
5

Just note to my previous post. From 2.3.0 version of the library, it is recommended to avoid direct access to options, and use \Couchbase\Authenticator + \Couchbase\N1qlQuery::crossBucket() methods to make such queries. The documentation with example is here:

http://docs.couchbase.com/sdk-api/couchbase-php-client-2.3.0/classes/Couchbase.N1qlQuery.html#method_crossBucket