Multiple filters query problem

Feature Discussion Query & Indexing
1

I got a problem and stuck.

+----------+-----------+------+--------------+---------------+---------------+
| from| to | type | data         | timestamp     | uid           |
+----------+-----------+------+--------------+---------------+---------------+
| "a" | "c" | "m"  | "hi, he llo" | 1453286107693 | 1453286036182 |
| "a" | "b" | "m"  | "hi, he llo" | 1453286068605 | 1453286036183 |
| "b" | "c" | "m"  | "hi, he llo" | 1453286068605 | 1453286036184 |
| "a" | "b" | "m"  | "hi, he llo" | 1453286068605 | 1453286036184 |
| "b" | "c" | "m"  | "hi, he llo" | 1453286068605 | 1453286036184 |
+----------+-----------+------+--------------+---------------+---------------+

How can I run query to get data from above table where from “a” and to “b” or from “b” and to “a”.

We tried SELECT * FROM test.message_data WHERE (from=“a” AND to=“b”) OR (from=“b” AND to=“a”)

It’s only filtering for SELECT * FROM test.message_data WHERE from=“a”. Is there way to do it?

2

Yes, it is possible but you need to use a stream UDF for the second/third/… WHERE-clause. You can choose the most selective condition as your WHERE and implement any further filtering through server-side UDFs.

http://www.aerospike.com/launchpad/query_multiple_filters.html

3

@ManuelSchmidt would you pls write the query for me? because I’m very new in aerospike.

4

What client (language) are you using? I’ll see if I manage to create a working example for you this week.

5

@ManuelSchmidt I’m using nodejs. please sir I need badly.

6

Here’s a piece of code I’d written for Java. Hope you can convert it according to your needs.

  1. The Java File : Aerospike/AverageDAO.java at master · LeloucH-Zer0/Aerospike · GitHub

You can go through the comments in the file to understand the inputs needed.

  1. The Lua File : Aerospike/genericUdf.lua at master · LeloucH-Zer0/Aerospike · GitHub

I believe you can use this code without making any changes whatsoever.

I had designed the thing to be as generic as possible. Hope this helps :slightly_smiling: . Feel free to respond on this thread if you need any clarifications.

2 Likes
7

You would first query for records where from = val1 and apply the following stream UDF on the matched records

local function from_to_filter(val1, val2)
  return function(rec)
    if (rec['from'] == val1 and rec['to'] == val2) or
       (rec['from'] == val2 and rec['to'] == val1) then
      return true
    end
    return false
  end
end

local function map_record(rec)
  local ret = map()
  for i, bin_name in ipairs(record.bin_names(rec)) do
    ret[bin_name] = rec[bin_name]
  end
  return ret
end

function from_to_match(stream, val1, val2)
  return stream : filter(from_to_filter(val1, val2)) : map(map_record)
end