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

This enables the add-in 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"                                      />