Hello <@U01PL2YSG8L> <@U01NZ4D0LDD> <@U076GF3T0HY>...
# community-help
u
Hello @Kishore Nallan @Jason Bosco @Fanis Tharropoulos @Alan Martini Need some help in JOINs. I have 2 collections in typesense v28.0 1. documents 2. companies • Both have one-to-many relationship between them. • So, one document from "documents" collection can be joined to multiple documents of "companies" collection. • Now, user from a specific company (lets say, companyId = 4) perform a search in "documents" collections. ◦ q = "Microsoft" ◦ query_by = "name" ◦ collection_name = documents • SO, WHAT I WANT IN RESULTS IS... ◦ Matching results from "documents" collection... PLUS... ◦ JOINED field from "companies" collection based on the companyId. ◦ One Important point here is, there can be a one or zero document in "companies" collection for a specific tenderId and companyId. So, "company" in the output must be an object with or without data as shown in the given example. ◦ So, what should be my other search parameters with q and query_by to achieve this type of search response? ◦ Example of search result,
Copy code
{
  "found": 2,
  "hits": [
    {
      "document": {
        "id": "215675",
        "name": "Microsoft Lizenzen",
        "company": {}
      }
    }
  ],
  "out_of": 5,
  "page": 1,
  "request_params": {
    "collection_name": "documents",
    "first_q": "Microsoft",
    "per_page": 10,
    "q": "*"
  }
}
Here is schema and sample data for both the collection 1. Schema of "documents" collection.
Copy code
{
  "name": "documents",
  "enable_nested_fields": true,
  "fields": [
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "name",
      "optional": false,
      "sort": true,
      "stem": false,
      "type": "string"
    }
  ]
}
2. Schema of "companies" collection
Copy code
{
  "name": "companies",
  "enable_nested_fields": true,
  "fields": [
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "documentId",
      "optional": false,
      "sort": true,
      "stem": false,
      "type": "string",
      "reference": "documents.id"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "companyId",
      "optional": false,
      "sort": true,
      "stem": false,
      "type": "int64"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "groupCompanyId",
      "optional": true,
      "sort": true,
      "stem": false,
      "type": "int64"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "assignedUserId",
      "optional": true,
      "sort": true,
      "stem": false,
      "type": "int64"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "status",
      "optional": true,
      "sort": true,
      "stem": false,
      "type": "string"
    },
    {
      "facet": false,
      "index": true,
      "infix": false,
      "locale": "",
      "name": "note",
      "optional": true,
      "sort": true,
      "stem": false,
      "type": "string"
    }
  ]
}
Sample data for "documents" collection:
Copy code
[
   {
      "id":"215679",
      "name":"SEZ_übergeordnete Baustelleneinrichtung"
   },
   {
      "id":"215678",
      "name":"KARLA Verwaltungs- und Bildungszentrum - Stahlblechtüren, Toranlagen und Brandschutzschiebetore II"
   },
   {
      "id":"215677",
      "name":"Microsoft Lizenzen der Bezirkskliniken Mittelfranken"
   },
   {
      "id":"215676",
      "name":"Verlängerung der Citrix Lizenzen der Bezirkskliniken Mittelfranken"
   },
   {
      "id":"215675",
      "name":"Microsoft Lizenzen"
   }
]
Sample data for "companies" collection:
Copy code
[
   {
      "documentId":"215679",
      "companyId":4,
      "groupCompanyId":"None",
      "assignedUserId":502,
      "status":"None",
      "note":"None"
   },
   {
      "documentId":"215679",
      "companyId":2,
      "groupCompanyId":"None",
      "assignedUserId":2,
      "status":"Pending",
      "note":"None"
   },
   {
      "documentId":"215678",
      "companyId":3,
      "groupCompanyId":"None",
      "assignedUserId":"None",
      "status":"Rejected",
      "note":"None"
   },
   {
      "documentId":"215678",
      "companyId":2,
      "groupCompanyId":"None",
      "assignedUserId":"None",
      "status":"Confirmed",
      "note":"None"
   },
   {
      "documentId":"215677",
      "companyId":4,
      "groupCompanyId":"None",
      "assignedUserId":"None",
      "status":"None",
      "note":"Test Note"
   },
   {
      "documentId":"215676",
      "companyId":3,
      "groupCompanyId":"None",
      "assignedUserId":5727,
      "status":"None",
      "note":"None"
   },
   {
      "documentId":"215675",
      "companyId":5,
      "groupCompanyId":"None",
      "assignedUserId":"None",
      "status":"Confirmed",
      "note":"None"
   }
]
f
You can't query by joined fields. You can only search on one of the two collections, and the joined fields will be automatically retrieved