CODEWITHSUNDEEP
phpdatabaseaerospike
Furunomoe
Furunomoe

Reputation: 1404

AND and GROUP BY in Aerospike PHP Client

I am currently examining aerospike for replacing my company MySQL database. Currently, in MySQL, we have a table that stores the transaction data, the table looks like this :

+--------+------------+-----------+------------+-----+--------+
| trx_id | trx_date   | client_id | product_id | qty | total  |
+--------+------------+-----------+------------+-----+--------+
|      1 | 2015-01-01 |         1 |          1 | 100 | 100000 |
|      2 | 2015-01-02 |         2 |          2 | 200 | 200000 |
|      3 | 2015-01-03 |         3 |          3 | 300 | 300000 |
+--------+------------+-----------+------------+-----+--------+

For reporting, we usually do something like :

SELECT MONTH(trx_date), SUM(qty), SUM(total) FROM transaction WHERE client_id = 1 AND product_id = 1 GROUP BY MONTH(trx_date)

to get the monthly transaction data for a client.

I've read the documentation for the Aerospike PHP client and I don't seem to find anything similar to AND, GROUP BY, or MONTH.

So, in Aerospike PHP client, what is the recommended way to achieve something like that?

Thanks.

Upvotes: 2

Views: 405

Answers (1)

Ronen Botzer
Ronen Botzer

Reputation: 7117

Aerospike is a NoSQL key-value store, and as such you can't expect to use SQL with it. However, using Lua as the User-Defined Function (UDF) language, you can extend the basic functionality.

What you are looking for is an aggregation, applying a stream UDF to the results of a query.

There is an example of implementing a GROUP BY x HAVING in the PHP client's documentation for the aggregate() method. The thing to remember is that you want the secondary index query to eliminate as many records as you can, so that predicate should used for the 'WHERE', and the secondary filtering for the 'AND' should happen inside the stream UDF's filter on the smallest possible data set.

Reading the UDF Development Guide would also help.

Upvotes: 3

Related Questions