MySQL result filtering

(Nicho) #1

Hi everyone,

I have a problem with the {{mysql_query.result__rows}} filtering and maybe somebody knows how to fix it :wink:

I want to check in a filter after a SQL-Query, if the result rows are empty. In other syntax something like this: result.rows.length == 0.
I tried the collection syntax like this {{mysql_query.result__rows #}} EQUALS 0, but it didn’t work. I also tried {{mysql_query.result__rows #}} IS EMPTY AND {{mysql_query.result__rows }}.

My idea is, that I have 2 other filters, so that my Bot can answer in a different way, based on the amount of rows.

With a little workaround, I was able to do it with an additional item-attribute in my SQL-query, which I gave 1 as a value. The query looked like this: SELECT *,'1' as item FROM Table;
I used then in the filters the SUM to check the length of the rows:

  • Multiple items found: {{mysql_query.result__rows_+_item #}} IS GREATER THAN 1
  • 1 Item found: {{mysql_query.result__rows_+_item #}} EQUALS 1
  • 0 Items found: my current problem :confused:

@FlowXO: I think the current SUM-function is only working for key-values inside arrays, but not for an array. Is there something like an array.length-function, which I haven’t seen yet?

I hope you can help me, many thanks and cheerio,
Nicho

(Kellsey Shaw) #2

Hi there,

We have a Code service that you can use to handle the JSON that is returned as the query results. You can then read the rows value to check whether there are any rows,

(John Jackson) #3

I think you might also be able to do this test:

{{mysql_query.result__rows}} IS THE VALUE []

If you output the contents of {{mysql_query.result__rows}} when there are no rows, you’ll see what you need to match on and should be able to go from there.

Hope that helps!

(Nicho) #4

Many thanks, Kellsey and John!! I followed your advice and wrote the following script:

if(inputs.rows.length == 0){
    resolve(0);
} else if(inputs.rows.length == 1) {
    resolve(1);
} else if(inputs.rows.length > 1) {
    resolve(inputs.rows.length);
}

After that I was able to filter with EQUALS 0, EQUALS 1 and GREATER THAN 1. Everything is working perfectly!! :tada: Thank you guys!! :blush:

(John Jackson) #5

That is great to hear :grinning:

And thanks for sharing the code.

(Glenn Geller) #6

Sorry to bump an old (ancient) post, but I just recently converted to mySQL, and found the lack of a row count to be an issue as well.

Trying the earlier solution, and sending just the “rows” was returning some high number using the .length (I needed a real number of rows).

I found this solution, and it seems to work for all mySQL returns, using Code -> Execute Code:
(sidenote- as the input could be escaped (/“data/”), …parsing as JSON made it possible)

Set a new Input like rows = {{searchby_mysql.result__rows}}

var rows = JSON.parse(inputs.rows);
var count = 0;
count = Object.entries(rows).length;
resolve(count);

Accurate Zero return, and positive count achieved.

Hope this helps someone. :wink:

Thanks! GG @ VDO

1 Like