Cassandra PHP驱动程序502 badgateway来自准备好的声明

Question

I'm using this driver for cassandra: https://github.com/datastax/php-driver

Here's the code I used to create the table:

CREATE TABLE test.packages (
     packageuuid timeuuid, 
     ruserid text, 
     suserid text, 
     timestamp int, 
     PRIMARY KEY (ruserid, suserid, packageuuid, timestamp)
);

and then I create a materialized view:

CREATE MATERIALIZED VIEW test.packages_by_userid 
    AS SELECT * FROM test.packages 
    WHERE ruserid IS NOT NULL 
       AND suserid IS NOT NULL 
       AND TIMESTAMP IS NOT NULL 
       AND packageuuid IS NOT NULL 
    PRIMARY KEY (ruserid, suserid, timestamp, packageuuid) 
    WITH CLUSTERING ORDER BY (packageuuid DESC);

and here's a snippet of my PHP code which causes 502 bad gateway:

$session   = $cluster->connect($keyspace);
$selectstmt = $session->prepare("SELECT suserid, susername, snickname, msg, savatar, timestamp FROM packages_by_userid WHERE ruserid IN (?, ?) AND suserid IN (?, ?) AND timestamp < ? LIMIT 40;");
$params = array('ruserid' => array($rid, $sid), 'suserid' => array($rid, $sid), 'timestamp' => $endtimestamp);
$options = array('arguments' => $params);
$future    = $session->executeAsync($selectstmt, $options);
$result    = $future->get();

I believe I messed up with binding the parameters to the prepared statement. What's the proper way to do that in my case as I have to bind more than one value to ruserid and suserid?

Thanks to anyone who can help.

Answer 1

I suggest you to change your data model like below :

CREATE TABLE packages (
    cnvid text,
    packageuuid timeuuid,
    ruserid text,
    suserid text,
    PRIMARY KEY (cnvid, packageuuid)
);

Here cnvid is the conversation id. You can make the conversation id using the below function :

 function makeConversationId($ruserid, $suserid ) {
    return $ruserid <= $suserid ? $ruserid . ':' . $suserid : $suserid . ':' . $ruserid ;
 }

Whoever the sender or receiver your conversation id will be same. i.e

 echo makeConversationId('1', '2') . '<br/>';
 echo makeConversationId('2', '1'); 

Output :

1:2
1:2

Now you have the cnvid, whenever you insert/update/delete/select use the above method to make cnvid.

And though packageuuid is the timeuuid, all your packageuuid will be sorted by time.

So to query first create the cnvid (i.e 1:2) then create a timeuuid with the timestamp for which you want to query (i.e 896f8110-49d4-11e7-ade6-493d3332b999)

Check this : https://datastax.github.io/php-driver/api/Cassandra/class.Timeuuid/

So you can query to get packages of user between 1 and 2 whose timestamp greater than the given timestamp :

Example Data :

 cnvid | packageuuid                          | ruserid | suserid
-------+--------------------------------------+---------+---------
   3:4 | bc0809e0-49d3-11e7-ade6-493d3332b999 |       3 |       4
   1:2 | 1f4aae70-49d1-11e7-ade6-493d3332b999 |       1 |       2
   1:2 | 237ff3b0-49d1-11e7-ade6-493d3332b999 |       2 |       1

Query :

SELECT * FROM packages WHERE cnvid = '1:2' AND packageuuid < 896f8110-49d4-11e7-ade6-493d3332b999;

Output :

 cnvid | packageuuid                          | ruserid | suserid
-------+--------------------------------------+---------+---------
   1:2 | 1f4aae70-49d1-11e7-ade6-493d3332b999 |       1 |       2
   1:2 | 237ff3b0-49d1-11e7-ade6-493d3332b999 |       2 |       1

So you don't need the Materialized View and In query.