Problem/bug using 'IN' in query builder

Server Side Components Bugs
#1

If a comma-separated list of numbers is entered as a search parameter using the ‘IN’ operator in query builder, Wappler surrounds the list in single quotes. As a result the query will fail (it will return only the first item in the list). I think this is a bug, unless I’m using the option incorrectly.

In this example (what I’m working on at the moment), a list of IDs is stored in a cookie as items are selected from a list, and the contents of the cookie is then used in the query. This is working as it should, up to a point.

I’ve included a static list of numbers in the query, as well as the list from the cookie, to check that the format etc. is correct:

image
image.png746×211 6.57 KB

The relevant part of the query generated is:
SELECT stock_id, author, title FROM stock WHERE (stock_id IN (‘4146,3011’) OR stock_id IN (‘4146,5137,4380’))

… so everything is working as it should, except Wappler is adding single quotes around the list of numbers, so the query fails. Without the quotes, the query returns the correct results.

I raised this issue about a year ago (obviously not in relation to Wappler, but to the DW extensions), but I don’t think the matter was ever resolved. I would have thought it would be quite easy to fix and it would be a useful feature to have working.

1 Like
#2

Hello Tom,
The expression for the IN operator should be an array. If the expression returns a string then it will be used as a string in the query - so if your cookie returns a string, you need to apply the split filter to it…
It has been the same in DW as well.

#3

Thanks Teodor - that’s fixed it.

The final part of what I would like to do is give the user the option to email themselves a list of their selected items. I understand I can do this by putting the query results into a variable and using that with the mailer extension.

With some experimenting, I managed to get the query results into a session variable, but would like to put them in a cookie (or perhaps there is a better approach). Is this possible? I don’t know how the Set Value options works or what to select for names etc… There are no pickers to use and I tried various options with ‘cookie’, without luck. I would be grateful for any suggestions. As I mentioned, the Set Session option worked so hopefully I’m on the right track.
Thanks

image
image.png483×506 24.6 KB

#4

Hello Tom,
What exactly your {{query1}} returns? And what does it display using the session?

#5

Hi Teodor,
The query I was using at the time returned quite a lot of data, but I’ve just created a simply query returning a list of categores. I used the Set Session step:

image

… and here are the contents of the session variable when the query is run:

image
image.png700×276 18.9 KB

So this feature seems to work - it is possible to store query results in a variable. Is there some way I can use this session data to populate an email? Or is there a way I could put this data into a cookie? There seems to be very few examples and no documentation about this. Eg it’s been suggested (by George I think) that it’s possible to put query results into a variable of some sort, and from there into an email, but I don’t know if anyone has succeeding in doing this. I would very much like to know how - or is using PHP from scratch the only option?

#6

The question is - what do you need to be included in the mail body exactly? The code as returned from the query? Or specific items from the query? How should your mail body look?

#7

The query returns a list of item descriptions, each containing several fields. The mail body could return specific items from the query, or if this was a problem, I could create a query to include only the items to be included.

Ideally the mail body would look something like it appears on the webpage, but I was going to think about this once I knew the basic principal was going to work. Eg if necessary/if it would make it easier, it might be possible to create a generated column or use a trigger in the MySQL table to put the data for the email into a single field - perhaps including HTML tags (but plain text would be a good start).

It’s the general principal I would like to understand. What I need to do at the moment is relatively simple (assuming it’s actually possible). I’ve asked before about mailing the contents of a shopping cart and understood it’s not possible with the mailer extension’s current limitations (not including repeat regions). However reading some recent threads on this forum, it seems it might in fact be possible.

I would much rather use the features in Wappler if they exist, rather than code everything in PHP (eg a shopping cart facility - including mailng orders etc.).

#8

For things like showing a list of bindings from a database table just use what George explained here:

#9

Thanks Teodor - I finally made some progress. When I tried this before, I gave up after trying to use the expression builder in Server Connect (sample screenshots at the end). Anyway, this time I managed to loop through the records in a query and put values into a variable which I could output on a page (and presumably include in an email body - but I haven’t tried that yet).

It’s possible to add multiple fields to the variable, eg:
{{query1.join("<br>", "language") + "<br>" +query1.join("<br>", "ts_created")}}
… but this produces two lists of fields (the data is somewhat arbitrary):
FRE
GER
2018-07-19 08:06:58
2018-07-19 08:07:08

Is it possible to use the formatters to produce:
FRE 2018-07-19 08:06:58
GER 2018-07-19 08:07:08
… ?
If not, perhaps creating special fields in the relevant MySQL table might be a solution.

Are there plans to develop a mailer extension which would offer an easier solution to what must be a very common requirement?

In case it’s of interest to anyone, here are the steps I used:

image
image.png403×648 22.5 KB

Error messages using the expression builder:

image

image