custom MySQL query with order by issue

[samg666]

Hello,

I found a similiar issue as #5187 on custom SQL query. My query is something like SELECT * FROM view_bills WHERE BillDesc LIKE '%{{Keywords}}%' ORDER BY {{OrderBy}}, and the variable OrderBy is a dropdown list with options like id DESC or name DESC, this query work just fine on v1.0.9x, but after upgraded to v1.0.1xx, as Budibase has changed the parameters handling, it will treat the variable as string then automatically added single quote, the query sent to MySQL was SELECT * FROM view_bills WHERE BillDesc LIKE '%keyword%' ORDER BY 'id DESC' while the expected correct query should be SELECT * FROM view_bills WHERE BillDesc LIKE '%keyword%' ORDER BY id DESC.

@mike12345567 Could you please help to take a look on this?

[mike12345567]

Hi @ggs331 - the use of dynamic identifiers in queries through Budibase has been restricted for the time being, statements like order by {{ sort }} cannot be considered valid due to the security implications of them; this would require direct user input into SQL queries something. We are currently discussing internally as to how we could support this, for now you'll need to change your query to something like:

select * from view_bills
order by 
  CASE WHEN {{ sort }}='id DESC' THEN firstname END DESC,
  CASE WHEN {{ sort }}='id ASC' THEN firstname END ASC

We realise this is somewhat non-ideal if you have a very very dynamic scenario, we are looking into ways that we can support this safely - but for now you'll need to use something like the above statement to utilise dynamic identifiers in SQL queries through Budibase.

[stale]

This issue has been automatically marked as stale because it has not had recent activity.

[hawkinsjon]

@mike12345567 I'm curious, in a scenario like the one you guided above:

select * from view_bills
order by 
  CASE WHEN {{ sort }}='id DESC' THEN firstname END DESC,
  CASE WHEN {{ sort }}='id ASC' THEN firstname END ASC

What settings would one put on the datasource so budibase didn't attempt to apply its datasource, sort settings to the already sorted results? Or will the budibase datasource sorting component be irrelevant if a sort command is found in the query?

In other words, I'm troubleshooting a similar issue myself and it would be helpful to know which sort (the SORT BY in the custom sql query) or the sort in the datasource component is used. Or maybe both?

There is no way to deselect the sort settings in the component that I'm aware of so I'm kind of curious which sort directions will be followed.

Additionally, it seems the budibase component sort overrides any sort that is listed in the custom query but it would be good to know how this works as well.

[mike12345567]

Hi @hawkinsjon - the sort options within the data provider don't apply at the query level, Budibase does not know how to apply these to say SQL, a DynamoDB query, a REST request, so instead that sorting is carried out in the frontend end based on whatever data is returned in the response. For queries you need to write your sorting/pagination yourself and pass the required parameters to the query.

[hawkinsjon]

That's good to know, thanks @mike12345567

This leaves me with just one question. What settings should I set to Budibase to so that it leave the already sorted results alone? The problem I'm running into now is that my sort column and sort order settings in the Budibase UI are applying sorting to my already sorted results set.

Here's a more specific example. My data provider is running a query that ends like this:

The results from this query directly are perfect, exactly how I need them to be. The problem is that I am forced to select a sort column and sort order in the BB UI.

When BB Data Provider component sorts on top of sorting thats already carried out at the query level, the results get funky. Is there a way to essentially disabled the BB Data Provider component sort order, sort column, and limit settings?

[hawkinsjon]

@mike12345567 as a quick update I found this dummy column work around from @mjashanks and it works perfectly. If I can +1 or vote for a setting in these drop downs that allows the BB user to disable the front end sorting attempts, that would be outstanding!

#2947 (comment)

[mike12345567]

Hi @hawkinsjon - sorry for not responding earlier - that is a good workaround for now - I've raised this to the team that looks after data provider settings, there has been some discussion about this, adding an option to disable sorting is being looked into.

[hawkinsjon]

@mike12345567 no worries at all and thank you for your consideration and help.

FWIW Mike, I (as I'm sure many in the community) are very appreciative of your time when you have it to share but never feel so bold as to expect it.

It's incredibly motivating watching the BB team build software with this energy, quality, opinionated no-code/low code design, everything...

You all should be proud of this rhythm you've developed over time. This level of software development reminds me of a small period of time when I was able to work with Pivotal Labs (NYC). You guys have it right now, don't lose it and know that when you do have time for a little support and chatting with the customer base, it's seen as a luxury by us, not an expectation.

[mike12345567]

@hawkinsjon - thank you so much for the kind words - I've passed them along to the rest of the team as well - you've made our day!

[stale]

This issue has been automatically marked as stale because it has not had recent activity.