Query Builder - advanced functionality

[George]

Tom do note that all the filters have different possibilities depending on the data type of the field.

So if it is a number you will get all the numeric comparison like bigger than , equal etc
while if it is a string you will get contains, begins with …

So it is really powerful

As of the dialog - well for the most cases it fits nicely and if you have so many conditions - well then it just creates a scrollable pane so you can scroll

Most people use just 1-2 conditions so it won’t be a good idea to make the dialog bigger just because of an exception. While scrolling is ok.

In the current Wappler UI - we explicitly try to stay away from resizable dialogs as they introduce too much UI clutter. You can maximize it though.

[TomD]

Thanks George - I can certainly see now it is very powerful. I think the problem was I started with a set of rules (not in a group) and then started adding groups. The result was that there was no way I could switch the AND/OR operators as I needed. Anyway, I’ve sorted it out now.

You can maximize it though.

I think I need to take a break… Perhaps it’s the dark interface… Resizeable window no longer required.

Thanks. I really think Wappler is amazing.

[TomD]

Hi Teodor and George,
Thanks for your help with the query builder etc…

I did get a basic keyword search to work finally. The query builder generated exactly what I needed with a series of grouped rules, so I was puzzled that it didn’t work initially. I had allowed for 3 words, but after the first word, the query executed and returned every record - because of the empy query strings for the other words. I had to create a separate query for each word, and use the condition step to check the query string wordcount. I’m not sure if this makes sense, but once conditions can be applied to rules I won’t have to do this and a single query will work as before. I hope this final part of the query builder won’t take too long but at least I can move on.

[George]

Well there you go @TomD specially for you in the today Wappler update 1.2.1 we have all the conditional query and also conditional filters!

So go ahead try it out and let us know how it is!

[TomD]

I’ve had a quick look at the conditional filters and they seem to work very well - this will make things a lot easier.

However, I’m having serious problems with the query builder. I think it’s to do with aliases, but it doesn’t work even without them. If you add a second table to a query, Wappler seems to guess how they’re joined, but it joins several fields (and only one is correct). If you select a join to delete, the delete options are greyed out:

(The ‘.undefined’ field appeared when I tried to remove the joins by clearing the contents of the row.)
I thought I had found a solution: adding a new field using the + button enables the X and the dustbin. I could now remove the unwanted lines. However I created the query, using aliases, and it didn’t work. I tried again, without aliases, and it still didn’t work. Each time, trying to run the query produced:
“SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘items.item_id’ in ‘field list’”,

I think this was a problem when aliases were used before; now it seems to be a problem generally.

I’ll carry on trying to find a solution. If it’s a new bug and I have to wait weeks for a fix, over the Summer, I shall be really stuck. I’m working on a solution with lots of many:many relationships. Hopefully I’m doing something wrong or there is a solution (but I wouldn’t have thought an unknown column should be generated by the query builder interface). It would be interesting to see if others have this problem.

[Teodor]

Hi Tom,
It is not really clear what exactly are you doing there.
Can you explain like step-by-step what exactly did you do and what is supposed to happen?

[TomD]

Hi Teodor
In the image above, I’ve added a second table to a query and Wappler has ‘guessed’ which fields to join on. The first one is correct, so I wanted to delete the others, but the delete option is greyed out. Here’s another example:

Perhaps because both tables have timestamp fields with the same name, they’ve been joined automatically. The problem is the delete options are greyed out - surely if I’ve selected a row, as I have in this example, these buttons should be available?

As I said, I’ve found a way round this and shall try to create the query. The other problems could be my fault - I’ll work on this more and let you know if I can’t get further. I need to create a series of nested queries. I tried this the other day on some test tables, using you very clear demo - and it worked find, but perhaps I’ve forgotten something.

[George]

Well Wappler guesses indeed the join indeed based a bit on your field names to make it easy. But you are in full control! Just double click on the field name and choose the right one!

[TomD]

My point was that it seems you’re not in full control - in fact it seems that you can’t create correct the incorrect joins.

