Skip to content

ORM

EspoCRM utilizes own built-in ORM (object-relational mapping). Create, update, read, delete and search operations are performed via the Entity Manager instance.

The EntityManager is available as a container service. It's a central access point for ORM functionalities.

A Repository class serves for fetching and storing records. Base classes: Espo\ORM\Repositories\RDBRepository, Espo\Core\Repositories\Database. RDB stands for a relational database.

An Entity class represents a single record. Each entity type has it's own entity class. Base class: Espo\Core\ORM\Entity, interface: Espo\ORM\Entity.

An EntityCollection is a collection of entities. It's returned by find operations. An SthCollection is a collection of entities, consuming much less memory than EntityCollection. Collections are iterable.

See also

Injecting Entity Manager

The Entity Manager is available as a Container service.

A class with the entityManager dependency:

<?php
namespace Espo\SomeNamespace;

use Espo\ORM\EntityManager;

class SomeClass
{    
    public function __construct(private EntityManager $entityManager)
    {}
}

Working with entities

Create new entity

<?php
$entity = $entityManager->getNewEntity($entityType)

Or type hinted:

<?php
use Espo\Modules\MyModule\Entities\MyEntity;

// Will infer a proper MyEntity type (for static analysis and IDEs supporting generic types).
$entity = $entityManager->getRDBRepositoryByClass(MyEntity::class)->getNew();

Note

It creates a new instance but doesn't store it in DB. The entity doesn't have ID yet.

Fetch existing entity

<?php
$entity = $entityManager->getEntityById($entityType, $id);

Or type hinted:

<?php
use Espo\Modules\MyModule\Entities\MyEntity;

$entity = $entityManager->getRDBRepositoryByClass(MyEntity::class)->getById($id);

Store entity

<?php
$entityManager->saveEntity($entity);

With options:

<?php
use Espo\Core\ORM\Repository\Option\SaveOption;

$entityManager->saveEntity($entity, [SaveOption::SILENT => true]);

Available options:

  • skipHooks – skip all hooks; workflows, formula will be ignored;
  • silent – workflows will be ignored, modified fields won't be change;
  • skipAll – skip all additional processing;
  • skipCreatedBy – createdBy won't be set with current user;
  • skipModifiedBy – modifiedBy won't be set with current user;
  • createdById – override createdBy;
  • modifiedById – override modifiedBy.

Options in constants available here: Espo\Core\ORM\Repository\Option\SaveOption.

Create and store entity

<?php
$entity = $entityManager->createEntity($entityType, [
    'name' => 'Test',
    'status' => 'Hello',
]);

Remove entity

<?php
$entityManager->removeEntity($entity);

Get attribute value

<?php
$attributeValue = $entity->get('attributeName');

Note

As EspoCRM supports custom fields and relationships which are added dynamically without the need to compile, attribute accessor methods get, set and has were introduced. For type safety, consider creating getters and setters for needed attributes in your custom Entity class. Use these methods in your business logic code.

Has attribute value

Checks whether an attribute is set. Note: If it's set to NULL it will return true.

<?php
$attributeIsSet = $entity->has('attributeName'); // true or false

Set attribute value

One:

<?php
$entity->set('attributeName', 'Test Value');

Multiple:

<?php
$entity->setMultiple([
    'name' => 'Test Name',
    'assignedUserId' => '1',
]);

Clear attribute value

<?php
$entity->clear('attributeName');

This will unset the attribute. If you save the Entity after that, it will not change the value to NULL in database.

Fetched attributes

Check whether an attribute was changed.

<?php
// a value that was set once the record was fetched from DB
$value = $entity->getFetched('attributeName');

// check whether an attribute was changed since the last syncing with DB
$attributeChanged = $entity->isAttributeChanged('attributeName');

Get all attribute values

<?php
$valueMap = $entity->getValueMap(); // stdClass

Delete from DB

<?php
$entityManager->getRDBRepository($entityType)->deleteFromDb($id);

This will delete a record permanently.

Repository

Get a repository by a class:

<?php
use Espo\Modules\Crm\Entities\Account;
// returns Collection<Account>
$accountRepository = $entityManager->getRDBRepositoryByClass(Account::class);
<?php
// The proper type of a returned Entity is inferred (in static analysis and IDE that supports generic types).
$account = $entityManager->getRDBRepositoryByClass(Account::class)
    ->getById($id);

