Is there support for filtering WHERE x LIKE y?

REST API
1

Apologies if this is documented somewhere but despite searching I cannot find it.

Is there support through the API, such as getRecordsByFilter(), where you can filter using the SQL equivalent of LIKE?

e.g. SELECT * FROM Mockpatients WHERE Surname LIKE ‘S%’;

At the moment it seems you are limited to: SELECT * FROM Mockpatients WHERE Surname=“Smith”

In essence what I want to be able to do is be able to return records beginning with “S” or “Sm*” etc. from a Surname field. As far as I can tell at the moment you have to put in the exact match.

If it is not supported, has anyone implemented something like this (pardon the pun) and if so can point me in the right direction.

Thanks.

Sincerely,

Grant D. VAllance

2

Yes you can SQL style queries and wild cards are supported! This’ll work for you:

SELECT * FROM Mockpatients WHERE Surname LIKE ‘%Sm%’

or [complete with html encoded elements]

http://yourserver.com/rest/yourservice/Mockpatients?filter=Surname%20LIKE%20’%sm%’

1 Like
3

Thanks for the response, @antonyjsmith! For further reading, here is the DSP Wiki page on using ?filter= in the API URI of a GET call: https://github.com/dreamfactorysoftware/dsp-core/wiki/Database-Retrieving-Records#get-filter

4

Many thanks to antonyjsmith! Works perfectly. I am very grateful.

Thank you too to jeffrystables for replying. I had been to https://github.com/dreamfactorysoftware/dsp-core/wiki/Database-Retrieving-Records#get-filter to get guidance but the documentation was opaque (to me at least). Some examples would be helpful. I am willing to do it.

So would it be OK for me to edit the gitHub page to give the kind of example I am looking for?

5

Certainly, and we would appreciate your contribution! Please make a pull request for the wiki page, and once you’ve made your changes we will approve and push the updates.

6

Apologies.

After I sent the message I thought, “Let me see if I can do this …” and it turns out that (apparently) I can. I have made some edits on the wiki page: https://github.com/dreamfactorysoftware/dsp-core/wiki/Database-Retrieving-Records

So I have just done it. In future if I have something to contribute I will go through the procedure you suggested.

Hopefully, the additions are helpful. Feedback appreciated. This is the first time I have made a documentation contribution to any FOSS project.

1 Like
7

Thanks @gdvallance. Looks good.

8

Hello,

How can use the like filter without any case sensitive. Please any one help this,

For your reference:

_table/users?filter=username%20like%20%25Ha%25

Actual result-—> Ha*

Expected(Both) ----> Ha* & ha*

How can i do this?

9

_table/users?filter=(username%20like%20%25Ha%25)%20OR%20(username%20like%20%25ha%25)
would work

10

Hi Drew,

Thanks for your response,

But in V2.1.6 and above like filter is not working?

I was checked upto V2.2.3 still like filter not working

11

Please provide the exact API call you are making and the exact error received.

12

Hi Drew,

I’m using the Dreamfactory V2.2.3.

API Call: http://52.32.88.219/api/v2/dream_service/_table/users?filter=username%3Dhariharaprabu

Above Request is woking Fine.

DreamFactory.png1307×633 60.5 KB

///////////////

If i’m using filter by using like,

http://52.32.88.219/api/v2/dream_service/_table/users?filter=username%20like%20%hari%

Which is empty result.

DreamFactory_1.png1307×633 54.6 KB

I Hope you will get my point…!

13

I looking forward your response :slight_smile:

14

looks like a bug. I have filed this with the engineering team.

This only affects MongoDB services. Other filters using LIKE should still work.

1 Like
15

Is there any possible to fix it myself ??

Because I’m in closer to finish project. But It was stopped due to this kind of issue!!!

But Still love your product.

1 Like
16

Hey @Hariharaprabu_M, thanks for being a part of the community!

I can report that this issue is a major priority, and the bug has been filed.

I hope to give you a status update soon.

Thanks for your patience!
@AlexBowen

17

Hey @Hariharaprabu_M, I wanted to give you a quick update.

We should be able to get this in one of our next Sprints over the next few months.

In the mean time, you can use verb tunneling to pass a native MongoDB query as JSON.

POST /api/v2/mongodb/_table/todo?method=GET

with payload

{“filter”:{mongodb filter json here}}
18

Hi Alex & Drew,

Is there any updates related on LIKE filtering.

looking forward your answer.

2 Likes
19

This is in the current sprint, which is scheduled to conclude the first week of October.

3 Likes
20

Hi Drew,

Looking for your response…!!:slight_smile: