I often find myself needing to apply an SQL function to a field, (using a recent example highlighted by @mrbdrm such as DATE(‘field’) where field is datetime format) as well as items such as Trig functions, other time manipulations and string management.
While some of these can be added via repeats or client side formatting it would be useful to be able to apply these directly in the query builder perhaps by a picker like in formatter or dropdown in the “Column” column as in the aggregate column.
Would this be possible.
Pushing my luck further, can queries be made accessible in server connect (preferably with parameters)
I do like the idea and see indeed a lot of potential, specially when need to group by date part indeed.
Useful functions for chart data input.
The only problem we have with implementing such things is that we have to be cross database oriented. Our Database Connect works exactly the same way for mySQL, SQLite, PostgreSQL and SQL Server for example.
So we can’t introduce a very specific filters just for mySQL. We have to make it work on all. With plain SQL that is doable because all those databases have support for the general constructs of SQL. There are minor differences per database but we handle them.
So the same is with implementing specific SQL formatting/filtering functions. We have to have them for all databases.
So for the user interface we need a more generic approach that works for all. Like supply only a predefined set of sql filter functions. And in the Database Connector part in Server Connect we can translate those to the native variants.
Hope it is a bit clear 
So make a list of really useful functions that you would like and we can see if we can implement those in all database variants.
Just to give you a small example of the various date functions between mySQL and SQL Server:
http://www-db.deis.unibo.it/courses/TW/DOCS/w3schools/sql/sql_dates.asp.html
What do you think about “procedures”? Use of the Procedure can be very useful.
The use of the “procedure” will be very very good to add (call procedure function). If possible.
Yes, i appreciate the problems with different SQL variants.
Perhaps the common core functions initially and then if necessary look to build more specific functions into it by reference to the database server model selected in the server connection?
Not something i would expect in the next release but we can hope for the future.
Out of interest, do you have any stats on platforms used by wappler subscribers?
I do get the impression that most are linux/php/mysql althought have seen a few posts regarding other platforms including classic ASP which frankly i thought was long dead now (not used in in 10 years).
Also access to queries would help as then the functions and calculations could be coded at query level, especially if parameter passing could be implemented.
I will start drawing up a list, input from other members welcome
a quick start i think would be:
ASCII, LEFT, LOWER, LTRIM, REPLACE, REVERSE, RIGHT, RTRIM, SOUNDEX, SPACE, SUBSTRING, UPPER, ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, TAN, DAY, MONTH, COALESCE, NULLIF, CAST, CONVERT, DATE, DATEADD (DATE_ADD), DATEDIFF
maybe CONCAT functions…
good call, missed that IMPORTANT one
Well let me clear up something. The goal of the query builder is just to retrieve data. All the formatting you can do later on - even needed server side data formatter, or preferably even client side with app connect data formatter.
You should really think of server side actions for data retrieval - as to use them to retrieve raw data. Formatting is separate from that and can vary per client / localisation. So that is why it is best to do it last on the client side.
So saying all this there is no need for implementing string processing functions that are usually used only for formatting.
We should really focus on only filter functions, so in that area a definitely understand the need of functions like DAY, MONTH, DATE and DATE_ADD, DATEDIFF.
So simple function that usually surround the db field in the query and are added to group by.
Maybe if you can give some useful examples of other ones…? I think @Hyperbytes had something with geo location … but it will be very challenging to allow a full SQL expression parsing…with complicated expressions.
So we rather want to keep it simple.
In one trigger I’m using several of these functions, as well as a couple not mentioned so far:
LPAD
CONCAT_WS
TRIM (LEADING, TRAILING, BOTH )
It also uses:
CASE (+ WHEN, THEN, ELSE, END)
… which can be very useful.
Part of this trigger is to create a field for the ORDER BY part of the query, which is probably another issue.
I think this is is a very good suggestion (if it’s feasible). I imagine it might be a huge task to implement all of the functions into the query builder interface. Presumably at some point a standard query is generated. If we could have access to that, bypassing what the query builder offers, and pass pararmeters to it, then it might be possible to overcome a lot of potential limitations without making the UI over-complicated.
For example, I was creating an import feature a few days ago. The only option avaiable in Wappler is to use multiple INSERTs. There are several options in MySQL not availabe in Wappler (eg dealing with duplicates and multiple inserts) - which would have made the task much easier, and much faster to execute. What I usually use is a quite different MySQL option: LOAD DATA LOCAL INFILE which is dramatically faster. In many cases, the limited options would mean it would be quite impractical or impossible to do the job in Wappler. What should take a couple of seconds could take minutes (or just time out) - in the case of this particular feature. I appreciate that nobody’s going to need all the features of MySQL (or the other types of database), so an ‘advanced’ option to cater for these situation - where users would be expected to write their own queries - could make Wappler much more powerful.
Yes, i have a few applications which use the haversine formula to calculate the distance between two points.
a = sin²(Δφ/2) + cos φ1 ⋅ cos φ2 ⋅ sin²(Δλ/2)
I used this in DW hard coded into the query but i have to use embedded PHP to use it within Wappler. Not really expecting t be able to do that one in a hurry!
Tom, please check my previous response about the case and idea for the query builder:
I didn’t see your reply before sending my last message.
But the server side actions do much more than this - eg with files, images, email, exporting and inserting data etc… As I suggested, I’m sure it’s not going to be feasible to include all the options in the query builder UI, so I was wondering (as was @Hyperbytes) if it might be possible to give direct access to the queries for occasions where options not available in the UI are needed. If not, it’s always going to be a potentially rather serious limitation of Wappler, which might deter developers who expect and/or need all the features of MySQL etc…
I realise it’s not the Wappler approach, but if variables could be passed to user-defined queries, it would be an extremely useful feature. I have no idea what would be involved in adding such a feature. I have no doubt it would be a huge task to provide a query builder style UI for it, but nobody is suggesting/expecting that.
I don’t think wappler will give you direct access to the query. It’s how it was made.
most important functions are the ones that we must do on the server to get the initial results. Like the group by date function.
currently it’s impossible to achieve the group by and sum of another field in that date on app connect.
Perhaps we are approaching this from the wrong direction.
To implement all the available functions for all the different data server types is a big ask, maybe an alternative would be an “Query Pass through” where a user could pick “Custom Query” and type in a custom query which is simply passed through the the database server as pretty much a a raw query with only globals processed
So the user could type something like
“SELECT DATE(datetimefieldname), Sin(angle),ASCII(symbol) WHERE filterval = {{$_GET.filterval}}”
and the $_GET.filterval would be processed but the remaining query would be passed Raw
I appreciate it is not quite “visual Wappler” but it would be immensely powerful
2 Likes
This is exactly what I had in mind.
As you say, it’s not quite “visual Wappler” but it’s certainly in line with the idea that Wapppler is a powerful development environment which can compete with Vue.js, React etc… Also, most powerful “visual” development software doesn’t restrict or limit writing code.
1 Like