Get a repository by an entity type:

<?php
$repository = $entityManager->getRDBRepository($entityType);

Find

<?php
$collection = $entityManager
    ->getRDBRepository($entityType)
    ->where([
        'type' => 'Customer',
    ])
    ->find();

Descending order:

<?php
$collection = $entityManager
    ->getRDBRepository($entityType)
    ->limit(0, 10)
    ->order('createdAt', 'DESC')
    ->find();

Complex order:

<?php
$collection = $entityManager
    ->getRDBRepository($entityType)
    ->order([
        ['createdAt', 'ASC'],
        ['name', 'DESC'],
    ])
    ->find();

Or:

<?php
$collection = $entityManager
    ->getRDBRepository($entityType)
    ->order('createdAt', 'ASC')
    ->order('name', 'DESC')
    ->find();

Or:

<?php
<?php
use Espo\ORM\Query\Part\Expression as Expr;

$collection = $entityManager
    ->getRDBRepository($entityType)
    ->order(
        Expr::concat(
            Expr::column('firstName'),
            Expr::column('lastName')
        ),
        'DESC',            
    )
    ->find();

Ordering by a value list:

<?php
$collection = $entityManager
    ->getRDBRepository('Opportunity')
    ->order('LIST:stage:Prospectring,Qualification,Proposal')
    ->find();

Feeding a query to a repository:

<?php
$collection = $entityManager
    ->getRDBRepository($entityType)
    ->clone($query)
    ->limit(0, 10)
    ->find();

Finding the first one:

<?php
$entity = $entityManager
    ->getRDBRepository($entityType)
    ->where([
        'type' => 'Customer',
    ])
    ->findOne();

You can use getRDBRepositoryByClass for type safety.

<?php
use Espo\Modules\Crm\Entities\Account;

$entity = $entityManager
    ->getRDBRepositoryByClass(Account::class)
    ->findOne();

Relations

Has-Many:

<?php
$opportunityCollection = $entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'opportunities')
    ->limit(0, 10)
    ->where($whereClause)
    ->find();

$opportunity = $entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'opportunities')
    ->order('createdAt', 'DESC')
    ->findOne();

Belongs-To or Has-One:

<?php
$account = $entityManager
    ->getRDBRepository('Task')
    ->getRelation($task, 'account')
    ->findOne();

Filtering by a relation column:

<?php
 $leads = $entityManager
    ->getRDBRepository('TargetList')
    ->getRelation($targetList, 'leads')
    ->where([
        '@relation.optedOut' => false,
    ])
    ->find();

optedOut is a column in the middle table.

Relate entities

<?php
$entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'opportunities')
    ->relate($opportunity);

$entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'opportunities')
    ->relateById($opportunityId);

$entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'contacts')
    ->relate($contact, [
        'role' => 'CEO', // relationship column
    ]);

Unrelate entities

<?php
$entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'opportunities')
    ->unrelate($opportunity);

$entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'opportunities')
    ->unrelateById($opportunityId);

Update columns

<?php
$entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'contacts')
    ->updateColumns($contact, [
        'role' => 'CEO', // relationship column
    ]);

$entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'contacts')
    ->updateColumnsById($contactId, [
        'role' => 'CEO', // relationship column
    ]);
<?php
$isRelated = $entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'opportunities')
    ->isRelated($opportunity);

Collections

Collections contains Entities. An EntityCollection is a regular collection of entities. An SthCollection is a collection of entities, consuming much less memory. It's reasonable when working with large query results. It does not allocate memory for all results but only for a current entity (e.g. when iterating).

Iteration:

<?php
foreach ($collection as $entity) {
    // Do something with entity.
}

Get all values:

<?php
$valueMapList = $collection->getValueMapList(); // stdClass[]

Factory:

<?php
$collection = $entityManager->getCollectionFactory()->create(); // EntityCollection
$collection[] = $entity;

$sthCollection = $entityManager->getCollectionFactory()->createFromQuery($selectQuery); // SthCollection

Select Query Parameters

Where clause

Comparison operators

Supported comparison operators: >, <, >=, <=, =, !=.

