Query Builder join on parameter value not column

Wappler General Need Help
1

I need to use a query such as

SELECT 
    t.*, 
    eo.count 
FROM 
    taxa t
LEFT JOIN 
    event_occurrences eo 
ON 
    eo.taxon_id = t.taxon_id 
   AND eo.event_id = 10;

which is not possible in Query Builder as it only allows JOINs on columns and not parameter values: https://community.wappler.io/t/left-join-on-parameter-value/27051.
An alternative equivalent query would be

SELECT 
    t.*, 
    eo.count 
FROM 
    taxa t
LEFT JOIN 
    (SELECT * FROM event_occurrences WHERE event_id = 10) eo 
ON 
    eo.taxon_id = t.taxon_id;

but this also seems not to be possible as any query is added at the end, and I can't see how to join a subquery.
I could do this with a custom query but I require paging so would prefer to use a paged query with the Query Builder.
Any suggestions?

2

If I do have to use a custom query and implement my own paging, I assume I can define limit and offset parameters and use them in the query. I also assume I'd need to define the output in the same JSON structure as a paged query for App Connect to use a Pagination List. I couldn't work out how to do this: any examples?

3

You should be able to use a Custom Query then on your project front end put them in a data view which will give you access to paging etc if I understand what you are trying to achieve.

4

You can absolutely do pagination with a custom query, we only use custom queries. But it does require a little bit of extra work.

You can use query parameters to define your pagination limit and offset like this.

SELECT 
    t.*, 
    eo.count 
FROM 
    taxa t
LEFT JOIN 
    event_occurrences eo 
ON 
    eo.taxon_id = t.taxon_id 
   AND eo.event_id = 1
   
LIMIT :P_LIMIT OFFSET :P_OFFSET;

Then assign $_GET.offset to the :P_OFFSET parameter etc. We add in additional logic in the expression so that if they are empty it will pre-fill them with default values, similar to this:

:P_LIMIT = ($_GET.limit? $_GET.limit : '25')
:P_OFFSET = ($_GET.offset ? $_GET.offset : '0')

The next tricky bit is the output. We handle this by using the Response action, in the value we send a json formatted string like this below. Because it's a custom query you have to handle the response.

We handle our own pagination a little differently so we have different values to what a regular database query action would return, but you could easily make the json structure the same with the same key => values.

{
    "payload": "{{custom_query}}",
    "pagination": {
        "limit": "{{$_GET.limit}}",
        "offset": "{{$_GET.offset}}"
    }
}

Hopefully that should get you started in the right direction. Since we use them everywhere we made our life a little easier by creating libraries that handle all of the pagination settings and values, and how it returns data.

Also, you should definitely only return the rows that you need on the frontend (hence the pagination). Don't return everything and then filter it on the frontend - that will cause a bad user experience by slowing your app down sending potentially 1000's or 100,000's of rows of data.

2 Likes
5

Thanks, that's helpful. I've got the basics working this way. Need to figure out the values for the page object for this, but it looks promising!

6

I'm sure it must be possible to do this more simply if you know more than I do, but I got this working with the following

image
image753Ă—1403 110 KB

7

There is this if still supported and I think I read it is now free