I have a product listing service, and it will give a list of products for the logged-in users, and the list is paginated, with ten products per page. I implemented this pagination using MySQL LIMIT query.

Recently I got a requirement to integrate it with a relevancy service; this service will give product attributes relevant to the currently logged-in user. I need to sort the products based on relevancy to the user.

var page_no = 0;
var url = "https://relevancy-service-url"
var query = "SELECT * FROM products LIMIT 10 OFFSET page_no;"
var product_list = query -> fetch
var relevent_attributes = api -> get(url, user_id)

here relevent_attributes is an array containing items in the following format:

[attribite_1 : 40, attribute_2: 20, attribute_3: 10, attribute_4: 3]

Here the key is the attribute name, and the value is the ranking. Based on the attribute ranking, I need to sort the product list.

What is the best way of sorting this list?

1 Answer 1


Pagination is something that needs to happen after sorting. If you were to paginate first and then sort, you would only be sorting your current page, which is not what you want.

Secondly, general performance suggests that you want to do your pagination on the database server, not in-memory; simply to avoid having to load all data only to then filter most of it back out again.

This leads to the inevitable conclusion that you have to know your sorting in advance before you fetch your data, since that query needs to include the specific sorting order.

  1. Fetch relevant columns from the relevance service
  2. Create your query based on the relevance data (= sort order) and your current pagination settings
  3. Instantiate and return the result of your query
  • Thanks for the reply, I have a problem sorting in advance, each of these fields has a rank which I get from the relevancy service. How do we architecture such a system? if we have a listing service and a relevancy service and we need to sort based on the relevancy from a relevancy service? Apr 14 at 9:00
  • @Unnikrishnan: I think you're conflating a few things here because your reply is a bit of a non-sequitur. Given the posted explanation and code, I don't see anything blocking you from first fetching the relevance data and then using that data to base your data fetch query off of. E.g. for your example, your query would do ORDER BY attribute_1, attribute_2, attribute_3, attribute_4 because of the relevance data returned (I'm assuming bigger numbers means higher relevance)
    – Flater
    Apr 14 at 9:07
  • @Unnikrishnan: If the listing service does not speak to the relevancy service directly, then the listing service needs to open its api to be told how to sort the data. A third party can then request the relevancy data (or not and instead just decide its own interests) and use those sorting fields that you added to the listing API in order to have the data sorted accordingly.
    – Flater
    Apr 14 at 9:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.