<?php
$opportunityList = $entityManager
    ->getRDBRepository('Opportunity')
    ->where([
        'amount>=' => 100
    ])
    ->find();

IN and NOT IN operators

<?php
$opportunityList = $entityManager
    ->getRDBRepository('Opportunity')
    ->where([
        'stage' => ['Closed Lost', 'Closed Won']
    ])
    ->find();
<?php
$opportunityList = $entityManager
    ->getRDBRepository('Opportunity')
    ->where([
        'stage!=' => ['Closed Lost', 'Closed Won']
    ])
    ->find();

LIKE operators

Supported operators:

  • * - LIKE
  • !* - NOT LIKE
<?php
$opportunityList = $entityManager
    ->getRDBRepository('Opportunity')
    ->where([
        'name*' => '%service%',
    ])
    ->find();

OR, AND operators

<?php
$opportunityList = $entityManager
    ->getRDBRepository('Opportunity')
    ->where([
        [
            'OR' => [
                ['stage' => 'Closed Won'],
                ['stage' => 'Closed Lost'],
            ],
            'AND' => [
                'amountConverted>' => 100,
                'amountConverted<=' => 999,
            ],
        ]
    ])
    ->findOne();

Sub-query

<?php
use Espo\ORM\Query\Part\Condition as Cond;

$collection = $entityManager
    ->getRDBRepository($entityType)
    ->where(
        Cond::in(Cond::column('id'), $subQuery)
    )
    ->find();

Condition

As of v7.0.

<?php
use Espo\ORM\Query\Part\Condition as Cond;

$collection = $entityManager
    ->getRDBRepository($entityType)
    ->where(
        Cond::or(
            Cond::equal(Cond::column('someColumn'), '1'),
            Cond::equal(Cond::column('someColumn'), '2')
        )
    )
    ->find();
<?php
use Espo\ORM\Query\Part\Condition as Cond;

$collection = $entityManager
    ->getRDBRepository($entityType)
    ->where(
        Cond::in(Cond::column('id'), $subQuery)
    )
    ->find();

Distinct

<?php
$opportunityList = $entityManager
    ->getRDBRepository('Opportunity')
    ->distinct()
    ->find();

Join

Join relationship:

<?php
$contactList = $entityManager
    ->getRDBRepository('Contact')
    ->distinct()
    ->join('opportunities')
    ->where([
        'opportunities.stage' => 'Closed Won'
    ])
    ->find();

Left-Join relationship:

<?php
$contactList = $entityManager
    ->getRDBRepository('Contact')
    ->distinct()
    ->leftJoin('opportunities')
    ->find();

'opportunities' is a relationship name.

Join alias:

<?php
$contactList = $entityManager
    ->getRDBRepository('Contact')
    ->distinct()
    ->join('opportunities', 'aliasForJoinedTable')
    ->where([
        'aliasForJoinedTable.stage' => 'Closed Won'
    ])
    ->find();

Joining any table (a table name should start with an apper case letter):

<?php
$meetingList = $entityManager
    ->getRDBRepository('Meeting')
    ->join(
        'MeetingUser', // meeting_user table
        'meetingUser', // alias
        [
            // Colon indicates that the right part is not a value.
            // It translates to `meetingUser.meeting_id = meeting.id`.
            'meetingUser.meetingId:' => 'id', // join condition
            'meetingUser.deleted' => false, // important
        ],
    )
    ->where([
        'meetingUser.userId' => $user->getId()
    ])
    ->find();

Joining a table with the query builder:

<?php
$query = $entityManager
    ->getRDBRepository('Meeting')
    ->select([
        'id',
        'name',
        ['meetingUser.status', 'meetingStatus'], // expression and alias
    ])
    ->join(
        'MeetingUser', // meeting_user table
        'meetingUser', // alias
        [
            'meetingUser.meetingId:' => 'id', // join condition
            'meetingUser.deleted' => false, // important
        ],
    )
    ->where([
        'meetingUser.userId' => $user->getId(),
    ])
    ->find();

Important

When joining by a table name (upper case is used), 'deleted' => false filter is not applied implicitly. You need to provide it explicitly.

Group By

