Pagination using SQL Query

In this lesson we will create a sql statement with join, execute it using WB ui for demonstration. Later, we will use an API through a function to exercise the same call.

The previous example showed how to fetch a list of objects from a database table using Trillo Workbench. There are times when we want to fetch a list and for each object its related object. In the database terms, it is achieved using a join statement.

Code: /lessons/Pagination_using_SQL_Query

Steps

1. Let us say we have to paginate through a list of customers and at the same time fetch all line items of each customer and link with it.

2. The following query fulfills the requirement.

SELECT
  c.id AS customer_id,
  c.firstName AS first_name,
  c.lastName AS last_name,
  li.id AS line_item_id,
  li.description,
  li.price
FROM
  Customer_tbl c
JOIN
  LineItem_tbl li ON c.id = li.customerId
WHERE
  c.id = {{{id}}};

3. In the above query {{{id}}} is a template variable. Its value will be replaced by a value passed in parameter, such as (id = 1).

4. Let us see how it works in the workbench.

5. Select Queries from the main navigation of the workbench.

6. Using New Query, create a new query.

7. Give it some name.

8. Enter your query and execute it.

9. Examine the result.

10. The following steps show how to fetch the same tree using a function.

11. Let us make a copy of the above function as PaginationBySQLQuery.

12. Set DataRequest.sql parameter as follows. It will instruct WB to give preference to the SQL statement for fulfilling the request.

dataRequest.setSql(<sql statement>);

13. Execute function and examine the result.