I am wanting to put two conditions on a query to get inspections by caseid if the $_GET variable caseid has a value, but if a specific vehicle is requested via the $_GET variable vehicleid get the inspections for a specific vehicle no matter what the caseid is.
I built this in conditions: (see screenshot)
But when I click the “Ok” button to close the modal and then click the “Query Options” button to bring up the modal again, this is what is now looks like: (see screenshot)
As you can see it doesn’t preserve what I built but changes it. Is it letting me do something it’s not designed to do but when I click “Ok” it corrects it?
The conditions inside this (notice the lighter grey shade of the group) will be considered for and/or but will also have the condition whether to apply or not associated to it.
In your example the condition line inspection_protocol.vehicleid equal $_GET.vehicle id is above the group you created. When you saved, the program sees tha group being empty and removes it because an empty group would cause syntax issues in the query.
What I think you need is the main parent group being AND TWO sub groups added each with the query line:
@bpj thanks for the reply. I see what you’re saying. I think this old school hand coder just needs to get used to the UI haha. I’ll give your suggestion a try and let you know.
the “AND” will be placed between the two sub groups, correct? Like:
WHERE pupils.recordType={{$_GET.filter}} AND pupils.agent={{$_GET.agentid}}
I guess there’s no way to add another condition of “OR” or another group of “AND” using the UI like?
WHERE (pupils.recordType={{$_GET.filter}} AND pupils.agent={{$_GET.agentid}}) OR (some_other.another=value AND some_other2.another2=value2)
Perhaps I just need to do a custom query or put a conditional in the step for the action.
I expect you can do what you need. I think the UI is slighly confusing, but works well on the whole, once you get used to it. Eg here’s part of a query that has about 70 parameters, including a number of OR groups:
Basically what I’m trying to do with the UI is set the “WHERE” conditionally. If I were to hand code it in php it would look something like this:
if (isset($_GET[‘caseid’]) && !isset($_GET[‘vehicleid’])){
$where = ‘WHERE case_id=?’;
}else if (isset($_GET[‘vehicleid’])){
$where = ‘WHERE vehicle_id=?’;
}else{
$where = ‘’;
}
$sql = $sql.$where;
Of course in PDO the placeholder ? would be replaced with the appropriate $_GET variable.
The groups in the UI are basically where you might put brackets to ensure processing order and you can nest them inside each other, adding conditions as shown by @TomD to determine whether that group should be processed at all.
In most cases entering the input parameter will include/exclude the group but do bear in mind that the numeric value 0 is considered false. If you have a query where 0 is needed, just put something like {{$_GET.parametername || $_GET.parametername == ‘0’}}
The should be quoted so it is seen as a string otherwise it still evaluates as false.
Ah ok, thanks @bpj. I see from you and @TomD’s screenshots that there is always an outer “OR” or “AND” with the groups inside. I wasn’t realizing that and was trying to get just the inner groups. Like I said, this old hand coder will have to get used to the UI. Most of it is pretty intuitive, this one was just working differently than my brain haha.
One other thing to bear in mind. If you’re creating a complicated query, I would try to plan it as much as possible before using Query Builder. Whereas you might work on code as in your example, and perhaps reorganise it as you go along or when it’s finished, to improve the maintainability/readability etc, you can’t do this with QB.
There are some limited drag/drop options in QB but you can’t move blocks of conditions around. I think the only way would be to edit the code, which would be very fiddly.
I broke the query down to 1 simple condition using caseid and it didn’t work so I knew it wasn’t the query itself. Besides getting to know the UI for query conditions better, it helps to make sure to add the caseid input parameter to the server connect.
Sorry one more question that @bpj you probably know the answer to. Simply putting {{$_GET.vehicleid}} acts like isset($_GET[‘vehicleid’]) but how do I write !isset($_GET’[vehicleid’]) (not isset)? I’m guessing it’s not as simple as {{!$_GET.vehicleid}} or !{{$_GET.vehicleid}}
The ! reverses the evaluation so if $_GET.vehicleid produces TRUE (i.e. it's present with a value other than 0 or FALSE) putting the ! in front to make it {{!$_GET.vehicleid}} would then make it FALSE
You can use formatters to so if length was important, for example:
{{$_GET.vehicleid.length() > 3}} would apply it if the id had 3 or more characters.
you should see an empty group created:
