How to retrieve composite column from Cassandra table in PHP

Question

I have a CassandraHandler that retrieves the queries in rows

class CassandraHandler
{
    private $keyspace = 'blabla'; //default is oyvent
    private $cluster = NULL;
    private $session = NULL;

    function __construct(){
        $this->cluster   =   \Cassandra::cluster()
            ->build();       // connects to localhost by default
        $this->session   = $this->cluster->connect($this->keyspace);
    }

    /**
     * @return  Rows
     */
    public function execute($query){
        $statement = new \Cassandra\SimpleStatement($query);
        $result    = $this->session->execute($statement);  
        return $result;
    }
}

When I use for normal columns it's fine but I can't get my photo column in php

I created the column like this

photos frozen<set<map<text,text>>>

my json example

{{"urllarge": "1.jpg", "urlmedium": "2.jpg"},
 {"urllarge": "3.jpg", "urlmedium": "4.jpg"}}

And here how can I use PHP to retrieve the composite columns?

$cassandraHandler = new CassandraHandlerClass(); 
 $rows = $cassandraHandler->fetchLatestPosts($placeids, $limit);

      foreach ($rows as $row) {
          $tmp = array();
          $tmp["userid"] = doubleval($row["userid"]);
          $tmp["fullname"] = $row["fullname"];
          $tmp["photos"] = $row["photos"]  //????????
       }

I know there is this documentation of the PHP driver https://github.com/datastax/php-driver

But I am a little confused.. I just need to get the json value like I get in cqlsh

Answer 1

You have two options to convert the composites into useable JSON:

1. Create a function to convert the deserialized/unmarshalled objects into JSON.
2. Retrieve the values from Cassandra as JSON.

Here is an example that demonstrates both options:

<?php

$KEYSPACE_NAME = "stackoverflow";
$TABLE_NAME = "retrieve_composites";

function print_rows_as_json($rows) {
    foreach ($rows as $row) {
        $set_count = 0;
        echo "{\"photos\": [";
        foreach ($photos = $row["photos"] as $photo) {
            $map_count = 0;
            echo "{";
            foreach ($photo as $key => $value) {
                echo "\"{$key}\": \"{$value}\"";
                if (++$map_count < count($photo)) {
                    echo ", ";
                }
            }
            echo "}";
            if (++$set_count < count($photos)) {
                echo ", ";
            }
        }
        echo "]}" . PHP_EOL;
    }
}

// Override default localhost contact point
$contact_points = "127.0.0.1";
if (php_sapi_name() == "cli") {
    if (count($_SERVER['argv']) > 1) {
        $contact_points = $_SERVER['argv'][1];
    }
}

// Connect to the cluster
$cluster = Cassandra::cluster()
    ->withContactPoints($contact_points)
    ->build();
$session = $cluster->connect();

// Create the keypspace (drop if exists) and table
$session->execute("DROP KEYSPACE IF EXISTS {$KEYSPACE_NAME}");
$session->execute("CREATE KEYSPACE {$KEYSPACE_NAME} WITH replication = "
    . "{ 'class': 'SimpleStrategy', 'replication_factor': 1 }"
);
$session->execute("CREATE TABLE ${KEYSPACE_NAME}.{$TABLE_NAME} ( "
    . "id int PRIMARY KEY, "
    . "photos frozen<set<map<text, text>>> )"
);

// Create a multiple rows to retrieve
$session->execute("INSERT INTO ${KEYSPACE_NAME}.{$TABLE_NAME} (id, photos) VALUES ( "
    . "1, "
    . "{{'urllage': '1.jpg', 'urlmedium': '2.jpg'}, "
    . "{'urllage': '3.jpg', 'urlmedium': '4.jpg'}}"
    . ")");
$session->execute("INSERT INTO ${KEYSPACE_NAME}.{$TABLE_NAME} (id, photos) VALUES ( "
    . "2, "
    . "{{'urllage': '21.jpg', 'urlmedium': '22.jpg'}, "
    . "{'urllage': '23.jpg', 'urlmedium': '24.jpg'}}"
    . ")");

// Select and print the unmarshalled data as JSON
$rows = $session->execute("SELECT photos FROM ${KEYSPACE_NAME}.{$TABLE_NAME}");
print_rows_as_json($rows);

// Select the data as JSON and print the string
$rows = $session->execute("SELECT JSON photos FROM ${KEYSPACE_NAME}.{$TABLE_NAME}");
foreach ($rows as $row) {
    echo $row["[json]"] . PHP_EOL;
}

From the above example you can see that selecting the data as JSON involves less code for your application while also moving the processing onto the server. This probably the preferred choice for your application needs.

NOTE: This example is using v1.3.0 of the DataStax PHP driver which added support to pass a query strings directly to Session::execute() and Session::executeAsync(). If you are using an earlier version you will need to convert all query strings to Cassandra\Statement objects before passing to $session->execute(...).