Hi everyone! I am having some trouble sorting on joined nested fields. I would like to rerank my search results for personalized search by joining with a score field from another collection, but I am not fully understanding query syntax. Here is a minimal example of the kind of joins I am performing. Collections and corresponding schemas:

stores_schema = {
    "name": "stores",
    "fields": [
        {"name": "store_id", "type": "int32"},
    ],
}

users_schema = {
    "name": "users",
    "fields": [
        {"name": "user_id", "type": "int32"},
    ],
}

interaction_score_schema = {
    "name": "interaction_scores",
    "fields": [
        {"name": "store_id", "type": "int32", "reference": "stores.store_id"},
        {"name": "user_id", "type": "int32", "reference": "users.user_id"},
        {"name": "score", "type": "float", "sort": True},
    ],
}

items_schema = {
    "name": "items",
    "fields": [
        {"name": "item_id", "type": "string"},
        {"name": "name", "type": "string"},
        {"name": "store_id", "type": "int32", "reference": "stores.store_id"},
    ],
}

Basic queries I am trying to run:

search_parameters = {
    "q": "*",
    "per_page": 10,
    "filter_by": "$stores($interaction_scores($users(user_id:=2)))",
    "sort_by": "$interaction_scores(score:desc)",
}

results = client.collections["items"].documents.search(search_parameters)

search_parameters = {
    "q": "*",
    "per_page": 10,
    "filter_by": "$stores($interaction_scores($users(user_id:=2)))",
    "sort_by": "$stores($interaction_scores(score:desc))",
}

Both yield the same error:

RequestMalformed: [Errno 400] No references found to sort by on interaction_scores.score

Removing the

sort_by

clause returns joined search results:

{'facet_counts': [],
 'found': 10,
 'hits': [{'document': {'id': '9',
    'item_id': '10',
    'name': '10',
    'store_id': 5,
    'stores': {'id': '4',
     'interaction_scores': {'id': '9',
      'score': 0.5,
      'store_id': 5,
      'user_id': 2,
      'users': {'id': '1', 'user_id': 2}},
     'store_id': 5}},
   'highlight': {},
   'highlights': []},
  {'document': {'id': '8',
    'item_id': '8',
    'name': '8',
    'store_id': 4,
    'stores': {'id': '3',
     'interaction_scores': {'id': '8',
      'score': 0.4,
      'store_id': 4,
      'user_id': 2,
      'users': {'id': '1', 'user_id': 2}},
     'store_id': 4}},
   'highlight': {},
   'highlights': []},

...

CC: @Harpreet Sangar

@Darya can you try again with

"sort_by": "$stores($interaction_scores(score:desc))"

@Harpreet Sangar I get the same error,

RequestMalformed: [Errno 400] No references found to sort by on interaction_scores.score

(on

27.1

) After searching for other threads related to JOINs and upgrading to

28.0.rc37

, the

sort_by

clause strated working