Really puzzled, impossible to filter my query on endpoint

38 views
Skip to first unread message

Nicolas Bouillon

unread,
Jun 15, 2022, 5:00:38 PM6/15/22
to Fusio
Hi there,

I set up the following:
- route: /bank
 - parameters: SQL-Table-Parameters
- response: Bank-Collection
- action: Bank-Query-All

My Bank-Query-All is like this:
{ "connection": "2", "sql": "SELECT id, code, name, country\nFROM banks", "limit": 10000 }
And was set up automatically during creation of query on table

I send my request with a valid token and here is the output of curl:
* Trying ::1:80...
* TCP_NODELAY set
* Connected to localhost (::1) port 80 (#0)
> GET /fusio/public/bank?filterBy=name&filterOp=contains&filterValue=AXA HTTP/1.1
Host: localhost
User-Agent: Mozilla/5.0 (Windows NT 6.2; WOW64; rv:17.0) Gecko/20100101 Firefox/17.0
Accept: */*
Authorization: Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJodHRwOlwvXC9sb2NhbGhvc3RcL2Z1c2lvXC9wd<blablablablablabla>mV4cCI6MTY1NTQ5OTMwNSwibmFtZSI6IktBU0JBLXVzZXIifQ.cOLZi6It3re_Ihi8gHzbp9uSnWlBmU-XvkDg3TWZ-HA

* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Date: Wed, 15 Jun 2022 20:55:05 GMT
< Server: Apache
< Vary: Accept
< X-Powered-By: psx
< x-ratelimit-limit: 720
< x-ratelimit-remaining: 703
< warning: 199 PSX "Resource is in development"
< Transfer-Encoding: chunked
< Content-Type: application/json
<
* Connection #0 to host localhost left intact

And the result is ALL the records, no filter just if nothing was passed.


{
  "totalResults": 18,
  "itemsPerPage": 10000,
  "startIndex": 0,
  "entry": [
    {
      "id": 1,
      "code": "UNK",
      "name": "-- Unknown --",
      "country": "-- Unknown --"
    },
    {
      "id": 2,
      "code": "AGE",
      "name": "Ageas",
      "country": "Belgium"
    },
    {
      "id": 3,
      "code": "AXA",
      "name": "AXA Bank",
      "country": "Belgium"
    },
    {
      "id": 4,
      "code": "BFS",
      "name": "Belfius",
      "country": "Belgium"
    },
    {
      "id": 5,
      "code": "BNP",
      "name": "BNP Paribas Fortis",
      "country": "Belgium"
    },
    {
      "id": 6,
      "code": "BPO",
      "name": "B.POST",
      "country": "Belgium"
    },
    {
      "id": 7,
      "code": "DBB",
      "name": "Deutsche Bank S.A.",
      "country": "Belgium"
    },
    {
      "id": 8,
      "code": "ETH",
      "name": "Ethias",
      "country": "Belgium"
    },
    {
      "id": 9,
      "code": "IBB",
      "name": "Interactive Brokers",
      "country": "Germany"
    },
    {
      "id": 10,
      "code": "ING",
      "name": "ING Belgium",
      "country": "Belgium"
    },
    {
      "id": 12,
      "code": "KBC",
      "name": "KBC Bank",
      "country": "Belgium"
    },
    {
      "id": 13,
      "code": "RAB",
      "name": "Rabobank",
      "country": "Belgium"
    },
    {
      "id": 14,
      "code": "SKR",
      "name": "Skrills",
      "country": "USA"
    },
    {
      "id": 15,
      "code": "WFA",
      "name": "Well's Fargo",
      "country": "USA"
    },
    {
      "id": 16,
      "code": "HBP",
      "name": "Hello Bank",
      "country": "Belgium"
    },
    {
      "id": 17,
      "code": "VB",
      "name": "Van Breda",
      "country": "Belgium"
    },
    {
      "id": 18,
      "code": "ALL",
      "name": "Allianz",
      "country": "Belgium"
    }
  ]
}

I'm lost, I don't find any documentation or example on how to filter data from a select on a table without custom action.

Can you help please?

Cheers
Nico

Christoph Kappestein

unread,
Jun 15, 2022, 5:20:56 PM6/15/22
to Fusio
Hi Nico,

sure, so if you use the "SQL Query All" action you need to add the filter to your condition i.e.
"SELECT id, code, name, country FROM banks WHERE code = {code}" then you could simply use the query parameter "?code=AGE" to filter a specific entry. You can find the documentation at:
https://docs.fusio-project.org/docs/backend/api/action/sql-query-all/

If you use the "SQL Select All" action then you can use those filterBy, filterOp and filterValue query parameters.

best regards
Christoph

Nicolas Bouillon

unread,
Jun 15, 2022, 5:27:05 PM6/15/22
to Fusio
Christoph,

You are amazing !!! :D

It f.... works!!!

That was my mistake and I read the documentation, I know understand the logic behind it, it makes sense.
A really big thank you
And thanks for the super fast reply, it's just great to be able to move forward quickly.

Nico

Reply all
Reply to author
Forward
0 new messages