<?php
$query = $entityManager
    ->getQueryBuilder()
    ->select()
    ->from('Opportunity') // entity type
    ->select(['MONTH:(closeDate)', 'SUM:(amountConverted)']) // complex expressions
    ->groupBy('MONTH:(closeDate)') // complex expression
    ->where([
        'stage' => 'Closed Won'
    ])
    ->order('MONTH:(closeDate)')
    ->build();

$pdoStatement = $entityManager
    ->getQueryExecutor()
    ->execute($query);

$rowList = $pdoStatement->fetchAll(\PDO::FETCH_ASSOC);

STH collection

If STH is set (with sth method), the find method will return a collection (instance of SthCollection) that doesn't allocate memory for all result data.

<?php
$collection = $entityManager
    ->getRDBRepository('Email')
    ->limit(0, 10000)
    ->sth()
    ->find();

foreach ($collection as $entity) {
    // memory is allocated for each item, when collection is iterated
}

Complex expressions

MONTH:(closeDate) and SUM:(amountConverted) in the example above are complex expressions. See more about them.

It's possible to build expressions with the Expression class (as of v7.0).

<?php
use Espo\ORM\Query\Part\Expression as Expr;

$queryBuilder->select(
    Expr::if(
        Expr::greaterOrEqual(Expr::column('opportunity.amount'), 1000),
        '1000 or more',
        'less than 1000'
    ),
    'alias'         
);
<?php
use Espo\ORM\Query\Part\Expression as Expr;

$queryBuilder->where(
    Expr::greater(
        Expr::column('opportunity.amount'),
        1000
    )
);

It's possible to add custom functions (as of v7.0.8). An implementation class name for a custom function should be defined in metadata by the path app > orm > functionConverterClassNameMap_Mysql. The class should implement Espo\ORM\QueryComposer\Part\FunctionConverter interface.

Query builder

Delete:

<?php
$deleteQuery = $entityManager
    ->getQueryBuilder()
    ->delete()
    ->from('SomeTable')
    ->where([
        'someColumn' => 'someValue'
    ])
    ->build();

$entityManager
    ->getQueryExecutor()
    ->execute($deleteQuery);

Select:

<?php
$selectQuery = $entityManager
    ->getQueryBuilder()
    ->select(['column1', 'column2', 'someExpression'])
    ->from('SomeTable')    
    ->order('column1', 'DESC')
    ->limit(0, 10)
    ->build();

$pdoStatement = $entityManager
    ->getQueryExecutor()
    ->execute($selectQuery);
<?php
$selectQuery = $entityManager
    ->getQueryBuilder()
    ->select('SUM:(someColumn)', 'value')
    ->from('SomeTable')    
    ->select('anotherColumn')
    ->groupBy('anotherColumn')
    ->build();

$row = $entityManager
    ->getQueryExecutor()
    ->execute($selectQuery)
    ->fetch();

$sum = $row['value'];

Update:

<?php
$updateQuery = $entityManager
    ->getQueryBuilder()
    ->update()
    ->in('SomeTable')
    ->set(['status' => 'Expired'])
    ->where([
        'status' => 'Pending',
        'expiresAt' => $dateTimeString,
    ])
    ->build();

$entityManager->getQueryExecutor()->execute($updateQuery);

Update with join (not implemented for PostgreSQL):

<?php
$updateQuery = $entityManager
    ->getQueryBuilder()
    ->update()
    ->in('SomeTable')
    ->set(['column:' => 'joinAlias.foreignColumn'])
    ->join('AnotherTable', 'joinAlias', ['joinAlias.foreignId:' => 'id'])
    ->where([
        'someColumn' => 'someValue'
    ])
    ->build();

$entityManager
    ->getQueryExecutor()
    ->execute($updateQuery);

Insert:

<?php
$insertQuery = $entityManager
    ->getQueryBuilder()
    ->insert()
    ->into('SomeTable')
    ->columns(['column1', 'column2'])
    ->values([
        'column1' => 'value1',
        'column2' => 'value2',
    ])
    ->build();

$entityManager->getQueryExecutor()->execute($insertQuery);

Mass insert:

<?php
$insert = $entityManager
    ->getQueryBuilder()
    ->insert()
    ->into('SomeTable')
    ->columns(['column'])
    ->values([
        ['column1' => 'value1'],
        ['column2' => 'value2'],
    ])
    ->build();

