#community-help

Issues Sorting Multiple Fields with Sort_by in Typesense Query

TLDR Rishabh was having trouble sorting two fields separately with a single query. Jason explained that a secondary sorting will only occur if the first sort results in matching values. They clarified the functionality of sorting in various search engines.

Powered by Struct AI
grinning2
+11
Apr 18, 2021 (29 months ago)
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:18 PM
according to the docs sort_by can have upto 2 field for sorting but it doesn't seems to work. just to confirm both of the data type are int64. and both field work when only single sort is provided. what could be the reason ?
Jason
Photo of md5-8813087cccc512313602b6d9f9ece19f
Jason
10:20 PM
Could you share the exact curl command / code snippet and the output?
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:22 PM
https://example.com/typesense/collections/job/documents/search?per_page=15&query_by=jobTitle
&page=1&q=&sort_by=jobDeadline:asc,projectCreationTime:desc&filter_by=
10:22
Rishabh
10:22 PM
this is the url, both sort data are unix timestamp
Jason
Photo of md5-8813087cccc512313602b6d9f9ece19f
Jason
10:24 PM
The query seems fine... May I know what error you get from Typesense when you run it?
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:25 PM
I'm not getting any error, it's just the data is not sorted
10:25
Rishabh
10:25 PM
it sort the data when used only 1 field
10:29
Rishabh
10:29 PM
this is sample 50 records
10:29
Rishabh
10:29 PM
of this query domain/search?per_page=50&page=1&q=&sort_by=jobDeadline:asc,projectCreationTime:desc&query_by=jobTitle&include_fields=jobDeadline,projectCreationTime,id
Jason
Photo of md5-8813087cccc512313602b6d9f9ece19f
Jason
10:31 PM
May I know which records seem off to you in ordering?
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:31 PM
in this json you can se 1st and 2nd record
10:32
Rishabh
10:32 PM
here jobDeadline is in right order ie. asc as sent in query
10:32
Rishabh
10:32 PM
but projectCreationTime is not in desc
Jason
Photo of md5-8813087cccc512313602b6d9f9ece19f
Jason
10:32 PM
I eye-balled the first few and it seems like they're all ordered by jobDeadline:asc . I don't see any overlapping jobDeadline values for the projectCreationTime:desc to kick-in though
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:34 PM
jobDeadline is sorted but projectCreationTimes doesn't seems working.
Jason
Photo of md5-8813087cccc512313602b6d9f9ece19f
Jason
10:34 PM
Essentially, when multiple sort fields are used, the data is sorted by the first field, then if the first field has the same value across records, then the 2nd field is used to sort within those. This is very similar to how sorting multiple columns in a spreadsheet would work for eg
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:36 PM
you mean after 1st sort, if it founds same value in multiple record then 2rd fielf of sort will trigger
10:37
Rishabh
10:37 PM
something like that
Jason
Photo of md5-8813087cccc512313602b6d9f9ece19f
Jason
10:37 PM
Correct
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:37 PM
okay, is there any way we can sort multiple field like i described
10:38
Rishabh
10:38 PM
meilisearch also doesn't support this. initially i though typesense do support this feature
10:39
Rishabh
10:39 PM
but typesense is way better than meilisearch
grinning2
Jason
Photo of md5-8813087cccc512313602b6d9f9ece19f
Jason
10:42 PM
> is there any way we can sort multiple field like i described
No, but I'm trying to visualize how this would work. For every record, there needs to be a single value to know which ordered position to put it in, right?
10:43
Jason
10:43 PM
Could you give me an example of say three records with some sample data for these two fields, and how you'd sort them in the way you've described?
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:44 PM
ok
Jason
Photo of md5-8813087cccc512313602b6d9f9ece19f
Jason
10:46 PM
> meilisearch also doesn't support this. initially i though typesense do support this feature
I don't think so either. But if you're referring to the comparison chart, what Meilisearch & Algolia do not support that Typesense supports, is support for dynamic sorting, as part of the search parameters. With Meilisearch & Algolia, you have to define the sort order upfront. With Typesense, you can sort on any integer fields on the fly at search time.
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:47 PM
right. I spent 4-5 day in meilisearch before discovering typesense
10:51
Rishabh
10:51 PM
[
{
"document": {
"id": "5bdb10bca52ece560e8a8a17",
"projectCreationTime": 10,
"jobDeadline": 50
}
},
{
"document": {
"id": "5bdb1278a52ece560e8a8a26",
"projectCreationTime": 15,
"jobDeadline": 40
}
},
{
"document": {
"id": "5bdb1235a52ece560e8a8a1c",
"projectCreationTime": 20,
"jobDeadline": 22
}
}]
10:51
Rishabh
10:51 PM
for eg. here projectCreationTime is in asc and jobDeadline is in dsc
Jason
Photo of md5-8813087cccc512313602b6d9f9ece19f
Jason
10:53 PM
How about this example. Let's say the unsorted dataset is this:

[{
    "document": {
        "id": "5bdb10bca52ece560e8a8a17",
        "jobDeadline": 10,
        "projectCreationTime": 1
    },
},
{
    "document": {
        "id": "5bdb1278a52ece560e8a8a26",
        "jobDeadline": 6,
        "projectCreationTime": 5
    },
},
{
    "document": {
        "id": "5bdb1235a52ece560e8a8a1c",
        "jobDeadline": 9,
        "projectCreationTime": 3
    },
}]

How would you sort by jobDeadline:asc,projectCreationTime:desc in the way you've described.
10:53
Jason
10:53 PM
If you were to do it by hand...
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:54 PM
maybe i think i'm overthinking. ideally what i'm thinking is not doable.
Jason
Photo of md5-8813087cccc512313602b6d9f9ece19f
Jason
10:55 PM
> ideally what i'm thinking is not doable.
I think so too, not just in Typesense - but in general, with sorting as a concept.

But I'm trying to see if I'm missing something obvious or the possibility of a new feature 🙂
Rishabh
Photo of md5-764f7656bad4000225d99911a7b5de42
Rishabh
10:55 PM
let me think of myself and i'll come up with a good example.
+11