Filtering database query with variable input values

Wappler General
#1

Dear fellow Wapplers

From the doldrums of my Wappler development trenches, I call out for some help… because it seems like I ran into a Wappler dead-end… which I didn’t expect…

What I need to accomplish is seemingly simple:

I have a table with a significant number of rows. A standard query works great with which to populate a bootstrap 5 table.

But to offer the user of my app a better experience, and also an essential feature, I need to present a BS card with some possible search criteria which can then be composed and used to filter the data returned from the database.

There are number of possible criteria, in this particular case, 5, which can be specified in any combination. As an example:

The database table contains available jobs. A job typically has a title, status, type, province, city, employer, recruiter and more.

A user searching for a job, must be able to filter on a combination of title (contains), status, type, and city. Any of these can be presented, or none at all. A single or multi value filter is possible, but the query, via database query or custom database query, needs to be defined up front as such.

But with the number of filter variables that can vary, in different combinations, this does not seem possible with the query builders.

I also made an attempt to pass the whole WHERE clause as a $GET variable to be bound within the SLECT statement, where this WHERE clause can be constructed on the fly before running the query. But this does not work either.

Is there another way?

Any brilliant and highly anticipated insights will be greatly appreciated!

Regards

#2

Hi.
Doesn’t sound very complicated.
Create all inputs in the UI. And bind them to the Server Connect which gets your data.

In the SC, open your query builder conditions tab.
There, create a new rule & set the condition: title contains $_GET.title.
Now for the important part: set the condition on this condition. Click the question mark icon and set the binding to $_GET.title again.

What this means is that this condition will be applied, if something has been sent in title from GET.

Similarly, you can create other conditions… and set the main operator to be “OR”.
In case you have other where conditions, create a Group first, then add all these conditional conditions inside that - and set the operator to OR.

Hope this makes sense.

#3

This is indeed easy to do with Wappler, most is described in our docs:

Additionally if you have multiple optional conditions, you can choose to make each dependable of some logical expression ( like empty or not). it is the ? Icon under each condition in the query builder.

#4

Hi sid

Many many thanks! Sorted.

If not for your guidance, I never would have discovered that ? icon and its meaning. It makes everything clear. It rolls a big stone out of my way!

And especially thanks for responding so quickly. Didn’t give me any time to contemplate any alternatives… Because of you, I now love Wappler even more!

Regards

1 Like
#5

Hi George

Many thanks for the reply. I’ve indeed studied this material very well. But I missed the last part, the sneaky ? icon as @sid indicated above.

Perhaps a good idea to include it in the documentation :face_with_raised_eyebrow:

Regards

2 Likes
#6
#7

Yes, the ? Icon wasn’t clear to me either and it is soooo powerful! :rocket: