I am trying to set up a rather complex SQL query using 4 tables in Wappler, however the SQL builder seems to have some limitations, or I am not doing this correctly. What I need is to be able to manually add a query, rather than using the query builder, if that is at all possible?
Thanks
Hans
Hello Hans,
You cannot add custom SQL queries in Wappler. You should be able to do pretty much everything with the query builder.
What are you trying to achieve? What should be the final result on your page?
Hi Teodor,
The query builder is a bit confusing form me, maybe I am just a bit unaware of how it all can be done, but this is what I need:
SELECT * FROM hairpros.places WHERE Abbr IN (SELECT StateABR FROM hairpros.Clinics WHERE Active=‘yes’ And DrID=(Select DrID FROM hairpros.Doctors WHERE Active=‘Yes’ and DrID=hairpros.Clinics.DrID) and DrID=(Select DrID FROM hairpros.Doctors WHERE Guest=‘No’ and HTN_Info=1 and DrID=hairpros.Clinics.DrID) ) ORDER BY Abbr
Effectively I have 3 tables, one with place names, abbreviations and description, one with clinics - related to a doctor - and one with doctors that show their membership status. I need to list places that only belong to clinics which are active and are associated with valid and current doctors.
I have tried to use the query builder, but it does not really appear to be that intuitive and when I tried to get this set up, I got a whole list of errors when viewing it in a browser. Really hoped there was a way to just add a custom query.
I have had to do a few complex queries also and found it much more straight forward to create a View in my DB. Would this be an option for you?
3 Likes
That is a common solution but as the query required dynamic parameters then a view is not a suitable solution as a parameters cannot be integrated with a view
After seeing so many requests about custom queries, we had more in depth investigation in supporting custom queries in Wappler and we can to do conclusion that it can be done! 
We will be making a new ‘Custom Query’ action for server connect that will allows you to enter any SQL query code. On its dialog you will also be able to define custom parameters in a grid and directly test/see the output!
Because you will be able to run the query directly, we can use its output to construct the schema, that we will use later for all the data pickers.
So we will work on it in the next few updates. So hope to show this working to you soon
14 Likes
This is a great idea. It will add another level of power to Wappler.
One suggestion. It would helpful to be able to use an existing query, built with query builder, as the basis for a custom query. I like using query builder and the ease with which you can create quite complex queries - so even for a custom query, it would be a good place to start. Just a thought.
2 Likes
Thanks George, that would really help to have that as a feature. Great product.
2 Likes
Used to use the DW SQL Query panel quite a lot a few years ago. Was actually quite good for its time…
1 Like
This would be huge! Looking forward to this one!
1 Like
That will be fantastic. I’m still in trail and this is one of the features I was looking to see if it included. This being the case I’ll complete my purchase and look forward to seeing a follow through this.
As you incorporate this feature be sure to consider including the ability to pass variables to the custom query, that will be important as well.
3 Likes
custom query coming with wappler 2.0 ? or …
@George Very excited to see that Custom Query feature get added.
In the meantime, could you advise me on how I should configure an INSERT step that looks up a UserID based on the Name of the user? It is for a form. We don’t want the person filling out to have to remember IDs.
If I were to construct an SQL query, it would look something like this:
INSERT INTO InteractionLog (InteractionID,UserID,ContactID)
VALUES(
101,
SELECT UserID from Users where Users.FIRSTNAME = 'Jim',
(SELECT ContactID from Contacts where Contacts.FIRSTNAME = 'Fred')
);
Thank you!
I think you need to clarify this more.
Is ‘Jim’ a system user (logged in?) or is that name entered into a form and Fred the contact name also entered into that form like this?
Thanks for the reply!
Jim would be a user that is logged in, so I suppose that I can use some sort of cookie with to find his user ID, I am sure there’s a doc for that somewhere.
Fred would be a contact of someone else, but I did find a work-around by using a lookup query inside of the form. This looks up the contactID and autopopulates the form field that links to the insert query.
you can get the logged in user identity directly from the security provider.identity value in server connect
2 Likes
Custom queries are already available in Wappler 2.1.0
2 Likes