Home / Documentation / User Guide 2.2 / XML Catalog file Schema Reference / <subquery>
«Prev   Next»
<subquery>

<subquery>

Abstract

Learn to configure the <subquery> element for Pull Reports™ Ad Hoc report and data service software.

Table of Contents

Catalog Configuration Java API analog:  SubqueryProvider, TableConfigurationBuilder#setSubqueryProvider

The <subquery> element specifies a query to represent the parent <table> within the Export Report REST API SQL from clause instead of the parent <table>'s name attribute. The <subquery> value may be dynamically generated through the use of a Groovy template or factory class.

Usage

Basic <subquery> usage

To demonstrate <subquery> element usage, take with this simple XML Catalog file which specifies a base <table> named order containing two <column>s: item_number and item_name.

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="customer" name="Customer Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="order" name="Customer Orders">
        <table id="order" name="cust_order" displayName="Order"> 
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>

In this example, the following HTTP request to the Export Report REST API results in the following SQL query.

/[context]/pullreports/catalog/customer/report/order/export
select t1.item_number,t1.item_name from cust_order t1

Using a <subquery> permits more flexible query definition than does the <table> name attribute. For example, if another table in the database called canceled_orders contains those orders which are canceled, the following catalog configuration uses <subquery> to exclude the canceled orders from the export results.

Example 1. Via an XML Catalog file
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="customer" name="Customer Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="order" name="Customer Orders">
        <table id="order" displayName="Order"> 
            <subquery>
            select o.item_number,o.item_name 
            from cust_order o left outer join order_canceled oc on o.id = oc.id
            where oc.id is null
            </subquery>
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>

Note

The <subquery> select clause must contain all the <column> children of the parent <table>.


Example 2. Via the Catalog Configuration Java API

The following example is an identical Catalog Configuration Java API configuration.

package com.pullreports.examples.subquery;

import com.pullreports.model.CatalogId;
import com.pullreports.model.ColumnId;
import com.pullreports.model.ParamType;
import com.pullreports.model.ReportId;
import com.pullreports.model.Subquery;
import com.pullreports.model.SubqueryProvider;
import com.pullreports.model.TableId;
import com.pullreports.model.config.CatalogConfiguration;
import com.pullreports.model.config.CatalogConfigurationFactory;
import com.pullreports.model.config.ColumnConfiguration;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.TableConfiguration;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

public class BasicSubqueryCatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

        ColumnConfiguration itemNumberColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("item_number"),"item_number")
            .setDisplayName("Item Number").setParamType(ParamType.LONG).build();

        ColumnConfiguration itemNameColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("item_name"),"item_name")
            .setDisplayName("Item Name").build();

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
                itemNumberColumnConfiguration,itemNameColumnConfiguration);

        TableConfiguration orderTableConfiguration = new TableConfiguration.Builder(
            new TableId("order"),"Order",columnConfigurations)
            .setSubqueryProvider(new OrderSubqueryProvider()).build();

        ReportConfiguration orderReportConfiguration = new ReportConfiguration.Builder(
            new ReportId("order"),"Customer Orders",orderTableConfiguration).build();
        
        List<ReportConfiguration> reportConfigurations = Collections.singletonList(orderReportConfiguration);

        return new CatalogConfiguration(new CatalogId("customer"),"Customer Reports",reportConfigurations);
    }

    private final static class OrderSubqueryProvider implements SubqueryProvider {
        private static final String QUERY =    
            "select o.item_number,o.item_name " + 
            "from cust_order o left outer join order_canceled oc on o.id = oc.id " + 
            "where oc.id is null";

        @Override
        public Subquery createSubquery(HttpServletRequest hsr) {
            return new Subquery(){
                @Override
                public Optional<String> getValue(){
                    return Optional.of(QUERY);
                }
            };
        }
    }
}

Now, the same HTTP request to the Export Report REST API results in the following SQL query. Note the embedded subquery in the SQL from clause.

/[context]/pullreports/catalog/customer/report/order/export
select t1.item_number,t1.item_name 
from 
(select o.item_number,o.item_name 
 from cust_order o left outer join order_canceled oc on o.id = oc.id
 where oc.id is null) t1

Adding a <description> to a <subquery>

Adding a child <description> element to a <subquery> will output that description within Export Report REST API if the parent <table> is included in the columns parameter. A child <description> element is typically used to communicate the subquery behavior to Export Report REST API users.

See the format parameter documentation for how each export format outputs <subquery> descriptions.

Example 3. Via an XML Catalog file
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="customer" name="Customer Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="order" name="Customer Orders">
        <table id="order" displayName="Order"> 
            <subquery>
                <description>Only orders that are not canceled are present in the export result.</description>
                select o.item_number,o.item_name 
                from cust_order o left outer join order_canceled oc on o.id = oc.id
                where oc.id is null
            </subquery>
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>

Example 4. Via the Catalog Configuration Java API

The following example is an identical Catalog Configuration Java API configuration.

package com.pullreports.examples.subquery;

import com.pullreports.model.CatalogId;
import com.pullreports.model.ColumnId;
import com.pullreports.model.ParamType;
import com.pullreports.model.ReportId;
import com.pullreports.model.Subquery;
import com.pullreports.model.SubqueryProvider;
import com.pullreports.model.TableId;
import com.pullreports.model.config.CatalogConfiguration;
import com.pullreports.model.config.CatalogConfigurationFactory;
import com.pullreports.model.config.ColumnConfiguration;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.TableConfiguration;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

