douwei1921
2017-06-05 06:56
浏览 51
已采纳

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

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.

图片转代码服务由CSDN问答提供 功能建议

我正在将此驱动程序用于cassandra: https://github.com/datastax/php-driver

以下是我用来创建表的代码:< / p>

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

然后我创建一个物化视图:

  CREATE MATERIALIZED VIEW测试。  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); 
   
 
 

这里是我的PHP代码片段导致502坏网关: \ n

  $ session = $ clus  ter-> connect($ keyspace); 
 $ selectstmt = $ session-&gt; prepare(“SELECT suserid,susername,snickname,msg,savatar,timestamp FROM packages_by_userid WHERE ruserid IN(?,?)AND suserid IN(?  ,?)和时间戳&lt;  ?  LIMIT 40;“); 
 $ params = array('ruserid'=&gt; array($ rid,$ sid),'suserid'=&gt; array($ rid,$ sid),'timestamp'=&gt; $  endtimestamp); 
 $ options = array('arguments'=&gt; $ params); 
 $ future = $ session-&gt; executeAsync($ selectstmt,$ options); 
 $ result = $ future-&gt; get  (); 
   
 
 

我相信我搞砸了将参数绑定到准备好的语句。在我的情况下,这样做的正确方法是什么,因为我必须绑定更多 ruserid suserid 的一个值?

感谢任何可以提供帮助的人。

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duanchun2349 2017-06-05 07:10
    已采纳

    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.

    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题