Search based on value between two fields

[btaskew]

Hi there,

Apologies, not really an issue but hoping to seek some help.

My use case is I'm storing events with startTime and endTime as Unix values in numeric fields. I need to be able to search for events where a given time is between the startTime and endTime fields so that I can find all events in progress at the given time.

I'm aware you can search where a single field is between two given values, I guess I need the reverse where a single given value is between two fields.

Is this something that is at all possible? Or is there anyway I can mimic this behaviour using or queries?

Any advice would be most appreciated! :)

[MacFJA]

To do what you want you need need to have 2 criteria: one for the startTime and one for the endTime.

---

Let say we have this setup:

FT.CREATE event ON HASH PREFIX 1 e SCHEMA startTime NUMERIC endTime NUMERIC name TEXT

And this data:

HSET e1 startTime 1 endTime 50 name "First event"
HSET e2 startTime 40 endTime 60 name "Second event"
HSET e3 startTime 30 endTime 40 name "Three times"
HSET e4 startTime 20 endTime 50 name "Unstoppable !"
HSET e5 startTime 10 endTime 25 name "Recurring !"

If we want to all event that are running at 35, the RediSearch query will be:

FT.SEARCH event "@startTime:[-inf 35] @endTime:[35 inf]"

Which can be translated into the following PHP code:

use MacFJA\RediSearch\Query\Builder;
use MacFJA\RediSearch\Query\Builder\NumericFacet;
use MacFJA\RediSearch\Redis\Command\Search;

$client = /* ... */;

$queryBuilder = new Builder();
$query = $queryBuilder
    ->addElement(NumericFacet::lessThan('startTime', 35))
    ->addElement(NumericFacet::greaterThan('endTime', 35))
    ->render();

$search = new Search();

$search
    ->setIndex('event')
    ->setQuery($query);
$results = $client->execute($search);

---

I hope that answer your question.