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}}};

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

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

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

4. Using New Query, create a new query.

5. Give it some name.

6. Enter your query and execute it.

7. Examine the result.

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

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

10. 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>);

1. Execute function and examine the result.