Db 

\nn\t3::Db()->debug($query = NULL, $return = false); 

Debug of the QueryBuilder statement.

Outputs the complete, compiled query as a readable string, as it is later executed in the database executed later in the database e.g. SELECT FROM fe_users WHERE ...

// Output statement directly in the browser
\nn\t3::Db()->debug( $query );

// Return statement as a string, do not output automatically
echo \nn\t3::Db()->debug( $query, true );

| ➜ Go to source code of Db::debug()

\nn\t3::Db()->delete($table = '', $constraint = [], $reallyDelete = false); 

Delete database entry. Small and fine. Either a table name and the UID can be transferred - or a model.

Delete a data record by table name and uid or any constraint:

// Deletion based on the uid
\nn\t3::Db()->delete('table', $uid);

// Delete based on a custom field
\nn\t3::Db()->delete('table', ['uid_local'=>$uid]);

// Delete entry completely and irrevocably (do not just remove via flag deleted = 1)
\nn\t3::Db()->delete('table', $uid, true);

Delete a data record per model:

\nn\t3::Db()->delete( $model );

| ➜ Go to source code of Db::delete()

\nn\t3::Db()->deleteWithAllFiles($model); 

The GDPR version of deletion.

Radical removal of all traces of a data set including the physical SysFiles, linked to the model. To be used with caution, as no relations are checked for the model to be deleted.

\nn\t3::deleteWithAllFiles( $model );

| ➜ Go to source code of Db::deleteWithAllFiles()

\nn\t3::Db()->filterDataForTable($data = [], $table = ''); 

Only keep elements in key/val array whose keys also exist in TCA for certain table

| ➜ Go to source code of Db::filterDataForTable()

\nn\t3::Db()->findAll($table = '', $ignoreEnableFields = false); 

Retrieves ALL entries from a database table.

The data is returned as an array - this is (unfortunately) still the absolute most performant way to fetch many data records from a table, since no DataMapper has to parse the individual rows.

// Get all data records. "hidden" is taken into account.
\nn\t3::Db()->findAll('fe_users');

// Also fetch data records that are "hidden"
\nn\t3::Db()->findAll('fe_users', true);

| ➜ Go to source code of Db::findAll()

\nn\t3::Db()->findByUid($table = '', $uid = NULL, $ignoreEnableFields = false); 

Finds an entry based on the UID. Also works if the frontend has not yet been initialized, 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);

| ➜ Go to source code of Db::findByUid()

\nn\t3::Db()->findByUids($table = '', $uids = NULL, $ignoreEnableFields = false); 

Finds entries using multiple UIDs.

\nn\t3::Db()->findByUids('fe_user', [12,13]);
\nn\t3::Db()->findByUids('fe_user', [12,13], true);

| ➜ Go to source code of Db::findByUids()

\nn\t3::Db()->findByValues($table = NULL, $where = [], $useLogicalOr = false, $ignoreEnableFields = false, $fieldsToGet = [], $additionalQueryParams = []); 

Finds ALL entries based on a desired field value. Also works if the frontend has not yet been initialized.

// 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]]);

// SELECT uid, username FROM fe_users WHERE name = 'test'
\nn\t3::Db()->findByValues('fe_users', ['name'=>'test'], false, false, ['uid', 'username']);

// SELECT FROM fe_users WHERE name = 'test' LIMIT 1
\nn\t3::Db()->findByValues('fe_users', ['name'=>'test'], false, false, false, ['limit'=>1]);

// SELECT FROM fe_users WHERE name = 'test' LIMIT 2 OFFSET 3
\nn\t3::Db()->findByValues('fe_users', ['name'=>'test'], false, false, false, ['limit'=>2, 'offset'=>3]);

| ➜ Go to source code of Db::findByValues()

\nn\t3::Db()->findIn($table = '', $column = '', $values = [], $ignoreEnableFields = false); 