In my screenshot above, Wappler guessed I wanted to join on three fields (not very likely) - so I thought I would simply delete the two I didn’t need. However the delete ‘x’ or dustbin options are greyed out (having selected the row I wanted to deleted). I think it would actually be better if Wappler didn’t try to guess and let the user decide from the beginning.

I have tested this several times and it seems to be a consistently reproducible bug. Once you’ve found how to get round the bug, it’s not a problem - but it’s still a bug which might prevent people from being able to create joins - eg if they’re not familiar with Wappler and don’t realise it sometimes needs a little ‘persuading’ to do things.

I think aliases are still buggy too. I tried creating an action file with two queries (using a repeat). It’s quite easy to get an error. I tested the output at each step and eventually got things to work fine. However adding an alias for example might break things and result in:
JSON output ok, then added alias;
“SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘as.ts_created’ in ‘on clause’”
… It was working before I added the alias. Then I couldn’t remove the alias. I had to delete the query and create it again.

As I say, I got their in the end. I have quite a few queries like this to do, so it will probably get easier, but I think I’ll have to keep testing as I add steps - to avoid having to start all over again. I’m not confident enough with the current query builder to do a lot of work with it and expect it to work. I can see it’s very powerful and it seems reliable for simple queries. However I think it has a few bugs which could put new users off.

[Teodor]

Hi Tom,
You sometimes generalize things like "you’re not in full control " as if this is how wappler is supposed to work … yes, there could be some small bugs, and that is why we are updating the program every week so we can fix everything asap.

And contrary to what you say, actually you are in full control, and can edit every field/option.
I just tested the joins - and i AM able to use the delete icons there. When exactly they don’t work for you (is it possible to provide step-by-step instructions on how to reproduce this)?

Also - when saying this:

I think aliases are still buggy too. I tried creating an action file with two queries (using a repeat). It’s quite easy to get an error. I tested the output at each step and eventually got things to work fine. However adding an alias for example might break things and result in:
JSON output ok, then added alias;
“SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘as.ts_created’ in ‘on clause’”
… It was working before I added the alias. Then I couldn’t remove the alias. I had to delete the query and create it again.

Do you mean you create a query, add a column, add an alias to it:

Then nest another query after it in a repeat and use this alias to filter it like:

As if this is the case. it outputs the results just perfectly.

Also - what do you mean you cannot remove the alias?
Just double click here and press the delete button on your keyboard:

[TomD]

You sometimes generalize things like "you’re not in full control "

I think you missed the point of this comment. George commented:
"But you are in full control! "
… and I was responding to that comment.

And contrary to what you say, actually you are in full control

You quoted me out of context. I didn’t say this. I actually said:
"it seems you’re not in full control " - in other words I didn’t say you’re not in full control.

I don’t think the bugs are critical but I’m sure you want to fix them so I tried to explain as clearly as I could the bug which initially concerned me. I’ve included two screenshots (in my posts above) of a row selected and the delete options unavailable. I would have thought this was sufficient to demonstrate the bug but I could make a demo if it would help.

Regarding the alias issue - no, this alias feature is not the problem (at least as far as I know - I haven’t used it yet). The issue is in relation to table aliases eg:

This feature had some known bugs previously and fixes were made in the last update. However, as I said, there still seem to be bugs.

[Teodor]

Apologies for not getting you correctly

I will check the issues you reported with our developers.

[TomD]

Great - thanks.

(There is a what seems to be bug in query builder which I can’t get round. I’ll create a new topic for this.)

[Teodor]

Hi Tom,
the issue with table joins alias usage has been identified and fixed. The fix will be included in the next update.

[TomD]

Great. When you say ‘next update’, will that mean in several weeks’ time or will there be any updates during this time?
Thanks

[mrbdrm]

the updates are weekly

[TomD]

That would be good. However I understood from this:

Wappler 1.2.1 Released

After this update our dev team will have a few weeks summer break. So expect the next update after that.

… that they might not be for a while (but hoped fixes rather new features might be included in updates in the meantime).

[Teodor]

The next update will be on Monday guys

[George]

This should be improved now