`Like` operator does not work for internal tables

[mjashanks]

When using a filter on a Data Provider, with internal tables, the Like operator does not work. It works fine on SQL.

I seem to remember discussing this before and finding that it won't be possible with internal tables.

Let's remove it, for internal tables.

[mike12345567]

Like should work, but differently than you'd expect for internal tables, its actually a fuzzy search in the case of internal tables, which is not hugely useful beyond a free text search input.

I agree in this case, its probably not particularly useful, we should just remove it from the frontend as an option for internal tables - as long as the backend still supports it then it'll be fine for backwards compatibility.

[aptkingston]

Do we need to keep this around for external SQL tables? It's actually quite a pain to only have it for external, because everywhere that previously only needed to know schema types (e.g. from data bindings) now needs to know the actual type of the datasource as well. We've relied on having the same searching API across the board since that was intended to be a feature.

For what it's worth, "Like" does work as intended when I use it. It's a fuzzy search and I think allows for 2 character corrections. For "Iran" for example, "oran", "irab" and even "xxan" all work. @mjashanks could you provide more details on the setup here that didn't work? I would rather investigate and fix a bug if one exists around the usage for internal tables than remove it, because it is supported and does work in the testing that I've done.

[mjashanks]

I think it's a misunderstanding of the term. I was expecting it to work like startsWith or contains - probably because I come from SQL, and that's the most common use of a SQL LIKE ....

1. WHERE country LIKE 'Ir%'. matches Iran, Ireland
2. WHERE name LIKE '%john%' matches John Smith, Michael Johnson

If it does a fuzzy search, then maybe we should rename it Fuzzy Like, in the builder.

Long term, I would have thought that this would be an item on the "Feature List" of a datasource - i.e. the list of filter operators it supports. But I know we are not there yet.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[xnohat]

Hi, this bug still available ? Currently at version 1.0.90, Like operator still not working for internal tables, also not working for fuzzy search Is there any alternative solution for searching in Internal tables ?

[mike12345567]

Hi @xnohat - due to technical restrictions in place around the current version of CouchDB we are not able to offer a search along the lines of how an SQL LIKE operator works. You can use the string search for a starts with search, but there is no way to do a contains search.

[aidenmitchell]

@mike12345567 Is there a timeline for a fix? I have a similar problem with the table block, the search column doesn't search inside the string.

[mike12345567]

Hi @aidenmitchell - this is not something that we have a plan to fix at the moment, it is a technical limitation of Clouseau our search engine within CouchDB. In a self hosted environment we recommend the use of Postgres for data heavy applications which utilise a lot of searching/relational data.

There is a chance we may come across a mechanism to sort this at some point, especially with CouchDB 4.0 currently being worked on, but for the time being we don't know of any way to rectify this issue.

[bjornclauw]

Hi, i would LIKE to add that this also affects filtering on data providers populated via REST API. The workaround now is to send a new GET request containing my filter. Which adds quite a bit off extra API calls.

Also, one of the first things i created with budibase was a 'quick search' internal phone number app. I set this up with budibase internal database. It was not immediatly appearent to me (as a new user) why the simple search function was not working with the LIKE operator. I the end i setup a mysql database where i could use the LIKE statement. I remember thinking to myself that this was quite the detour to end up with an app where i could search for "Doe" in datatable ("John Doe" ; "1234" ).

Don't get me wrong, i'm glad i continued looking into BB because this is an amazing and powerful piece of software with an active and helpful community.

[diedder]

Hi, in my app the LIKE operator for filtering a dara provider also doesn't work with an external MySQL DB. In the following Filter

Filtering for the first character works

Filtering a whole existient phrase also

Filtering just the number, which is always the second character doesn't work!

I want to filter the table wirh the textfield. In the real data the column hold complete sentences an my goal ist to filter alls rows which contain special words or so

[stale[bot]]

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

[hotemetoot]

Hi @diedder, have you been able to get the search function to work for your MySQL database?

[diedder]

Hi @hotemetoot , adding % before and after the binding worked for me

[stale[bot]]

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