Two Database Paged Query of the same name to query boolean field

[zitroware]

Hello,

I would like to ask if there are any implications or issues if I have 2 Database Query of the same name in an Action File, please see below for an example:

The reason why I have created 2 queries of the same name is because I have a filter on my page using a select input that will allow users to filter the table using a Boolean table field. We can query SQL with that field for only 2 values, true or false (or 1 or 0). I would like to allow the users to filter the table to get all records with true or false or both.
I have created one database query initially and tied that up with a bootstrap table. After I copied the same table I checked again the bootstrap table generator and it can still see only 1 query. It is working so far.

Both Database Query have the same query except for the Conditions.
Is it okay to do it this way?
Will it not create an issue in the future?
Are there any other workarounds (on which I can query a Boolean field for a true or false or both)?

Attached is the json file for my Acton File.
employees.zip (1.7 KB)

Thank you in advance.

[ben]

First off, it amazes me that it works. Even so, I would never advise using the same name for different queries; it is confusing whichever way you look at it.

[zitroware]

I was also surprised that the page still works and the bootstrap table can still get the same fields. Might be because they both have the same fields? The only difference they have is the conditions set.
If I run the generator and look into the data bindings , I can only see 1 even after refreshing:

[ben]

That makes a lot of sense.

[ben]

Thinking further, there is a file created under dmxConnect/api with the name of the query. In your case this would be called pq_employees.php. When you use the same query name, one will overwrite the other.

[Hyperbytes]

Morning Ben & Zitroware. Ben, that would only happen with a linked query, if you leave it unlinked it resides in the main server action file so wont be overwritten.

Personally I would have tried to find a way of manipulating a single query, if possible, to do this but that solution should work so why not.

[zitroware]

Thinking further, there is a file created under dmxConnect/api with the name of the query. In your case this would be called pq_employees.php. When you use the same query name, one will overwrite the other.

You are correct @ben , it will overwrite the same pq_employees.aspx file (I am using ASP.NET as the Server Model). But the queries themselves are in App_Date/ServerConnect/api directory. Both queries are in the json file I have uploaded in my initial post. Maybe since they uses the same name, it will be both referenced by the pq_employees.aspx.

Do you think there is any other way to do the query instead of using this approach?

[zitroware]

Good afternoon @Hyperbytes (afternoon from where I am from ).
Can you think of some other way to accomplish what I need?

By the way, here are the conditions from the first and the second query:

The issubcon field is Boolean.
I would like to filter a true or a false or both.

[Hyperbytes]

Why not just make the first condition (as a group)
emp.issubcon = {{$_GET.subcon}} OR emp.issubcon is not Null

therefor combining the two conditions

[zitroware]

I have already tried it (and other combinations as well) without any success.
Boolean comes only with 2 values and they will never be null, only the $_GET variable. So I think I should work on checking the variable instead, hence, the reason why I put in the condition if the $_GET variable is >=0 or not. I was also confused because $_GET variable == null does not work or I may be doing it wrong.

[Hyperbytes]

Just to get it clear in my head,

the first query you want is to return the values within the emp table which contain the firstnames and lastnames as specified and they are a subcon (send as integer value 1 via $_GET.subcon)

Can you describe in words what you want returned when $_GET.subcon = 0 please

[patrick]

You know that you can add a condition to a whole group of conditionals, it is the expression input to the right of the AND/OR.

[zitroware]

Just to get it clear in my head,

the first query you want is to return the values within the emp table which contain the firstnames and lastnames as specified and they are a subcon (send as integer value 1 via $_GET.subcon)

Can you describe in words what you want returned when $_GET.subcon = 0 please

I would like to filter the table that contains either firstname or lastname AND whether they are subcon or not (1 or 0) or both. The “both” filter comes in because I want to show the user all results whether they are 1 or 0. If the user only wants to see who are subcon, then the user will select “subcon only” that equates to $_GET.subcon = 1. If the user wants to see only those that are “not subcon”, then $_GET.subcon = 0.
Also, initially, upon page load, all records should show in the table, and will be filtered only when the user wants to.

I am editing my reply to add the following screenshot for the select input:

I hope this is clear and I am not confusing it. (sorry, I’m finding it hard to explain in a non-native language)

[Teodor]

Well, just use the condition filed for the group then, to see if the GET var has a value:

{{$_GET.subcon}} used as a condition checks if the {{$_GET.subcon}} returns a value and only executes the filtering if there is a value.

[Hyperbytes]

You beat me to it this time Teodor!, exactly what i was about to suggest!

[Hyperbytes]

You effectively have three possible data sets to return from what i can see

subcon = 1 for subcontractors
subcon = 0 for direct
subcon = null for all

So you can add a clause to test for subcon status 0 or 1 and then make that filter a conditional filter so it is ignored if subcon is null

[zitroware]

Hello @Teodor, it is not working, it is a hit or a miss.
I even tried the following because I need to filter for firstname and lastname even when $_GET.subcon is null:

Am I doing it wrong?

@Hyperbytes, can you please tell me how to do it?

Just to add clarification, the situation should be:
subcon=1 AND (firstname matches OR lastname matches)
subcon=0 AND (firstname matches OR lastname matches)
subcon = null for all AND (firstname matches OR lastname matches)

Thank you very much for giving your time and help.

[Hyperbytes]

The middle section is not necessary

Your two parts (groups) should be:

and

That way the latter test on name will always occur but the issubcon clause will be ignored if null

[zitroware]

The middle section is not necessary

Your two parts (groups) should be:

and

That way the latter test on name will always occur but the issubcon clause will be ignored if null

Wow it worked! Exactly as I want it to be.
Thank you very much @Hyperbytes. That solved the problem. And I don’t have to use 2 database queries of the same name.

Thank you also to @Teodor, @ben, and @patrick. All of you helped and lead the way to the solution.

[scott]

@zitroware Thanks for posting this question. I’ve seen that field next to the and/or at the top of the query builder conditions page, but never really understood why it was there.

Now I know !