$entityManager
    ->getQueryExecutor()
    ->execute($insert);

Mass insert by populating with a select sub-query:

<?php
$insertQuery = $entityManager
    ->getQueryBuilder()
    ->insert()
    ->into('SomeTable')
    ->columns(['column'])
    ->valuesQuery($subQuery)
    ->build();

$entityManager
    ->getQueryExecutor()
    ->execute($insertQuery);

Union:

<?php
$unionQuery = $entityManager
    ->getQueryBuilder()
    ->union()
    ->all()
    ->query($subQuery1)
    ->query($subQuery2)
    ->order(1, 'DESC')
    ->limit(0, 5)
    ->build();

$sth = $entityManager
    ->getQueryExecutor()
    ->execute($unionQuery);

Cloning and modifying an existing query:

<?php
$clonedQuery = $entityManager
    ->getQueryBuilder()
    ->clone($query)
    ->limit(0, 10)
    ->build();

Mass insert

Mass insert with Mapper:

<?php
$entityManager->getMapper()->massInsert($collection);

Transaction manager

Transaction:

<?php
$tm = $entityManager->getTransactionManager();

$tm->start();

try {
    // do something
    $tm->commit();
}
catch (Throwable $e) {
    $tm->rollback(); // this will roll back everything done within the transaction
}

Nested transactions:

<?php
$tm = $entityManager->getTransactionManager();

$tm->start();
// do something
$tm->start();
// do something
$tm->commit();
// do something
$tm->commit();

Running a function in a transaction:

<?php
$entityManager
    ->getTransactionManager()
    ->run(
        function () {
            // transaction started implicitly
            // do something
            // transaction committed implicitly or rolled back if an exception occurred
        }
    );

Locking:

<?php
$entityManager->getTransactionManager()->start();

$entity = $entityManager
    ->getRDBRepository('SomeTable')
    ->where(['id' => $id])
    ->forUpdate() // this will lock selected rows until the transaction is finished
    ->findOne();

// do something with entity

$entityManager->saveEntity($entity);

$entityManager->getTransactionManager()->commit();

Locker

<?php
// this will start a transaction implicitly and lock a table
$entityManager->getLocker()->lockExclusive('SomeEntityType');

// do something

// this will unlock all locked tables
$entityManager->getLocker()->commit();

Defs

As of v7.0.

An access point to read ORM metadata: entity, attribute, field, relation and index definitions.

<?php
$defs = $entityManager->getDefs();

ORM Defs can also be required as a dependency so that it will be injected into your class. Use the type hint Espo\ORM\Defs.

Check entity existence:

<?php
$entityExists = $defs->hasEntity($entityType);

Entity defs:

<?php
$entityDefs = $defs->getEntity($entityType);

An attribute list:

<?php
$attributeList = $entityDefs->getAttributeNameList();

Attribute defs:

<?php
$attributeDefs = $entityDefs->getAttribute($attributeName);

A relation list:

<?php
$relationList = $entityDefs->getRelationNameList();

Relation defs:

<?php
$relationDefs = $entityDefs->getRelation($relationName);

A field list:

<?php
$fieldList = $entityDefs->getFieldNameList();

Field defs:

<?php
$fieldDefs = $entityDefs->getField($field);

See all available methods in:

  • Espo\ORM\Defs\EntityDefs
  • Espo\ORM\Defs\AttributeDefs
  • Espo\ORM\Defs\RelationDefs
  • Espo\ORM\Defs\FieldDefs
  • Espo\ORM\Defs\IndexDefs

Attribute types:

  • Entity::ID
  • Entity::VARCHAR
  • Entity::INT
  • Entity::FLOAT
  • Entity::TEXT
  • Entity::VARCHAR
  • Entity::FOREIGN
  • Entity::FOREIGN_ID
  • Entity::FOREIGN_TYPE
  • Entity::DATE
  • Entity::DATETIME
  • Entity::JSON_ARRAY

Relation types:

  • Entity::MANY_MANY
  • Entity::HAS_MANY
  • Entity::BELONGS_TO
  • Entity::HAS_ONE
  • Entity::BELONGS_TO_PARENT
  • Entity::HAS_CHILDREN
  • Entity::JSON_OBJECT