Taking a closer look at the Publisher Query Language and the future

Recently we launched v201004, and with that introduced the new Statement object which gives support for one of our top requested features - bind variables. In this blog post, we will take a closer look at the Publisher Query Language (PQL), Statements with bind variables, and what we have in store for the future.

Publisher Query Language

PQL plays a very significant role in the DFP API by providing the developer with a robust way of filtering which objects should be retrieved or modified before the request is completed. In other words, if you would like to retrieve only orders which are in the draft state, you could take one of two approaches. You could fetch all orders within your network and filter them one by one or instruct the server to only fetch orders in the draft state before returning all results. By doing the latter, the DFP API allows developers to create smaller and more direct requests, and, in turn, increases the efficiency of their code.

PQL has a very similar syntax to SQL, but does not include keywords such as SELECT or FROM; they are implied by the method which uses the PQL statement. The following piece of code constructs a Statement capable of fetching orders in the draft state and retrieves those orders:

  // Create a statement to only select orders in the
  // 'DRAFT' state. 

  Statement filterStatement = new Statement();

  filterStatement.setQuery("WHERE status = 'DRAFT' LIMIT 500");

  OrderPage orderPage =
      orderService.getOrdersByStatemet(filterStatement);

The documentation included for each "get*ByStatment" (e.g. getOrdersByStatement) method indicates which PQL fields map to which object properties.

Paging

The result for "get*ByStatment" calls are pages specific to the service; i.e. an OrderPage is returned by getOrdersByStatement. The limit for the number of objects that can be fetched for a single PQL request, and in a single Page, is 500. Because of this, you should always include LIMIT 500 in your statement. However, if you would like to fetch more than 500 objects, you will need to page through the results by including an OFFSET <#> in your statement as well. To page through orders in groups of 500, for example, in your statement, you would include LIMIT 500 as well as an OFFSET of an interval of 500.

This can be represented by the following code:

  // Sets defaults for page and filter.  

  OrderPage page = new OrderPage();

  Statement filterStatement = new Statement();

  int offset = 0;

  do {

    // Create a statement to get all orders.

    filterStatement.setQuery(
        "WHERE status = 'DRAFT' LIMIT 500 OFFSET " + offset);

    // Get orders by statement.

    page = orderService.getOrdersByStatement(filterStatement);

    if (page.getResults() != null) {

      int i = page.getStartIndex();

      for (Order order : page.getResults()) {

        System.out.println(i + ") Order with ID \""

          + order.getId() + "\", name \"" + order.getName()

          + "\", and advertiser ID \"" + order.getAdvertiserId()

          + "\" was found.");

        i++;

      }

    }

    offset += 500;

  } while (offset < page.getTotalResultSetSize());

The loop will end when there are no pages left, i.e. the offset is greater than or equal to the to the total result set size.

Bind variables

Bind variables were recently introduced to allow for reusing of the same template PQL statement combined with varying parameters. To change the PQL statement above to differ which status is being selected, 'DRAFT' is changed to the bind variable status, represented by :status. Note that bind variables can be any name - not just the name of their property. We chose :status here for simplicity.

  // Create a statement to only select orders in the state
  // bound to status.

  Statement filterStatement = new Statement();

  filterStatement.setQuery("WHERE status = :status LIMIT 500");

To bind to :status, you will need to create a parameter map with a String_ParamMapEntry coupling :status with a StringParam. Note that ":" is not included in the bind variable name in the parameter map.

  // Create the string parameter.
  StringParam stringParam = new StringParam();

  // Create bind parameters map.
  String_ParamMapEntry[] paramMap = new String_ParamMapEntry[] {
      new String_ParamMapEntry("status", stringParam)
  };

  filterStatement.setParams(paramMap);

 Before you make the call to getOrdersByStatement, set the stringParam value to the specific status.

  stringParam.setValue("DRAFT");

In this case, because status was bound to a variable declared before the parameter map, you can set the variables value at any time.

The first iteration of the while loop above would then produce the following XML snippet:

  ...
  <filterStatement>

    <query>WHERE status = :status LIMIT 500 OFFSET 0</query>

    <params>

      <key>status</key>

      <value  xmlns:ns2=
          "https://www.google.com/apis/ads/publisher/v201004"  
          xsi:type="ns2:StringParam">

        <value>DRAFT</value>

      </value>

    </params>

  </filterStatement>

  ...

Planned features and production

The release of v201004 was the culmination of the last step before we can begin rolling out access to the production API and we will have more information about signing up to for production use in the coming weeks. We are still hard at work on the forecasting and reporting service and will have some news about those in the following weeks as well.

We appreciate all of the great feedback we've been receiving on the forum, both on the API and the client libraries, and we''ll continue to incorporate it as we continue development.

-- Adam Rogal, The DoubleClick for Publishers API Team