Finds ALL entries that contain a value from the $values array in the $column column. Also works if the frontend has not yet been initialized. 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']);

| ➜ Go to source code of Db::findIn()

\nn\t3::Db()->findNotIn($table = '', $colName = '', $values = [], $ignoreEnableFields = false); 

Reverse of \nn\t3::Db()->findIn():

Finds ALL entries that do NOT contain a value from the $values array in the $column column. Also works if the frontend has not yet been initialized.

// 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']);

| ➜ Go to source code of Db::findNotIn()

\nn\t3::Db()->findOneByValues($table = NULL, $whereArr = [], $useLogicalOr = false, $ignoreEnableFields = false, $fieldsToGet = []); 

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);

// SELECT uid, name FROM fe_users WHERE firstname = 'david' OR username = 'john'
\nn\t3::Db()->findOneByValues('fe_users', ['firstname'=>'david', 'username'=>'john'], true, false, ['uid', 'name']);

| ➜ Go to source code of Db::findOneByValues()

\nn\t3::Db()->fixFileReferencesForModel($model); 

"Repairs" the SysFileReferences for models that have a property that only referencea FileReferenceinstead of an ObjectStorage instead of a FileReference. At the moment, it is unclear why TYPO3 has included these persists them in the table sys_file_reference, but empties the field tablenames field â or does not set uid_foreign. With an `ObjectStorage the problem does not occur.`

// must happen directly after persisting the model
\nn\t3::Db()->fixFileReferencesForModel( $model );

| ➜ Go to source code of Db::fixFileReferencesForModel()

\nn\t3::Db()->get($uid, $modelType = '', $ignoreEnableFields = false); 

Get one or more domain models/entities using a uid A single $uid or a list of $uids can be passed.

Returns the "real" model/object including all relations, analogous to a query via 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 based on their uids
$modelArray = \nn\t3::Db()->get( [1,2,3], \Nng\MyExt\Domain\Model\Model\Name::class );

// Also returns hidden models
$modelArrayWithHidden = \nn\t3::Db()->get( [1,2,3], \Nng\MyExt\Domain\Model\Name::class, true );

| ➜ Go to source code of Db::get()

\nn\t3::Db()->getColumn($table = '', $colName = '', $useSchemaManager = false); 

Get a table column (TCA) for a specific table

\nn\t3::Db()->getColumn( 'tablename', 'fieldname' );

| ➜ Go to source code of Db::getColumn()

\nn\t3::Db()->getColumnLabel($column = '', $table = ''); 

Get localized label of a specific TCA field

| ➜ Go to source code of Db::getColumnLabel()

\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' );

// Determine fields via the SchemaManager
\nn\t3::Db()->getColumns( 'tablename', true );

| ➜ Go to source code of Db::getColumns()

\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' );

| ➜ Go to source code of Db::getColumnsByType()

\nn\t3::Db()->getConnection(); 

Get a "raw" connection to the database. Only useful in really exceptional cases.

$connection = \nn\t3::Db()->getConnection();
$connection->fetchAll( 'SELECT FROM tt_news WHERE 1;' );

| @return \TYPO3\CMS\Core\Database\Connection

| ➜ Go to source code of Db::getConnection()

\nn\t3::Db()->getDeleteColumn($table = ''); 

Get delete column for specific table.

This column is used as a flag for deleted data records. Normally: deleted = 1

| ➜ Go to source code of Db::getDeleteColumn()

\nn\t3::Db()->getQueryBuilder($table = ''); 

Get QueryBuilder 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->executeStatement()->fetchAllAssociative();

| ➜ Go to source code of Db::getQueryBuilder()

\nn\t3::Db()->getRepositoryForModel($className = NULL); 

Get an instance of the repository for a model (or a model class name).

\nn\t3::Db()->getRepositoryForModel( \My\Domain\Model\Name::class );
\nn\t3::Db()->getRepositoryForModel( $myModel );

