# A filter similar in sql to \`where (a,b,c) \> (1,2,3)\`

**URL:** <https://forums.foundationdb.org/t/a-filter-similar-in-sql-to-where-a-b-c-1-2-3/4464>\
**Category:** Using Layers\
**Created:** [May 9, 2024, 6:35am UTC](https://forums.foundationdb.org/t/a-filter-similar-in-sql-to-where-a-b-c-1-2-3/4464 "2024-05-09T06:35:42Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dispalt](https://sea1.discourse-cdn.com/foundationdb/user_avatar/forums.foundationdb.org/dispalt/32/1492_2.png) [@dispalt](https://forums.foundationdb.org/u/dispalt)\
**Post date:** [May 9, 2024, 6:35am UTC](https://forums.foundationdb.org/t/a-filter-similar-in-sql-to-where-a-b-c-1-2-3/4464/1 "2024-05-09T06:35:42Z")

</div>

Looking at the record layer, it’s unclear to me how I’d go about constructing that, I was kinda thinking I could use a Query.keyExpression, but that looks like it’s reserved only for functions (practically), I kinda assumed I could do `Query.concatentateFields("a", "b", "c").greaterThan(Tuple.from(1,2,3))`

I tried looking through the tests, but didn’t find anything. I guess I could create a function `Quey.keyExpression(function("tupleize", fields("a", "b", "c")`, but I assume that would mean a table scan? `a`, `b`, `c` are my primary key

---

<div class="post-metadata">

**Author:** ![alloc](https://sea1.discourse-cdn.com/foundationdb/user_avatar/forums.foundationdb.org/alloc/32/9_2.png) [@alloc](https://forums.foundationdb.org/u/alloc)\
**Post date:** [May 10, 2024, 10:12am UTC](https://forums.foundationdb.org/t/a-filter-similar-in-sql-to-where-a-b-c-1-2-3/4464/2 "2024-05-10T10:12:44Z")

</div>

Yeah, unfortunately, there’s not a single function for that. In theory, it’s logically equivalent to something like:

```java
Query.or(
    Query.and(Query.field("a").equalsValue(1), Query.field("b").equalsValue(2), Query.field("c").greaterThan(3)),
    Query.and(Query.field("a").equalsValue(1), Query.field("b").greaterThan(2)),
    Query.field("a").greaterThan(1)
)

```

But, (1) that’s a bit unwieldy and (2) the planner isn’t quite smart to notice that if you have an index on `concatenateFields("a", "b", "c")`, the given ranges can be used to turn that into a single scan.

If you did introduce a `tupleize` function and made it a `QueryableKeyExpression`, then you certainly could index the result of that function. Assuming the index exists, the planner should chose it for you with something like the expression you suggested.

It’s a bit unsatsifying, but I think the most straightforward way to get this to work is actually to forego the planner and just issue an index scan directly. Something like:

```java
recordStore.scanIndexRecords(
    indexName,
    IndexScanType.BY_VALUE,
    new TupleRange(Tuple.from(1, 2, 3), null, EndpointType.RANGE_EXCLUSIVE, EndpointType.TREE_END),
    continuation // null if starting from the beginning; previous run's continuation if resuming a scan
    scanProperties // specifies forward/reverse as well as limits
);

```

If you know you have the index you want, this can be simpler than trying to get the planner to produce the thing you want.

---

<div class="post-metadata">

**Author:** ![dispalt](https://sea1.discourse-cdn.com/foundationdb/user_avatar/forums.foundationdb.org/dispalt/32/1492_2.png) [@dispalt](https://forums.foundationdb.org/u/dispalt)\
**Post date:** [May 10, 2024, 7:59pm UTC](https://forums.foundationdb.org/t/a-filter-similar-in-sql-to-where-a-b-c-1-2-3/4464/3 "2024-05-10T19:59:07Z")

</div>

Yep I came to the exact same conclusion! I should have responded to myself.

This is quite an impressive piece of technology and thank you for the help!