Database search query with advanced filters and pagination, for WU optimization

[mtsougranis]

I have created an app that manages projects, tasks and other things (contracts, customers etc).

In this app, there is a page that shows all tasks (I call them project steps) and gives the ability to apply filters. My current approach is to fetch all tasks from the database and apply the filtered operator on the front end, to show some of the tasks (according to the filters applied).

However, fetching all the tasks from the database will probably be consuming much WU, especially when their number (number of tasks) increases.

Therefore, I’m considering changing my approach, based on the boilerplate provided by @georgecollier , applying the filters on the backend and fetching a small number of tasks (for example 10 every time - pagination).

The problem is that one of my filters is advanced (makes a case insensitive search of the task title) and, as I know, cannot be applied on a Do search for, but only on the frontend (using the :filtered operator).

This means that I have to fetch all the tasks from the backend and then apply the Advanced filter on the frontend (and then apply pagination).

A workaround, in my opinion, is to avoid applying the advanced filter, by creating one more column in the tasks db table, that stores the title of the task uppercase and use that column for case insensitive search. But I want to avoid this approach.

So, is there any other way to be able to apply the advanced filter and pagination on the back end, so I can only retrieve from the database a limited number of items each time?

[betteredbritain]

Do you mean you making a server-side requests that limits what loads client-side using constraints (not filters) … Otherwise, I don’t understand why you would want to do that search on the backend …

But, yes … I would say you on the right track. Your proposed approach should work and you don’t want to load the entire table before filtering the relevant results you want to return only what is necessary and nothing more

[Jici]

I think you should just consider using text filter in do a search using title contains or contains keywords? This is case insentitive

[Eugene_West]

create aka “search_index” column and store there ur columns that need advanced filtering with proper formatting

[betteredbritain]

That’s another great approach

[boston85719]

What’s your concern, speed or WU?

[mtsougranis]

Yes, I want to make server side requests using constraints, to limit the amount of data fetched, resulting in consuming less WU.

[mtsougranis]

In general, both are of concern, but the goal of emphasizing on server side processing is to reduce data fetched from the server, resulting in less WU consumed.

[georgecollier]

@mtsougranis

My current approach is to fetch all tasks from the database and apply the filtered operator on the front end

Keep in mind :filtered is also a server-side constraint - only Advanced filters happen client side.

@mtsougranis

A workaround, in my opinion, is to avoid applying the advanced filter, by creating one more column in the tasks db table, that stores the title of the task uppercase and use that column for case insensitive search. But I want to avoid this approach.

Why do you want to avoid that approach? Unless this project is updated super super often, it will be more efficient. You just have a database trigger that keeps it in sync and update the column whenever the field you want to be searched over changes.

If you really don’t want to, you could always use contains keyword(s) or Any field contains. Both are case insensitive.

[mtsougranis]

As far as I know, ‘contains’ filter is case sensitive where as ‘contains keyword(s)’ searches for words (not parts of words). That’s why I use advanced filtering

[mtsougranis]

1. To tell you the truth, I believed that :filtered was client side. Thank you for this valuable insight.
2. The reason I’m not using contains keywords(s) or Any field contains is that I need to search for partial words.
3. In general, I don’t like the approach of having these extra columns (data duplication), that’s why I made this question in the forum. However, after this useful conversation, I will probably apply the aforementioned approach.
4. If I have the following search query (and asssuming that I don’t use any advanced filters), will the server return only 10 results? In other words, items from# and items until"# happen server-side or client-side?

[georgecollier]

Any field contains is partial and case insensitive!

@mtsougranis

If I have the following search query (and asssuming that I don’t use any advanced filters), will the server return only 10 results? In other words, items from# and items until"# happen server-side or client-side?

Only 10 results! If :filtered contained an advanced filter, then the number of results returned would be the number of results that meet the Search for Tasks:filtered (without advanced filter) constraint.

[betteredbritain]

I don’t know if its just me but I don’t see how this loaded table of records will paginate with the current setup.

Given that you are fetching the first 10 items consistent with the search, what happens if the result of the search has anything above 10 results?

[georgecollier]

Yeah item’s from # needs to be dynamic else this will always just be the first page

[betteredbritain]

Wait, I would define both from # and until # dynamically … although I know this would work, is that redundant?… Off the top of my head I would say no. Unless you set a fixed number of rows for the RG (which I would not recommend for obvious reasons). Am I wrong?

[georgecollier]

@betteredbritain

Wait, I would define both from # and until # dynamically … although I know this would work, is that redundant?… Off the top of my head I would say no.

Nah, items until only needs to be dynamic if the number of results per page should be user modifiable.

List A: items from X: items until Y

will always return a list of length Y

It’s easier to see why that is with parentheses

(List A: items from X): item's until Y)

X is our cursor/start index, Y is the number of results to return.

[mtsougranis]

1. @georgecollier , I was reading the following and got the impression that this operator doesn’t work with partial words.

2. About the question I made before @betteredbritain , of course we must dynamically define at least start (items from). But my purpose by showing this screenshot was not to focus on pagination but to get an answer to the question if this whole thing happens server side or client side (which I got).

[betteredbritain]

Agreed

[georgecollier]

@mtsougranis

@georgecollier , I was reading the following and got the impression that this operator doesn’t work with partial words.

Ah my bad

[boston85719]

@mtsougranis

In general, both are of concern, but the goal of emphasizing on server side processing is to reduce data fetched from the server, resulting in less WU consumed.

Okay, so main concern is WU, and idea of reducing the number of results returned for improving performance.

My paid Plugin Data Jedi might be of interest if you want to be able to afford scaling the app, as it will give you reduced WUs (drastically) and improved performance for pagination (watch video).

Here are some screen shots from the WU charges

Below is with the Hybrid Data Structure approach I use with my plugin. It fetched 228 Contacts for 0.68 WUs

Below is a screen shot of the Legacy approach with custom data type and simple from until. It paginated 3 times to fetch 30 Contacts for 1.40 WUs

And once you have all those objects on the client device, you can filter them as you want. The plugin also has an element for searchbox and multiselect that allow you to filter across multiple fields, so no need to do the extra data field of ‘search_index’ (which just adds to the total WU costs).