Paging support in the data service layer

Paging is used to return multiple "pages" of data when it is necessary to retrieve only a certain number of entries at a time. The data service layer supports paging of data, as well as setting result limits for paging.

Paging is generally accomplished by using two step queries. In the first stage of a two-step query, the search is performed, and the primary keys of the result set are found. This corresponds to the XPath search. In the second stage, the detailed information for each row in the result set are fetched. This corresponds to the access profile.

Paging can sometimes be used for a single-step query, if it returns one record per primary key of the base table.

Setting a result limit for paging for the entire service module

In the wc-component.xml service module configuration file, you can define the "maximumPagingResultLimit" attribute. For example:

<_config:dataservice dataMediatorType="JDBC" 
	metadataClass="com.ibm.commerce.catalog.example.server.metadata.CatalogExampleMetadata" 
	maximumPagingResultLimit="5000">
</_config:dataservice>

When paging is requested by the client, querying more rows than specified by the maximumPagingResultLimit will result in an exception.

Setting a maximum page size for the entire service module

In the wc-component.xml service module configuration file, you can define the "maximumPageSize" attribute. For example:

<_config:dataservice dataMediatorType="JDBC" 
	metadataClass="com.ibm.commerce.catalog.facade.server.metadata.CatalogExampleMetadata" 
	maximumPageSize="100">
</_config:dataservice>

Setting the "maximumPageSize" attribute to -1 enables a default behavior where a limit is set on the maximum size of the result set. This limit is defined by the "maximumPagingResultLimit" attribute.

How is paging supported by the query template

If you have particular query which you want to have a smaller or bigger limit, you can define a paging_result_limit in the SQL_STATEMENT section of your query template. For example, in the sample below, the paging_result_limit is set to 50:

BEGIN_XPATH_TO_SQL_STATEMENT
       <!-- fetch all CATENTRY records given a set of part numbers -->
       <!-- xpath = "/CatEntry[@catEntryId<123]" -->
       name=/CatEntry[@catEntryId<]+CatalogPaging
       paging_result_limit=50
       entity_table=CATENTRY
       dbtype=db2
       sql=  
             		SELECT  CATENTRY.$COLS:CATENTRY$
                      FROM CATENTRY WHERE CATENTRY.CATENTRY_ID < ?catEntryId? AND                            										
               CATENTRY.MARKFORDELETE = 0 ORDER BY CATENTRY.CATENTRY_ID
              
       paging_count
       sql =
              SELECT  
              	COUNT(*) as ct
              from 
                CATENTRY 
              where 
              	CATENTRY.CATENTRY_ID < ?catEntryId? AND                                                 
                            CATENTRY.MARKFORDELETE = 0
END_XPATH_TO_SQL_STATEMENT

paging_count SQL statement

If you are paging within a single step query, you need to provide a paging_count SQL statement. The paging count SQL statement should return the total number of unique records from the base table returned by your XPath to SQL template. If the number of the results exceeds the paging limit, an exception is thrown. An example of the paging_count SQL is shown in the following sample:

       paging_count
       sql =
              SELECT  
              	COUNT(*) as ct
              from 
                CATENTRY 
              where 
              	CATENTRY.CATENTRY_ID < ?catEntryId? AND                                                 
                            CATENTRY.MARKFORDELETE = 0

Note:

The paging_count SQL statement must be used for single-step queries.
The paging_count SQL statement is not required for two-step queries.
Parametric search queries should not specify the paging_count SQL statements.

Paging support for noun parts

Most XPath queries are paging on nouns. In this case, the data service layer is paging on the unique identifiers of the base table mapped to the given noun. In some cases, it is necessary to page on noun parts within the noun (for example, the merchandising associations of a product). In this case, a noun and a number of noun part records may be requested by the client. The number of requested noun parts is defined by the page size.

The naming convention for an XPath query paging on a noun part is /Noun[<predicate>]/NounPart.

Paging on a noun part is accomplished by using two step query. In the first stage1 of the two-step query , the first row and second column in the SELECT statement of the XPath to SQL template must be the primary keys of the entity and sub-entity tables respectively. In the second stage 2, the primary keys of the sub-entity table that is fetched by the XPath to SQL template in the first stage is injected into the association query that retrieves data from the sub-entity table. The $SUBENTITY_PKS$ tag specifies the location where the keys need to be inserted.

The following examples shows how paging is supported for the merchandising associations and attributes of a product.

1. Paging on merchandising associations of a product.

<!-- here we are paging on an Association subnoun within a CatalogEntry noun -->

1BEGIN_XPATH_TO_SQL_STATEMENT
name=/CatalogEntry[CatalogEntryIdentifier[UniqueID=]]/Association
base_table=CATENTRY

	
sql=
  SELECT 
    DISTINCT CATENTRY.$COLS:CATENTRY_ID$, MASSOCCECE.$COLS:MASSOCCECE_ID$, 
    MASSOCCECE.RANK	    					
  FROM
     CATENTRY, MASSOCCECE			