public class BasicSubqueryWithDescriptionCatalogConfigurationFactory implements CatalogConfigurationFactory {

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

        ColumnConfiguration itemNumberColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("item_number"),"item_number")
            .setDisplayName("Item Number").setParamType(ParamType.LONG).build();

        ColumnConfiguration itemNameColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("item_name"),"item_name")
            .setDisplayName("Item Name").build();

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
                itemNumberColumnConfiguration,itemNameColumnConfiguration);

        TableConfiguration orderTableConfiguration = new TableConfiguration.Builder(
            new TableId("order"),"Order",columnConfigurations)
            .setSubqueryProvider(new OrderSubqueryProvider()).build();

        ReportConfiguration orderReportConfiguration = new ReportConfiguration.Builder(
            new ReportId("order"),"Customer Orders",orderTableConfiguration).build();
        
        List<ReportConfiguration> reportConfigurations = Collections.singletonList(orderReportConfiguration);

        return new CatalogConfiguration(new CatalogId("customer"),"Customer Reports",reportConfigurations);
    }

    private static final class OrderSubqueryProvider implements SubqueryProvider {
        private static final String QUERY =    
            "select o.item_number,o.item_name " + 
            "from cust_order o left outer join order_canceled oc on o.id = oc.id " + 
            "where oc.id is null";

        private static final String DESCRIPTION =    
            "Only orders that are not canceled are present in the export result.";

        @Override
        public Subquery createSubquery(HttpServletRequest hsr) {
            return new Subquery(){
                @Override
                public Optional<String> getValue(){
                    return Optional.of(QUERY);
                }
                
                @Override
                public Optional<String> getDescription(){
                    return Optional.of(DESCRIPTION);
                }
            };
        }
    }
}

Dynamically creating the <subquery> with a Groovy Template

The <subquery> element may contain any construct supported by Groovy's SimpleTemplateEngine. For instance, expressions embedded within the <subquery> may use Groovy or Java operators and invoke static method calls such as java.lang.System.getProperty(String). Additionally, the HttpServletRequest associated with the Export Report REST API request is available as a the request template binding variable. This allows the <subquery> value to be responsive to HttpServletRequest state.

In the following <subquery> example, the subquery uses both a java.lang.System property and an HttpSession attribute. The example assumes both the System property and HttpSession attribute are set by external-to-Pull Reports™ configuration. For instance, java.lang.System properties may be set by passing a -D prefixed argument to the JVM on start up. HttpSessionListeners are commonly used to set HttpSession attributes.

Example 5. Via an XML Catalog file
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="customer" name="Customer Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="order" name="Customer Orders">
        <table id="order" displayName="Order"> 
            <subquery>
            select o.item_number,o.item_name 
            from cust_order o 
            where 
            o.item_date > '${System.getProperty('min.item.date')}'
            and
            o.user_id = ${request.session.getAttribute('user.id')}
            </subquery>
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>

Assuming the JVM was passed -Dmin.item.date=1976-04-23 on start up and the user.id session attribute has a value of 100, the following request to the Export Report REST API results in the following SQL query.

/[context]/pullreports/catalog/customer/report/order/export
select t1.item_number,t1.item_name 
from 
(select o.item_number,o.item_name 
from cust_order o 
where 
o.item_date > '1976-04-23'
and
o.user_id = 100) t1

Dynamically creating the <subquery> with a provider class

If a Groovy template is not expressive enough to create the <subquery> value, use the provider attribute to specify the fully qualified name of a Java class which implements SubqueryProvider to create the <subquery>.

In the following <subquery> example, the <table> element specifies a name attribute that will be used within the Export Report REST API SQL from clause if the createSubquery method of the SubqueryProvider class returns null.

Example 6. Via an XML Catalog file
<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="customer" name="Customer Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="order" name="Customer Orders">
        <table id="order" name='cust_order' displayName="Order"> 
            <subquery provider='com.pullreports.examples.subquery.OrderSubqueryProvider'/>
            <column id="item_number" name="item_number" displayName="Item Number" paramType="java.lang.Long"/>
            <column id="item_name" name="item_name" displayName="Item Name"/>
        </table>
    </report>
</catalog>

Example 7. The OrderSubqueryProvider class
package com.pullreports.examples.subquery;

import com.pullreports.model.Subquery;
import com.pullreports.model.SubqueryProvider;

import javax.servlet.http.HttpServletRequest;
import java.util.Optional;

public class OrderSubqueryProvider implements SubqueryProvider {

    @Override
    public Subquery createSubquery(HttpServletRequest request){

        return new Subquery(){
            @Override
            public Optional<String> getDescription() {
                if (request.isUserInRole("SUPERUSER")){
                    return Optional.empty();
                } else {
                    return Optional.of("The result is restricted to your orders.");
                }
            }
            @Override
            public Optional<String> getValue() {
                if (request.isUserInRole("SUPERUSER")){
                    // Superusers may see all records. Returning empty will use the
                    // <table>'s name attribute.
                    return Optional.empty();
                } else {
                    return Optional.of("select o.item_number,o.item_name " +
                    "from cust_order o " + 
                    "where " + 
                    "o.user_id = " + request.getSession().getAttribute("user.id"));
                }
            }
        };
    }
}

Children

<description>?

Parents

<table>

Attributes

provider

The fully qualified name of a Java class which implements SubqueryProvider to create the subquery value.

If the provider attribute is specified, the <subquery> element may not have content.

© Full Draft, LLC All Rights Reserved | Privacy Policy | Terms of Use