FT.AGGREGATE SORTBY syntax help

[Paul]

Hello,

I would like to move from FT.SEARCH to FT.AGGREGATE, and I don't seem to be able to get the SORTBY syntax correct, as the results always appear to be sorted by the first SORTABLE field.

Working version of FT.SEARCH:

FT.SEARCH clients_hash_index_2 "*" SORTBY @LastName ASC LIMIT 0 10

FT.AGGREGATE where SORTBY seems to be ignored:

FT.AGGREGATE clients_hash_index_2 "*" SORTBY 4 @LastName ASC @FirstName ASC LIMIT 0 10 APPLY @ClientID as ClientID

Here is the index:

FT.INFO clients_hash_index_2

1. index_name
2. clients_hash_index_2
3. index_options
4. (empty list or set)
5. fields
6. 1. 1. ClientID
      2. type
      3. NUMERIC
      4. SORTABLE
   2. 1. FirstName
      2. type
      3. TEXT
      4. WEIGHT
      5. "1"
      6. SORTABLE
   3. 1. LastName
      2. type
      3. TEXT
      4. WEIGHT
      5. "1"
      6. SORTABLE

Any ideas what I am missing?

Thanks!

[Kyle Davis]

Hi Paul,

I'm able to successfully sort on two sortable fields independently. Can you give a short cross section of data that shows your issue?

Thanks!

Kyle

[Paul]

Hi Kyle,

I have no doubt that I am missing something, maybe you could easily spot it from the syntax below:

FT.CREATE "users_hash_index_2" NOSCOREIDX SCHEMA "UserID" NUMERIC SORTABLE "UserName" TEXT SORTABLE "FirstName" TEXT SORTABLE "LastName" TEXT SORTABLE

FT.ADD users_hash_index_2 1 1 REPLACE FIELDS "UserID" 1 "UserName" "joe" "FirstName" "Joe" "LastName" "Smith"

FT.ADD users_hash_index_2 3 1 REPLACE FIELDS "UserID" 3 "UserName" "angela" "FirstName" "Angela" "LastName" "Doe"

FT.ADD users_hash_index_2 2 1 REPLACE FIELDS "UserID" 2 "UserName" "don" "FirstName" "Don" "LastName" "May"

Ascending sort by the LastName - so I would expect this to return 3,2,1

FT.AGGREGATE "users_hash_index_2" "*" "SORTBY" "2" "@LastName" "ASC" "APPLY" "@UserID" "AS" "UserID" "LIMIT" "0" "10"
1) (integer) 3
2) 1) "UserID"
   2) "1"
3) 1) "UserID"
   2) "3"
4) 1) "UserID"
   2) "2"

Descending sort by the LastName - so I would expect this to return 1,2,3
FT.AGGREGATE "users_hash_index_2" "*" "SORTBY" "2" "@LastName" "DESC" "APPLY" "@UserID" "AS" "UserID" "LIMIT" "0" "10"
1) (integer) 3
2) 1) "UserID"
   2) "2"
3) 1) "UserID"
   2) "3"
4) 1) "UserID"
   2) "1"

Based on what I am seeing it actually looks like it is returning the records in ascending or descending order of how they were entered, completely ignoring the fields I want to sort by.

Any help is appreciated!

Thank you!

[Dvir Volk]

It could be an issue of lower/upper case.

Could you try that with the field names in all lowercase?

--
You received this message because you are subscribed to the Google Groups "redisearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to redisearch+...@googlegroups.com.
To post to this group, send email to redis...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/redisearch/d3636a71-6dd2-4bda-b239-1045562ce745%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kyle Davis]

Hey Paul-

1) `NOSCOREIDX` was deprecated and now has been removed (https://github.com/RedisLabsModules/RediSearch/issues/149), although I don't think it really matters here - more of an FYI

2)  I believe the general problem here is that the 'LastName' isn't in brought into the aggregation pipeline before `SORTBY`. You need to do this via `APPLY` or `LOAD` and the sorting will be correct.

Works: `FT.AGGREGATE users_hash_index_2 * APPLY @UserID as UserID APPLY @LastName as LastName SORTBY 2 @LastName asc` 

Works: `FT.AGGREGATE users_hash_index_2 * APPLY @UserID as UserID APPLY @LastName as LastName SORTBY 2 @LastName desc`

The side effect is that you're also returning the LastName.

Also, tried Dvir's suggestion and lowercasing doesn't seem to help.

Thanks 

-Kyle

[Dvir Volk]

If that is the case it's likely a bug. You don't need apply for sorting by design. Of course my design itself may have changed but it should not be necessary unless for return purposes. 

--

You received this message because you are subscribed to the Google Groups "redisearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to redisearch+...@googlegroups.com.
To post to this group, send email to redis...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/redisearch/1e093aff-4fc9-45eb-89be-e3f703c5327a%40googlegroups.com.

[Paul]

Thank you Kyle, I appreciate your help! 

Adding the APPLY for all of the fields I want to include in the sort did the trick. One other thing I was missing was that the SORTBY needs to be after the APPLY otherwise it will not sort.

[Paul]

Hi Dvir,

I even tried changing the schema to be all lower case, but unless I use APPLY for each field I want to sort on, the SORTBY does not appear to work.

Another interesting problem I am seeing is that SORTBY needs to come after the APPLY, otherwise the SORTBY parameters will be ignored, unless this is done by design.

Thank you,

Paul