How to search across objects by attributes of secondary object, linked to first object via primary key?

Hello, I have two types of objects, where the ID of one object is a field in the other object. For example:

order1 = {'orderId': '1', 'customerId': '123', 'otherField': 'abcdefg'}
order2 = {'orderId': '2', 'customerId': '124', 'otherField': 'abcdefg'}
order3 = {'orderId': '3', 'customerId': '125', 'otherField': 'abcdefg'}
customer1 = {'customerId': '123', 'name': 'Bob Customer'}
customer2 = {'customerId': '124', 'name': 'Bobby Customer'}
customer3 = {'customerId': '125', 'name': 'Tim Customer'}

I want to search by the “name” field of the customer object, and return a list of all orders made by customers whose names contain the query string.
For example, with the data above, if I search “Bob,” the results would contain order1 and order2.

What is the best way to perform this search with algolia? Customers could number in the 100 thousands, and orders could number in the millions.

Right now I am thinking of performing this as two searches, a search across customers and then a search across orders with the resulting list of customerIds, but this seems problematic, as at times the intermediate list of customerIds could exceed 1000, the max value of “maxValuesPerFacet.”

Is there a better way to do this in algolia? Or if not, is there a way to make my above approach work for larger intermediate sets of customerIds?

Thanks

Hi @xa9948 , welcome to the forum!

If I understand correctly, you’d like to be able to search through an orders dataset, but by the customer name.

Algolia isn’t a database so there isn’t a way to create relationships or join tables between different indices, so it would be best to include any fields you want to be searchable directly on records themselves.

It seems you’re already including the customerId on the order records – is there any reason to also not include the customerName on order records so that it’s easily searchable? Though it would increase the data size of each record, this would be the most efficient way to search here.