Queries: content fetches, eZDB and SQL #

This page is for developers who reach for a query builder ($qb->where(), Model::where(), querysets, Arel). There is no general query builder in the kernel. You query in one of three ways, depending on the data.

Contents #

1. Which way for which data
2. Content: fetch parameters
3. Your tables: condition arrays
4. Your tables: SQL through eZDB
5. Portability
6. Performance

Which way for which data #

Content: fetch parameters #

$nodes = eZContentObjectTreeNode::subTreeByNodeID( array(
    'ClassFilterType'  => 'include',
    'ClassFilterArray' => array( 'event' ),
    'AttributeFilter'  => array( array( 'event/start_date', '>=', time() ) ),
    'SortBy'           => array( array( 'attribute', true, 'event/start_date' ) ),
    'Depth'            => 3,
    'Limit'            => 20,
    'Offset'           => 0 ), $parentNodeID );

The parameter array is the query builder for content: class filters, attribute filters, depth, sort, limit and offset. The current user's content/read policy is applied unless you pass 'Limitation' => array(), which you must never do in code that shows results to users (The content API). Templates use the same parameters in lower case through fetch( 'content', 'list', hash( ... ) ) (Fetch functions).

Your tables: condition arrays #

$items = eZPersistentObject::fetchObjectList( expDemoItem::definition(), null,
                                              array( 'user_id' => (int)$userID ),
                                              array( 'created' => 'desc' ),
                                              array( 'offset' => 0, 'length' => 20 ) );

Conditions are arrays; values are escaped for you (ORM: eZPersistentObject).

Your tables: SQL through eZDB #

From Database access and SQL:

$db = eZDB::instance();
$rows = $db->arrayQuery( 'SELECT id, title FROM expdemo_item WHERE user_id = ' . (int)$userID . ' ORDER BY created DESC',
                         array( 'offset' => 0, 'limit' => 20 ) );
$db->query( "UPDATE expdemo_item SET title = '" . $db->escapeString( $title ) . "' WHERE id = " . (int)$id );

arrayQuery() and query() take a finished SQL string, not a statement with placeholders as PDO does: cast integers, wrap strings in escapeString(), and pick identifiers from an allow list. query() returns true or false for statements that change data.

Portability #

The driver is chosen by site.ini [DatabaseSettings] DatabaseImplementation; the kernel ships drivers for MySQL/MariaDB (eZMySQLiDB), PostgreSQL, SQLite (eZSQLite3DB) and MongoDB (expMongoDB), and Oracle comes from the ezoracle extension (Database support). To keep your SQL working on all of them:

• Use arrayQuery() offsets and limits, not LIMIT in the string.
• Avoid engine-specific functions such as IFNULL, GROUP_CONCAT or SET @var := ....
• MongoDB has no joins: the driver translates common statements; for your own tables prefer eZPersistentObject.
• Test on SQLite (the default for new installations) and on the production engine.

Performance #

• Always limit lists; count with subTreeCountByNodeID() or eZPersistentObject::count().
• In long scripts, fetch in pages and clear in-memory caches between them (eZContentObject::clearCache()).
• The SQL query cache can keep read results (querycache.ini) (SQL query cache).
• The debug bar shows the queries of a page and their time (Debugging).

Related pages #

• Database access and SQL
• Databases
• Profiling and performance
• Free documentation: this page is free to copy, translate and improve under the GNU FDL 1.3.