Db¶
\nn\t3::Db()¶
Simplify access to the most commonly used database operations for write, read, delete .
Overview of Methods¶
\nn\t3::Db()->debug($query = NULL, $return = false);¶
Debug the QueryBuilder statement.
Puts out the complete compiled query as a readable string, as it will be executed later in the database.
e.g. SELECT FROM fe_users WHERE ...
// Output statement directly to the browser.
\nn\t3::Db()->debug( $query );
// Return statement as string, do not output automatically
echo \nn\t3::Db()->debug( $query, true );
@param mixed $query@param boolean $return@return string\nn\t3::Db()->delete($table = '', $constraint = [], $reallyDelete = false);¶
Delete database entry. Small and fine. Either a table name and the UID can be passed - or a model.
Delete a record by table name and uid or any constraint:
// Delete by uid.
\nn\t3::Db()->delete('table', $uid);
// Delete using a custom field
\nn\t3::Db()->delete('table', ['uid_local'=>$uid]);
// delete the entry completely and irrevocably (not only by flag deleted = 1)
\nn\t3::Db()->delete('table', $uid, true);
Deleting a record by model:
\nn\t3::Db()->delete( $model );
@param mixed $table@param array $constraint@param boolean $reallyDelete@return mixed\nn\t3::Db()->filterDataForTable($data = [], $table = '');¶
Keep in key/val array only elements whose keys also exist in exist in TCA for specific table
@param array $data@param string $table@return array\nn\t3::Db()->findAll($table = '', $ignoreEnableFields = false);¶
Get ALL entry from a database table.
The data is returned as an array â this is (unfortunately) still the absolute most
most performant way to fetch many records from a table, since no DataMapper
has to parse the individual rows.
// Get all records. "hidden" will be taken into account.
\nn\t3::Db()->findAll('fe_users');
// Also fetch records which are "hidden".
\nn\t3::Db()->findAll('fe_users', true);
@param string $table@param boolean $ignoreEnableFields@return array\nn\t3::Db()->findByUid($table = '', $uid = NULL, $ignoreEnableFields = false);¶
Finds an entry based on the UID. Works even if frontend has not been initialized yet, e.g. while AuthentificationService is running or in the scheduler.
\nn\t3::Db()->findByUid('fe_user', 12);
\nn\t3::Db()->findByUid('fe_user', 12, true);
@param string $table@param int $uid@param boolean $ignoreEnableFields@return array\nn\t3::Db()->findByUids($table = '', $uids = NULL, $ignoreEnableFields = false);¶
Finds records by multiple UIDs
\nn\t3::Db()->findByUids('fe_user', [12,13]);
\nn\t3::Db()->findByUids('fe_user', [12,13], true);
@param string $table@param int|array $uids@param boolean $ignoreEnableFields@return array\nn\t3::Db()->findByValues($table = NULL, $whereArr = [], $useLogicalOr = false, $ignoreEnableFields = false);¶
Finds ALL entries based on a desired field value. Works even if frontend has not been initialized yet
// SELECT FROM fe_users WHERE email = 'david@99grad.de'
\nn\t3::Db()->findByValues('fe_users', ['email'=>'david@99grad.de']);
// SELECT FROM fe_users WHERE uid IN (1,2,3)
\nn\t3::Db()->findByValues('fe_users', ['uid'=>[1,2,3]]);
@param string $table@param array $whereArr@param boolean $useLogicalOr@param boolean $ignoreEnableFields@return array\nn\t3::Db()->findIn($table = '', $column = '', $values = [], $ignoreEnableFields = false);¶
Finds ALL entries that contain a value from the $values array in the $column column.
Works even if the frontend has not been initialized yet.
Alias to \nn\t3::Db()->findByValues()
// SELECT FROM fe_users WHERE uid IN (1,2,3)
\nn\t3::Db()->findIn('fe_users', 'uid', [1,2,3]);
// SELECT FROM fe_users WHERE username IN ('david', 'martin')
\nn\t3::Db()->findIn('fe_users', 'username', ['david', 'martin']);
@param string $table@param string $column@param array $values@param boolean $ignoreEnableFields@return array\nn\t3::Db()->findNotIn($table = '', $colName = '', $values = [], $ignoreEnableFields = false);¶
Inversion to \nn\t3::Db()->findIn():
Finds ALL entries that do NOT contain a value from the $values array in the $column column.
Works even if the frontend has not been initialized yet.
// SELECT FROM fe_users WHERE uid NOT IN (1,2,3)
\nn\t3::Db()->findNotIn('fe_users', 'uid', [1,2,3]);
// SELECT FROM fe_users WHERE username NOT IN ('david', 'martin')
\nn\t3::Db()->findNotIn('fe_users', 'username', ['david', 'martin']);
@param string $table@param string $colName@param array $values@param boolean $ignoreEnableFields@return array\nn\t3::Db()->findOneByValues($table = NULL, $whereArr = [], $useLogicalOr = false, $ignoreEnableFields = false);¶
Finds ONE entry based on desired field values.
// SELECT FROM fe_users WHERE email = 'david@99grad.de'
\nn\t3::Db()->findOneByValues('fe_users', ['email'=>'david@99grad.de']);
// SELECT FROM fe_users WHERE firstname = 'david' AND username = 'john'.
\nn\t3::Db()->findOneByValues('fe_users', ['firstname'=>'david', 'username'=>'john']);
// SELECT FROM fe_users WHERE firstname = 'david' OR username = 'john'.
\nn\t3::Db()->findOneByValues('fe_users', ['firstname'=>'david', 'username'=>'john'], true);
@param string $table@param array $whereArr@param boolean $useLogicalOr@param boolean $ignoreEnableFields@return array\nn\t3::Db()->get($uid, $modelType = '', $ignoreEnableFields = false);¶
Get one or more domain models/entities by uid
A single $uid or a list of $uids can be passed.
Returns the “real” model/object including all relations, analogous to a query about the repository.
// Get a single model by its uid.
$model = \nn\t3::Db()->get( 1, \Nng\MyExt\Domain\Model\Name::class );
// Get an array of models by their uids
$modelArray = \nn\t3::Db()->get( [1,2,3], \Nng\MyExt\Domain\Model\Name::class );
// Returns also hidden models
$modelArrayWithHidden = \nn\t3::Db()->get( [1,2,3], \Nng\MyExt\Domain\Model\Name::class, true );
@param int $uid@param string $modelType@param boolean $ignoreEnableFields@return object\nn\t3::Db()->getColumn($table = '', $colName = '', $useSchemaManager = false);¶
Get a table column (TCA) for specific table
\nn\t3::Db()->getColumn( 'tablename', 'fieldname' );
@param string $table@param string $colName@param boolean $useSchemaManager@return array\nn\t3::Db()->getColumnLabel($column = '', $table = '');¶
Get localized label of a specific TCA field
@param string $column@param string $table@return string\nn\t3::Db()->getColumns($table = '', $useSchemaManager = false);¶
Get all table columns (TCA) for specific table
// Get fields based on the TCA array.
\nn\t3::Db()->getColumns( 'tablename' );
// get fields from the SchemaManager
\nn\t3::Db()->getColumns( 'tablename', true );
@param string $table@param boolean $useSchemaManager@return array\nn\t3::Db()->getColumnsByType($table = '', $colType = '', $useSchemaManager = false);¶
Get fields of a table by a specific type
\nn\t3::Db()->getColumnsByType( 'tx_news_domain_model_news', 'slug' );
@param string $table@param string $colType@param boolean $useSchemaManager@return array\nn\t3::Db()->getConnection();¶
Get a “raw” connection to the database. Only useful in real exceptional cases.
$connection = \nn\t3::Db()->getConnection();
$connection->fetchAll( 'SELECT FROM tt_news WHERE 1;' );
@return \TYPO3\CMS\Core\Database\Connection.
\nn\t3::Db()->getDeleteColumn($table = '');¶
Get delete column for specific table
This column is used as a flag for deleted records.
Normally: deleted = 1
@param string $table@return string\nn\t3::Db()->getQueryBuilder($table = '');¶
GetQueryBuilder for a table
$queryBuilder = \nn\t3::Db()->getQueryBuilder( 'fe_users' );
Example:
$queryBuilder = \nn\t3::Db()->getQueryBuilder( 'fe_users' );
$queryBuilder->select('name')->from( 'fe_users' );
$queryBuilder->andWhere( $queryBuilder->expr()->eq( 'uid', $queryBuilder->createNamedParameter(12) ));
$rows = $queryBuilder->execute()->fetchAll();
@param string $table@return QueryBuilder\nn\t3::Db()->getRepositoryForModel($className = NULL);¶
Get the repository instance for a model (or model class name)
\nn\t3::Db()->getRepositoryForModel( \My\Domain\Model\Name::class );
\nn\t3::Db()->getRepositoryForModel( $myModel );
@param mixed $className@return \TYPO3\CMS\Extbase\Persistence\Repository.
\nn\t3::Db()->getTableNameForModel($className = NULL);¶
Get table name for a model (or a model class name).
Alias to \nn\t3::Obj()->getTableName()
// tx_myext_domain_model_entry
\nn\t3::Db()->getTableNameForModel( $myModel );
// tx_myext_domain_model_entry
\nn\t3::Db()->getTableNameForModel( $myModel );
@param mixed $className@return string\nn\t3::Db()->insert($tableNameOrModel = '', $data = []);¶
Insert database entry. Simple and foolproof. Either the table name and an array can be passed - or a domain model.
Inserting a new record by table name and data array:
$insertArr = \nn\t3::Db()->insert('table', ['bodytext'=>'...']);
Insert a new model. The repository is determined automatically. The model will be persisted directly.
$model = new \My\Nice\Model();
$persistedModel = \nn\t3::Db()->insert( $model );
@param mixed $tableNameOrModel@param array $data@return mixed\nn\t3::Db()->orderBy($queryOrRepository, $ordering = []);¶
Set sorting for a repository or a query.
$ordering = ['title' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING];
\nn\t3::Db()->orderBy( $queryOrRepository, $ordering );
// asc and desc can be used as synonyms
$ordering = ['title' => 'asc'];
$ordering = ['title' => 'desc'];
\nn\t3::Db()->orderBy( $queryOrRepository, $ordering );
Can also be used to sort by a list of values (e.g. uids).
This is done by passing an array for the value of the single ordering:
$ordering = ['uid' => [3,7,2,1]];
\nn\t3::Db()->orderBy( $queryOrRepository, $ordering );
@param mixed $queryOrRepository@param array $ordering@return mixed\nn\t3::Db()->quote($value = '');¶
A replacement for the mysqli_real_escape_string() method.
Should be used only in case of emergency for low-level queries.
It is better to use preparedStatements.
Works only for SQL, not for DQL.
$sword = \nn\t3::Db()->quote('test'); // => 'test'
$sword = \nn\t3::Db()->quote('test';SET"); // => 'test\';SET'
$sword = \nn\t3::Db()->quote([1, 'test', '2']); // => [1, "'test'", '2']
$sword = \nn\t3::Db()->quote('"; DROP TABLE fe_user;#');
@param string|array $value@return string|array\nn\t3::Db()->save($tableNameOrModel = '', $data = []);¶
Create database record OR update an existing record.
Decides independently whether the entry must be inserted into the database via UPDATE or INSERT
or an existing record must be updated. The data will be
persisted directly!
Example for passing a table name and an array:
// no uid passed? Then INSERT a new record
\nn\t3::Db()->save('table', ['bodytext'=>'...']);
// pass uid? Then UPDATE existing data
\nn\t3::Db()->save('table', ['uid'=>123, 'bodytext'=>'...']);
Example for passing a domain model:
// new model? Will be added by $repo->add()
$model = new \My\Nice\Model();
$model->setBodytext('...');
$persistedModel = \nn\t3::Db()->save( $model );
// existing model? Will be updated by $repo->update()
$model = $myRepo->findByUid(123);
$model->setBodytext('...');
$persistedModel = \nn\t3::Db()->save( $model );
@param mixed $tableNameOrModel@param array $data@return mixed\nn\t3::Db()->setFalConstraint($queryBuilder = NULL, $tableName = '', $falFieldName = '', $numFal = true, $operator = false);¶
Add a constraint for sys_file_reference to a QueryBuilder. Restricts the results to whether there is a FAL relation.
$queryBuilder = \nn\t3::Db()->getQueryBuilder( $table );
// Get only records that have at least one SysFileReference for falfield.
\nn\t3::Db()->setFalConstraint( $queryBuilder, 'tx_myext_tablename', 'falfield' );
// ... which do NOT have a SysFileReference for falfield
\nn\t3::Db()->setFalConstraint( $queryBuilder, 'tx_myext_tablename', 'falfield', false );
// ... which have EXACTLY 2 SysFileReferences
\nn\t3::Db()->setFalConstraint( $queryBuilder, 'tx_myext_tablename', 'falfield', 2 );
// ... which have 2 or less (less than or equal) SysFileReferences
\nn\t3::Db()->setFalConstraint( $queryBuilder, 'tx_myext_tablename', 'falfield', 2, 'lte' );
@param \TYPO3\CMS\Core\Database\Query\QueryBuilder $queryBuilder@param string $tableName@param string $falFieldName@param boolean $numFal@param boolean $operator@return \TYPO3\CMS\Core\Database\Query\QueryBuilder\nn\t3::Db()->setNotInSysCategoryConstraint($queryBuilder = NULL, $sysCategoryUids = [], $tableName = '', $categoryFieldName = 'categories');¶
Constrain the constraint to records that are NOT in one of the specified categories.
Opposite and alias to \nn\t3::Db()->setSysCategoryConstraint()
$queryBuilder = \nn\t3::Db()->getQueryBuilder( $table );
\nn\t3::Db()->setNotInSysCategoryConstraint( $queryBuilder, [1,3,4], 'tx_myext_tablename', 'categories' );
@param \TYPO3\CMS\Core\Database\Query\QueryBuilder $queryBuilder@param array $sysCategoryUids@param string $tableName@param string $categoryFieldName@return \TYPO3\CMS\Core\Database\Query\QueryBuilder\nn\t3::Db()->setSysCategoryConstraint($queryBuilder = NULL, $sysCategoryUids = [], $tableName = '', $categoryFieldName = 'categories', $useNotIn = false);¶
Add constraint for sys_category / sys_category_record_mm to a QueryBuilder. Restricts the results to the specified sys-categories UIDs.
$queryBuilder = \nn\t3::Db()->getQueryBuilder( $table );
\nn\t3::Db()->setSysCategoryConstraint( $queryBuilder, [1,3,4], 'tx_myext_tablename', 'categories' );
@param \TYPO3\CMS\Core\Database\Query\QueryBuilder $querybuilder@param array $sysCategoryUids@param string $tableName@param string $categoryFieldName@param boolean $useNotIn@return \TYPO3\CMS\Core\Database\Query\QueryBuilder\nn\t3::Db()->sortBy($objectArray, $fieldName = 'uid', $uidList = []);¶
Sorts results of a query by an array and specific field.
Resolves the problem that a ->in() query does not return results
in the order of the given IDs. Example:
| $query->matching($query->in('uid', [3,1,2])); does not necessarily return
returned in the order [3,1,2].
$insertArr = \nn\t3::Db()->sortBy( $storageOrArray, 'uid', [2,1,5]);
@param mixed $objectArray@param string $fieldName@param array $uidList@return array\nn\t3::Db()->statement($statement = '', $params = []);¶
Submit a “raw” query to the database. Closer to the database is not possible. You are responsible for everything yourself. Injections are only opposed by your (hopefully sufficient :) intelligence.
Helps e.g. with queries of tables, which are not part of the Typo3 installation and therefore therefore could not be reached via the normal QueryBuilder.
// ALWAYS escape variables!
$keyword = \nn\t3::Db()->quote('search term');
$rows = \nn\t3::Db()->statement( "SELECT FROM tt_news WHERE bodytext LIKE '%${keyword}%'");
// or better use prepared statements:
$rows = \nn\t3::Db()->statement( 'SELECT FROM tt_news WHERE bodytext LIKE :str', ['str'=>"%${keyword}%"] );
For a SELECT statement, the rows from the database are returned as an array.
For all other statements (e.g. UPDATE or DELETE) the number of rows involved is returned.
@param string $statement@param array $params@return mixed\nn\t3::Db()->tableExists($table = '');¶
Exists a specific DB table?
$exists = \nn\t3::Db()->tableExists('table');
@return boolean\nn\t3::Db()->truncate($table = '');¶
Clear database table.
Deletes all entries in the specified table and resets the auto-increment value to 0
\nn\t3::Db()->truncate('table');
@param string $table@return boolean\nn\t3::Db()->undelete($table = '', $constraint = []);¶
Restore deleted database entry.
This is done by setting the flag for “deleted” (deleted) back to 0.
\nn\t3::Db()->undelete('table', $uid);
\nn\t3::Db()->undelete('table', ['uid_local'=>$uid]);
@param string $table@param array $constraint@return boolean\nn\t3::Db()->update($tableNameOrModel = '', $data = [], $uid = NULL);¶
Update database entry. Quick and easy. The update can happen either by table name and data array. Or you can pass a model.
Examples:
// UPDATES table SET title='new' WHERE uid=1
\nn\t3::Db()->update('table', ['title'=>'new'], 1);
// UPDATE table SET title='new' WHERE email='david@99grad.de' AND pid=12
\nn\t3::Db()->update('table', ['title'=>'new'], ['email'=>'david@99grad.de', 'pid'=>12, ...]);
With true instead of a $uid ALL records of the table will be updated.
// UPDATE table SET test='1' WHERE 1
\nn\t3::Db()->update('table', ['test'=>1], true);
Instead of a table name, a simple model can also be passed. The repository will be determined automatically and the model will be persisted directly.
$model = $myRepo->findByUid(1);
\nn\t3::Db()->update( $model );
@param mixed $tableNameOrModel@param array $data@param int $uid@return mixed