Query Slicer logic will enable the provider to push down separate requests for each filter value using the IN clause.

This enables the provider to avoid client side filtering.

Setting up Query Slicer Logic

Open the RSD where Query Slicer logic will be implemented.

See the example apiscript below.

<api:info title="launches" desc="List launches." other:queryslicercolumn="flight_id" xmlns:other="http://apiscript.com/ns?v1">

<attr name="flight_id"                   xs:type="integer"  readonly="false"              other:xPath="/json/flight_id"   other:filter="{flight_id}"  />
<attr name="id"                          xs:type="integer"  readonly="false"              other:xPath="/json/id"                                      />

<api:check attr="_input.flight_id">
  <api:set attr="URI" value="http://example.com?flight_number={flight_id}"/>
</api:check>

With this configuration, the following query will now dynamically pass down separate requests for each filter value:

SELECT * FROM launches WHERE flight_id IN ('1', '2', '3')

Additionally, you can use a sub-SELECT query in the filter to pass a dynamic list of filters:

SELECT * FROM launches WHERE flight_id IN (SELECT id FROM flights)

In order for this function to work, you need to set other:queryslicercolumn="flight_id" in the child table's, in this case 'launches', RSD only.

Limits and Considerations

In a script, most of the places that access a sliced input will not return single elements. For example, if you do something like this within your GET block trying to access the sliced value of the ID field:

<api:set attr="URI" value="http://example.com/[_input.id]" />

This won't work and will give you back a URL containing the full SQL list (e.g. http://example.com/(1, 2, 3)) instead of just a single ID.

The only place in the script that you can use sliced IDs is in URIs, because the brace bits are expanded at a later stage where the sliced input is actually available:

<api:set attr="URI" value="http://example.com/{id}" />

Automatic Query Slicing

If the table requires a sub-SELECT on another table, this process can be automated. Extending the above example, the 'launches' table must always slice on the flight_id, requiring the following query:

SELECT * FROM launches WHERE flight_id IN (SELECT id FROM flights)

In order to automatically perform this slicing, the following must be set in the child table's RSD.

• other:queryslicercolumn: The child table's column that needs to be filtered
• other:slicetable: The parent table
• other:sliceforeignkey: The parent table's column that supplies the filter

In this case, you need to set other:queryslicercolumn="flight_id" other:slicetable="flights" other:sliceforeignkey="id" in the 'launches' RSD. A simple SELECT will now automatically execute the sub-SELECT on the parent table:

SELECT * FROM launches

Note that multiple columns can be specified for slicing by specifying the queryslicercolumn and sliceforeignkey as semicolon-separated lists. For example, there may be a third table, 'tasks', which needs to slice on the id of launches, which in turn needs to slice on the id of flights, requiring the following query:

SELECT * FROM tasks where launch_id IN (SELECT Id FROM launches WHERE flight_id IN (SELECT id FROM flights))

In this case, the third table script will need to include both the launch_id and flight_id as slicable columns, along with their respective foreign keys in the launches table:

<api:info title="tasks" desc="List tasks for a launch." other:queryslicercolumn="launch_id;flight_id" other:slicetable="launches" other:sliceforeignkey="id;flight_id" xmlns:other="http://apiscript.com/ns?v1">

<attr name="flight_id"                   xs:type="integer"  readonly="false"              other:xPath="/json/flight_id"   other:filter="{flight_id}"  />
<attr name="launch_id"                   xs:type="integer"  readonly="false"              other:xPath="/json/launch_id"   other:filter="{launch_id}"  />
<attr name="id"                          xs:type="integer"  readonly="false"              other:xPath="/json/id"                                      />