3 ms·

The main place I've run into this is a table with sorting, pagination and filtering while lazy loading data and I encounter this need a lot. With some runtime

The main place I've run into this is a table with sorting, pagination and filtering while lazy loading data and I encounter this need a lot. With some runtime query builder most databases will have decent performance for the space between "too much to reasonably load in memory" to "we need a dedicated query service like elastic". Unfortunately taking the example of sort by X then by Y where X and Y are dynamic I don't know of a nice solution in SQL only in MSSQL 2012 or MySQL 5.

taeric 5y ago
I think you are playing into the comment, though. You'd be surprised how far you can go with a table that doesn't support sorting and filtering. Basic pagination can often be foregone, if you can instead resort to offering export and let them use whatever software they are used to for this purpose. (Pagination is also just easier if you forego custom sort and filtering.) At some point, yes, you will want these things. But the basics can get you really far.
- UglyToad 5y ago
  For sure you can offer a good approximation through SQL but I'm unlucky enough that I've needed to support dynamic sorting and query builders against tables at least 4 times for different datasets in the past years. I'm particularly proud of the last one I built that still absolutely flies despite doing wildcard querying on text fields (no full text index) with multiple combined queries and a dynamically computed status from aggregate child properties in MySQL 5 with its lack of window functions. The solution was basically dynamically construct an efficient as possible inner query before applying the joins. To clarify I believe in doing as much as possible in raw SQL and have a fondness for the stored procedure driven system I worked with but this problem remains unsolvable in SQL alone and I just keep encountering it.

3 ms·

The main place I've run into this is a table with sorting, pagination and filtering while lazy loading data and I encounter this need a lot. With some runtime

by UglyToad 5y ago

taeric 5y ago
I think you are playing into the comment, though. You'd be surprised how far you can go with a table that doesn't support sorting and filtering. Basic pagination can often be foregone, if you can instead resort to offering export and let them use whatever software they are used to for this purpose. (Pagination is also just easier if you forego custom sort and filtering.) At some point, yes, you will want these things. But the basics can get you really far.
- UglyToad 5y ago
  For sure you can offer a good approximation through SQL but I'm unlucky enough that I've needed to support dynamic sorting and query builders against tables at least 4 times for different datasets in the past years. I'm particularly proud of the last one I built that still absolutely flies despite doing wildcard querying on text fields (no full text index) with multiple combined queries and a dynamically computed status from aggregate child properties in MySQL 5 with its lack of window functions. The solution was basically dynamically construct an efficient as possible inner query before applying the joins. To clarify I believe in doing as much as possible in raw SQL and have a fondness for the stored procedure driven system I worked with but this problem remains unsolvable in SQL alone and I just keep encountering it.