WHERE
   CATENTRY.CATENTRY_ID=MASSOCCECE.CATENTRY_ID_FROM AND
   CATENTRY.CATENTRY_ID=?UniqueID?
   ORDER BY MASSOCCECE.RANK DESC, MASSOCCECE.MASSOCCECE_ID ASC
        
          
END_XPATH_TO_SQL_STATEMENT 

2BEGIN_ASSOCIATION_SQL_STATEMENT
	name=IBM_CatalogEntryMerchandisingAssociations
	base_table=MASSOCCECE
	additional_entity_objects=true
	sql=
		SELECT 
			MASSOCCECE.$COLS:MASSOCCECE$
		FROM
			MASSOCCECE
	    WHERE
	        MASSOCCECE.MASSOCCECE_ID IN ($SUBENTITY_PKS$) AND
        	MASSOCCECE.CATENTRY_ID_FROM IN ($UNIQUE_IDS$) AND
        	MASSOCCECE.STORE_ID IN ($STOREPATH:catalog$) 
        	
        ORDER BY
        	MASSOCCECE.RANK, MASSOCCECE.MASSOCCECE_ID ASC
	               	
END_ASSOCIATION_SQL_STATEMENT

BEGIN_PROFILE 
	name=IBM_Admin_CatalogEntryMerchandisingAssociations
	BEGIN_ENTITY 
	  base_table=CATENTRY 
	  className=com.ibm.commerce.catalog.facade.server.services.dataaccess.graphbuilderservice.CatalogEntryGraphComposer	  
	  associated_sql_statement=IBM_RootCatalogEntry	  
	  associated_sql_statement=IBM_CatalogEntryDescription	  
      associated_sql_statement=IBM_CatalogEntryMerchandisingAssociations
      associated_sql_statement=IBM_AssociatedCatalogEntrySummary      
    END_ENTITY
END_PROFILE

2. Paging on product attributes.

<!-- here we are paging on a subnoun (Attribute) within a CatalogEntry noun -->
<!-- paging and total count should continue to work properly            -->
1BEGIN_XPATH_TO_SQL_STATEMENT
	name=/CatalogEntry[CatalogEntryIdentifier[UniqueID=]]/Attributes
	base_table=CATENTRY

	
    sql=
		SELECT 
			    DISTINCT CATENTRY.$COLS:CATENTRY_ID$, ATTRIBUTE.$COLS:ATTRIBUTE_ID$, SEQUENCE, DESCRIPTION	    					
		FROM
				CATENTRY, ATTRIBUTE			
		WHERE
			   CATENTRY.CATENTRY_ID=ATTRIBUTE.CATENTRY_ID AND
			   CATENTRY.CATENTRY_ID=?UniqueID? AND
			   ATTRIBUTE.LANGUAGE_ID=$CTX:LANG_ID$
	    ORDER BY SEQUENCE DESC, DESCRIPTION DESC
        
          
END_XPATH_TO_SQL_STATEMENT 

2BEGIN_ASSOCIATION_SQL_STATEMENT
	name=IBM_CatalogEntryAttributesAndValuesAssocSQL
	base_table=CATENTRY
	sql=
		SELECT 
	            CATENTRY.$COLS:CATENTRY$, 
		    ATTRIBUTE.$COLS:ATTRIBUTE$, 
		    ATTRVALUE.$COLS:ATTRVALUE$
		FROM
		    CATENTRY, ATTRIBUTE, ATTRVALUE
		WHERE
               CATENTRY.CATENTRY_ID IN ($ENTITY_PKS$) AND               
               ATTRIBUTE.LANGUAGE_ID=$CTX:LANG_ID$ AND
               ATTRIBUTE.CATENTRY_ID=CATENTRY.CATENTRY_ID AND 
               ATTRIBUTE.ATTRIBUTE_ID=ATTRVALUE.ATTRIBUTE_ID AND
               ATTRVALUE.LANGUAGE_ID=ATTRIBUTE.LANGUAGE_ID AND
               ATTRIBUTE.ATTRIBUTE_ID IN ($SUBENTITY_PKS$)
        ORDER BY ATTRIBUTE.SEQUENCE DESC, ATTRIBUTE.DESCRIPTION DESC
END_ASSOCIATION_SQL_STATEMENT

BEGIN_PROFILE 
	name=IBM_Admin_ProductAttributes
	BEGIN_ENTITY 
	  base_table=CATENTRY 
	  associated_sql_statement=IBM_CatalogEntryAttributesAndValuesAssocSQL  
    END_ENTITY
END_PROFILE