Hi, I am trying to do something exactly like the custom pricing example here https://typesense.org/docs/27.1/api/joins.html#specialized-example. However, I also need to be able to sort by this custom pricing field. For example, I have a

basePrice

for each product in the

products

collection. If there is no corresponding custom price for a product for a particular customer, I'd like to use the

basePrice

in the

products

collection as the value, otherwise, use the custom price from the joined collection. I'm having trouble trying to build this particular query. Is it possible to achieve something like this without resorting to populating the base price as the custom price for each products for every customers in the custom pricing collection? Would really appreciate some help and recommendations, thank you!

https://typesense.org/docs/27.1/api/joins.html#left-join You'd apply a left join on that and on the client, check for the value on each product, otherwise use the basePrice that's included in the product document

Hi @Fanis Tharropoulos - I had to work on something else, sorry for the late reply and I appreciate the help, thanks! I was hoping for a server-side sort taking into consideration both fields. It probably won't happen like 99% of the time, but I'm worried about sorting affecting the results for a page (which probably won't matter much if it happens anyway). So far, I now tried overriding the price field with strategy merge with price having the same name to override the base price value, but it seems to retain the sort based on the original price value. So I'm getting something like this if for example, "product a" has its original price of 3.52 overridden (sort by price descending):

{id: 1, name: 'product a', price: 2.80},
{id: 2, name: 'product b', price: 3.21},
{id: 3, name: 'product c', price: 1.95}

I'll likely go with something like this and resort to sorting client side.