Help Needed: Paged Query Converted to Custom Query

[scott]

I need assistance on how to exactly utilize the features of a filter paged query in a custom query.

I was able to figure out how to use Limit properly, but I am at a loss as to how to use Filter, Offset, Sort, and Dir.

Server Actions
Filter, Offset, Sort, and Dir are listed in the Global $_GET.
I have added them in the parameters in the custom query.
I was not able to test them because I do not know what the test values should be.

App Structure
Filter, Offset, Sort, and Dir are listed in the Input Parameters of the Server Connect. The Offset, Sort, and Dir values are empty. Filter has a value of the text search field on the page.

Any assistance would greatly be appreciated.

[Hyperbytes]

Sort and Dir and used by the supportable headers function for bootstrap tables, Sort if the field (column) , dir is the direction (Asc, Desc),

As you have discovered Limit is the number of columns returned and aligns to the SQL LIMIT keyword and offset is the file offfset from where to retrieve those records

They basically align directly with the SQL LIMIT and OFFSET clauses

[scott]

@Hyperbytes,

Thank you so much for your reply. Do you know how to utilize the parameters in the custom query and then on the app connect side?

For example, the filter parameter used in a text field. I have it set in $_Global and as :P1 in the parameters of the custom filter. On the app connect side, I have the search text field value selected for the filter in the server connect.

But when I add the :P1 to a where clause I get no results. I have tried :P1, ‘:P1’, ‘%:P1%’ with no success.

[Hyperbytes]

I have to be honest, never tried but they are just $_GET parameters so I assume it will be something like this:

[scott]

Here is what I am trying to accomplish.

The following does not work:

WHERE sp_shop_products.sp_product_name LIKE :P1
WHERE sp_shop_products.sp_product_name LIKE ‘:P1’
WHERE sp_shop_products.sp_product_name LIKE %:P1%
WHERE sp_shop_products.sp_product_name LIKE ‘%:P1%’

[Hyperbytes]

So which part is the issue? Is it definatley the where clause?
I cant help on such limited info

1. is the main query actually returning results (without the where, order, limit)
2. if so add the where to confirm that is the issue, is “2018” part of the product name?

Assuming you are replicating the “contains” condition then try adding a set value stage, set a variable to ‘%’+$_GET.filter+’%’ to add the wild cards then use that variable as the condition for :P1 which will have the wildcards already added

Nothing tested, just ideas of the top of my head

[scott]

The query works without the WHERE clause

The query works with a WHERE clause LIKE ‘%2018%’

The issue is with the parameter :P1

I changed the variable from {{$_Get.filter}} to ‘%’+$_GET.filter+’%’ with no success.

[Hyperbytes]

Did you use a set value stage as suggested or just edit that in directly into the query?

[scott]

Is this what you mean?

[Hyperbytes]

OK, add this step to your server action (checked the syntax this time, this should be correct)

This will append ‘%’ to the start and end of 2018 resulting in a variable with the value “%2018%”

Then in the custom query use that variable as the search element. May not work but worth a try.

[scott]

Sadly that did not work.

[Hyperbytes]

Not sure what is going on then.

I have modelled your issue (using addresses in this case)

So this passes a filter to the server action

I then set the filter variable

Then add that variable to the custom query

This returns all addresses containing “street”

Or Avenue

So this works in my test

[scott]

I see now. I was not testing on the page itself. I was using the execute within the custom query builder.

@Hyperbytes Thank you so much !!! In the future, I’ll test the page.