PXSelect Classes

In traditional business query language (BQL), you define a data view or request database data in code by using one of the PXSelect classes (that is, the classes derived from PXSelectBase).

PXSelect Classes

The instances of PXSelect classes are complex objects containing the following:
  • A reference to the PXView object instantiated to process the data query
  • A reference (through the PXView object) to the Select object, which is the business query language (BQL) command to be executed
  • A reference to the graph
  • A reference to the cache of the data access class (DAC) type that is specified in the first type parameter of PXSelect
That is, through the PXSelect classes, you can execute the BQL command and interact with the cache, as illustrated in the following diagram.

Note: Do not confuse the PXSelect classes with the Select classes. PXSelect is an aggregate of the data view, cache, and graph. You can use PXSelect classes to read, write, update, and delete records in the scope of a graph. Select classes simply represent BQL commands. You cannot read records by using a BQL command without instantiating a data view. For more information on the Select classes, see The Classes That Compose BQL Statements.

Types of PXSelect Classes

The first type parameter of all PXSelect classes is a data access class (DAC) generally bound to a database table. The resulting SQL query selects records from this table. Other type parameters (such as Where, OrderBy, Join, and Aggregate) are optional and represent clauses that can be added to the basic select statement.

Depending on the clauses that will be used in a query, you select the appropriate variant of the PXSelect class.

For example, if you need to use the Where, OrderBy, and Join clauses, you can use the PXSelectJoin<Table, Join, Where, OrderBy> class to create the query, as shown in the following BQL sample code.

PXSelectJoin<Table1,
    LeftJoin<Table2, On<Table2.field2, Equal<Table1.field1>>>,
    Where<Table1.field3, IsNotNull>,
    OrderBy<Asc<Table1.field1>>>
Note: MYOB Acumatica Framework translates this statement to the following SQL query, where [list of columns] is the list of columns of the joined tables. 
SELECT [list of columns] FROM Table1
    LEFT JOIN Table2 ON Table2.Field2 = Table1.Field1
    WHERE Table1.Field3 IS NOT NULL
    ORDER BY Table1.Field1

MYOB Acumatica Framework explicitly enumerates the columns of the database table in the SQL query. For details on which columns are enumerated, see Translation of a BQL Command to SQL.

For more information on how to use the BQL clauses, see To Select Records By Using Traditional BQL.

If you need to retrieve data as it is currently stored in the database, you use one of the PXSelect classes that has Readonly in its name, such as the PXSelectReadonly<Table> class, or any of the PXSelect classes that use aggregation, such as the PXSelectGroupBy<Table, Aggregate> class. Otherwise, the data retrieved from the database can be merged with the data currently stored in the cache. For more information on how the data is merged with the cache, see Merge of the Records with PXCache.

The List of PXSelect Classes

MYOB Acumatica Framework provides the following PXSelect classes: