3

I have an api with 3 layers

1.) The endpoint layer which exposes api endpoint handler functions. These generally call the business layer, aggregate the results to form an http response.

2.) The business logic layer, which provides interfaces to get or edit data while employing business rules behind the scenes, modifying data and enforcing rules. Prevents bloating the api endpoint functions and provides re-usable logic.

3.) the data layer, does the actual modification or querying of the persistence layer (db or otherwise) without applying any logic to the data.

In some of my functions the business layer needs to work with a list of objects retrieved from the data layer which has been sorted by a specific field. There is no case (currently foreseeable) in which the business layer will ever process this list not sorted by that field, so in this case does it make sense to sort the data in the data layer, or does it make more sense to stay pure in layer intentions and even if it means re-sorting the list every time, keep the sorting in the business layer?

5

If there is only one acceptable order, business wise, decide if this ordering is an actual business rule, or is it something inherent to the data itself.

For instance:

  • A table of persons may be sorted by first or last name, or a phone number, or an e-mail address. Maybe business rules in a specific domain dictate that it may only make sense to display this data sorted by the last name of the person; but looking at the data, you can't figure it out.

  • On the other hand, a table which contains two columns: the date of a historical event, and the name of the event, will, in most cases, be sorted by date. One can expect it to be searchable by event name (including full-text search), but this doesn't mean it should be sorted by the event; the only thing it means is that the event should be indexed for full-text search.

Note that in some cases, if you decide to do the ordering in business layer, you may later be forced to move the sorting logic down the layer for performance reasons. Therefore, write your code in a way it will make this change easy later. However, don't just push the ordering to the database layer because of the hypothetical performance gains.

  • 1
    What about to parametrize data layer? I mean, allowing business layer to inform sorting fields. Performance and business rules needs would be covered. – Laiv Sep 7 '16 at 18:30
  • If this is possible and easy, great. It's not always the case, such as with hard-coded SQL queries. – Arseni Mourzenko Sep 7 '16 at 18:36
  • True. Well I will let it as a comment because I'm in agreement with you at this. I use ORM too much. – Laiv Sep 7 '16 at 18:39
  • The question implies the OP is in control of the data layer so even if the SQL is hard coded, the ORDER BY could easily be appended using string concatenation. – Martin Maat Sep 8 '16 at 7:36
  • 1
    You may lose some optimization but only of you use the functionality.And "open door to SQL injection"? Come on, you indicate which field to order by, there is no risk there. You do not even have to use the literal field name, you can pass an enum and have the DL change it into a field name for those enums that are recognized. – Martin Maat Sep 8 '16 at 10:45
1

If you're consistently sorting data sets the same way and they're not too large, you can do it in the database. Databases are pretty competent at sorting especially when they can utilize an index.

At some point, the sorting is too much of a performance hit. That's a good time to move it to your application. For apps that require multiple ways of sorting data or if the sorting algorithm gets too complicated (not necessarily a performance hit), put that in your business logic. Having a bunch of Case statements in your Order By clause would be one sign of this.

Some developers would want to be consistent, so if they are going to be required to sort in the business logic, they'll just do all of it there. Name your data retrieval objects to indicate if they're sorted, and you shouldn't invite too much confusion.

  • Why would you go away from database sorting with order by when it is a performance problem? I am pretty sure databases with indexes sort faster than the application would. – findusl Feb 13 '19 at 12:14
  • 1
    @findusl - at some point, the database may be the bottleneck especially when you only have one server. Of course you can tune it, throw hardware at it, redesign, go NoSQL, cache data, etc, but if you’ve exhausted those options, do it in the app. Some sorting logic gets so complex, it’s not worth it. If I want the option to sort on any combination of 10 different fields, I wouldn’t do it in the database. – JeffO Feb 20 '19 at 14:31
  • Oh so you mean use the users device since there are many user devices and only one server? That is actually a really good idea I never thought of... – findusl Feb 20 '19 at 15:17

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.