| ➜ Go to source code of Db::getRepositoryForModel()

\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( \My\Domain\Model\Name::class );

| ➜ Go to source code of Db::getTableNameForModel()

\nn\t3::Db()->ignoreEnableFields($queryOrRepository, $ignoreStoragePid = true, $ignoreHidden = false, $ignoreDeleted = false, $ignoreStartEnd = false); 

Removes default constraints for StoragePID, hidden and/or deleted for a query or repository.

\nn\t3::Db()->ignoreEnableFields( $entryRepository );
\nn\t3::Db()->ignoreEnableFields( $query );

Example for a custom query:

$table = 'tx_myext_domain_model_entry';
$queryBuilder = \nn\t3::Db()->getQueryBuilder( $table );
$queryBuilder->select('uid','title','hidden')->from( $table );
\nn\t3::Db()->ignoreEnableFields( $queryBuilder, true, true );
$rows = $queryBuilder->executeQuery()->fetchAllAssociative();

If this is not enough or too complicated, see:

\nn\t3::Db()->statement();

| ➜ Go to source code of Db::ignoreEnableFields()

\nn\t3::Db()->insert($tableNameOrModel = '', $data = []); 

Insert database entry. Simple and foolproof. Either the table name and an array can be transferred - or a domain model.

Inserting a new data set via table name and data array:

$insertArr = \nn\t3::Db()->insert('table', ['bodytext'=>'...']);

Insert a new model. The repository is determined automatically. The model is persisted directly.

$model = new \My\Nice\Model();
$persistedModel = \nn\t3::Db()->insert( $model );

| ➜ Go to source code of Db::insert()

\nn\t3::Db()->insertMultiple($tableName = '', $rows = [], $colOrder = []); 

Insert several lines into database.

use TYPO3\CMS\Core\Database\Connection;

$data = [
    ['title' => 'One', 'tstamp'=>123],
    ['title' => 'Two', 'tstamp'=>123],
];
$colOrder = [
    'tstamp' => Connection::PARAM_INT,
    'title' => Connection::PARAM_STR,
];

\nn\t3::Db()->insertMultiple('table', $data, $colOrder);

| ➜ Go to source code of Db::insertMultiple()

\nn\t3::Db()->insertOrUpdate($tableName, $whereArr = [], $model = []); 

Store an item in the database, but keep it unique by $whereArr = []

$data = [ profileUid: "", entityType: "", entityUid: "", ... ];
\nn\un::Interaction()->insertOrUpdate( $data );

| ➜ Go to source code of Db::insertOrUpdate()

\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). An array is passed for the value of the individual orderings:

$ordering = ['uid' => [3,7,2,1]];
\nn\t3::Db()->orderBy( $queryOrRepository, $ordering );

| ➜ Go to source code of Db::orderBy()

\nn\t3::Db()->persistAll(); 

Persist all.

\nn\t3::Db()->persistAll();

| @return void

| ➜ Go to source code of Db::persistAll()

\nn\t3::Db()->quote($value = ''); 

A replacement for the mysqli_real_escape_string() method.

Should only be used in an emergency for low-level queries. It is better to use preparedStatements.

Only works with SQL, not with 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;#');

| ➜ Go to source code of Db::quote()

\nn\t3::Db()->save($tableNameOrModel = '', $data = []); 

Create database entry OR update an existing data record.

Decides independently whether the entry should be added to the database via UPDATE or INSERT or whether an existing data record needs to be updated. The data is persisted directly!

Example for transferring a table name and an array:

// no uid transferred? Then INSERT a new data set
\nn\t3::Db()->save('table', ['bodytext'=>'...']);

// pass uid? Then UPDATE existing data
\nn\t3::Db()->save('table', ['uid'=>123, 'bodytext'=>'...']);

Example for transferring a domain model:

// new model? Is inserted via $repo->add()
$model = new \My\Nice\Model();
$model->setBodytext('...');
$persistedModel = \nn\t3::Db()->save( $model );

// existing model? Is updated via $repo->update()
$model = $myRepo->findByUid(123);
$model->setBodytext('...');
$persistedModel = \nn\t3::Db()->save( $model );

| ➜ Go to source code of Db::save()

\nn\t3::Db()->setFalConstraint($queryBuilder = NULL, $tableName = '', $falFieldName = '', $numFal = true, $operator = false); 

Add constraint for sys_file_reference to a QueryBuilder. Restricts the results to whether there is a FAL relation.

$queryBuilder = \nn\t3::Db()->getQueryBuilder( $table );

// Only fetch datasets that have at least one SysFileReference for falfield
\nn\t3::Db()->setFalConstraint( $queryBuilder, 'tx_myext_tablename', 'falfield' );

// ... that 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 );

// ... that have 2 or less (less than or equal) SysFileReferences
\nn\t3::Db()->setFalConstraint( $queryBuilder, 'tx_myext_tablename', 'falfield', 2, 'lte' );

| ➜ Go to source code of Db::setFalConstraint()

\nn\t3::Db()->setNotInSysCategoryConstraint($queryBuilder = NULL, $sysCategoryUids = [], $tableName = '', $categoryFieldName = 'categories'); 

Restrict 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' );

| ➜ Go to source code of Db::setNotInSysCategoryConstraint()

\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' );

| ➜ Go to source code of Db::setSysCategoryConstraint()

\nn\t3::Db()->sortBy($objectArray, $fieldName = 'uid', $uidList = []); 

Sorts the results of a query according to an array and a specific field. Solves the problem that an ->in() query does not return the results in the order of the passed IDs. Example: | $query->matching($query->in('uid', [3,1,2])); does not necessarily come return in the order [3,1,2].

$insertArr = \nn\t3::Db()->sortBy( $storageOrArray, 'uid', [2,1,5]);

| ➜ Go to source code of Db::sortBy()

\nn\t3::Db()->statement($statement = '', $params = [], $types = []); 

Send 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, for example, with queries of tables that are not part of the Typo3 installation and therefore could not be reached via the normal QueryBuilder.

// ALWAYS escape variables via!
$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}%"] );

// Types can be passed (this is determined automatically for arrays)
$rows = \nn\t3::Db()->statement( 'SELECT FROM tt_news WHERE uid IN (:uids)', ['uids'=>[1,2,3]], ['uids'=>Connection::PARAM_INT_ARRAY] );

With 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 affected rows is returned.

| ➜ Go to source code of Db::statement()

\nn\t3::Db()->tableExists($table = ''); 

Does a specific DB table exist?

$exists = \nn\t3::Db()->tableExists('table');

| @return boolean

| ➜ Go to source code of Db::tableExists()

\nn\t3::Db()->truncate($table = ''); 

Empty database table. Deletes all entries in the specified table and resets the auto-increment value to 0.

\nn\t3::Db()->truncate('table');

| ➜ Go to source code of Db::truncate()

\nn\t3::Db()->undelete($table = '', $constraint = []); 

Restore deleted database entry. To do this, the flag for "deleted" is set to 0 again.

\nn\t3::Db()->undelete('table', $uid);
\nn\t3::Db()->undelete('table', ['uid_local'=>$uid]);

| ➜ Go to source code of Db::undelete()

\nn\t3::Db()->update($tableNameOrModel = '', $data = [], $uid = NULL); 

Update database entry. Quick and easy. The update can be done 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);

// UPDATES table SET title='new' WHERE uid IN (1,2,3)
\nn\t3::Db()->update('table', ['title'=>'new'], ['uid'=>[1,2,3]);

// 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 are 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 is determined automatically and the model is persisted directly.

$model = $myRepo->findByUid(1);
\nn\t3::Db()->update( $model );

| ➜ Go to source code of Db::update()