Architecture: Ordering in Rails or MySQL

Question

I have an AngularJS front-end app that sends requests to a Rails API back-end. When a user search for items, so far the query is limited to 20 elements and is always ordered by popularity (a field stored in database). After the results are retrieved from DB, there is a complex process that calculates the item prices iterating one by one (remember only 20 elements). After that, the results are served to the user. Note: As told, item prices cannot be calculated directly in the query because an item can have different prices according to dates and also discounts can be applied.

This is how it´s working so far.

Now, I would like to introduce in the search results page an innocent order by: Price functionality. So, the array of items should come ordered by price.

As, I can´t get the prices directly with just one query, I see two choices:

1. To keep it as it is right now, I mean, making the query ordered by popularity and order the results after the prices are calculated. But I see a problem, If I get 20 elements each time ordered by popularity, then I can calculate prices and order by price these 20 elements, so I assume I´m not ordering correctly by price. This case, I would need to query without limit, to get all items, calculate prices, order them by price and return to user. I think I would also to develop a home-made pagination functionality.

2. Develop some kind of stored procedure in the database to provide the results with the complex prices calculations. I don´t know if I can order them easily. I´m worried because I don´t know stored procedures in MySQL and not sure if it´s possible to do what I need.

But, from the performance point of view, I guess the second choice should be better, right? I´d appreciate comments or any other options?

UPDATE: According to comments, I detail how to calculate prices functionality.

A user can rent an item for many days (a week i.e.). So, there is a check-in and check-out dates. Also, prices changes according to seasons. This means days can have different prices in a selected week. So, in order to calculate the total price, you have to get the daily price matching each selected day in the week and add it to the total price. Once, the base price is calculated, there can be discounts. Same as prices, discounts can be applied only for some days, so first, it must be checked if there is any discount for the selected week. If so, the discount is applied to base price to get the total final price.

Please, let me know if you need the code.

Answer 1

So you have the following factors then:

• User Inputs Beginning Date, End Date and sees list of items.
• The Beginning - End Date period (selected period) is consistent for all items in the query.
• To sort by price you must, for each item in the table (not just those retrieved)
  • Get Item Price for each day of the selected period.
  • Get available discounts for each day of the selected period.
  • Calculate Total Price for selected period.

Because user input is involved you will not be able to definitively calculate this and store the final product. The best you could do is calculate the gross and net prices per day and cache that, then performing the final calculation based on user input (or sending that data to browser for calculation there)

You could also use a first order approximation and just go with the current gross value, and then tweak from there. That assumes that the relationship between item prices remains more or less consistent.

Answer 2

This should be done on the backend (MySQL).

Like you mentioned, if you want to sort by price on the front-end, you'll have to replicate the entire database into Angular. Which is probably a bad idea.

This is the approach I would take in MySQL:

1. Set up a table with item_id, date, and price (or perform the joins necessary to get this table).
2. Apply discounts for each date. This step yields an interim table with updated prices.
3. Build your final query. Your SELECT clause should SUM(price). You should GROUP BY item_id. And you should ORDER BY SUM(price) DESC.

Depending on the size of your database, this query may require a lot of fine-tuning in order to return results quickly. But it definitely can (and should) be done in the backend.

Good luck!

EDIT: With a really big set of items, running this query through MySQL may become too slow, regardless of how much time you spend tuning performance. If MySQL doesn't cut it, you may need to rely on an auxiliary database like Elasticsearch.

But before turning to Elasticsearch/Hadoop/etc, you should think carefully about how "complex" your pricing algorithm really is. In all likelihood you can optimize the MySQL query to the point where it performs just fine.

Answer 3

This is a classical search listing problem. If you want to perform sort based on price, obviously you need the required inputs. it is worth mentioning that stored procedure won't magically improve the performance. The best what you can do is run recurring CRON job every hour/day ( bonus tip : use upset and perform mass operation) which updates the price. Now perform join and do the query and order it with "price". Also make sure to put indexes on required fields. After grabbing the result apply discount and show the result. If query is complex the pagination won't work. You have to do all sorts of operation manually. If you are using will_paginate , it has some function which also support Array instead of active record. Hope this helps.