ORM. How to manage entities and perform queries

EspoCRM has built-in own ORM (Object-relational mapping). It’s very simple to create, update, read, delete and search entities. All these operations available through EntityManager object.

EntityManager is available in Container. It's a central access point for ORM functionalities.

Repository serves for fetching and storing records. Each entity type has its own repository. Base classes: Espo\ORM\Repositories\RDBRepository, Espo\Core\Repositories\Database. RDB stands for a relational database.

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

EntityCollection is a collection of entities. It's returned by find operations.

SthCollection is a collection of entities, consuming much less memory than EntityCollection.

See also

• Complex expressions
• Value objects

Injecting Entity Manager

Entity Manager is available as a Container service.

A class with the entityManager dependency:

<?php
namespace Espo\SomeNamespace;

use Espo\ORM\EntityManager;

class SomeClass
{
    private EntityManager $entityManager;

    public function __construct(EntityManager $entityManager)
    {
        $this->entityManager = $entityManager;
    }
}

Entity Manager usage

Create new entity

$entity = $entityManager->getEntity($entityType)

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

Fetch existing entity

$entity = $entityManager->getEntity($entityType, $id);

Store entity

$entityManager->saveEntity($entity);

With options:

$options = [
    'skipHooks' => true, // skip all hooks; workflows, formula will be ignored
    'silent' => true, // workflows will be ignored, modified fields won't be changed
    'skipCreatedBy' => true, // createdBy won't be set with current user
    'skipModifiedBy' => true, // modifiedBy won't be set with current user
    'createdById' => true, // override createdBy
    'modifiedById' => true, // override modifiedBy
];

$entityManager->saveEntity($entity, $options);

Create and store entity

$entity = $entityManager->createEntity($entityType, [
    'name' => 'Test',
]);

Remove entity

$entityManager->removeEntity($entity);

Get attribute value

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

Has attribute value

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

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

Set attribute value

One:

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

Multiple:

$entity->set([
    'name' => 'Test Name',
    'assignedUserId' => '1',
]);

Clear attribute value

$entity->clear('attributeName');

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

Fetched attributes

You can check whether an attribute was changed.

// 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
$isChanged = $entity->isChanged('attributeName');

Get all attribute values

$valueMap = $entity->getValueMap(); // StdClass

Delete from DB

$entityManager->getRDBRepository($entityType)->deleteFromDb($id);

This will delete a record permanently.

Attributes

Note: As of v6.2.0 it's recommended to use ORM Defs to get entity definitions. See below about ORM Defs.

Each entity type has its own set of defined attributes. You cannot set an arbitrary attribute name.

// whether attribute is defined for entity
$hasAttribute = $entity->hasAttribute('attributeName');

$attributeList = $entity->getAttributeList();

$attributeType = $entity->getAttributeType('attributeName');

$paramValue = $entity->getAttributeParam('attributeName', 'attributeParam');

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
• Entity::JSON_OBJECT

Relations

Note: As of v6.2.0 it's recommended to use ORM Defs to get entity definitions. See below about ORM Defs.

$relationList = $entity->getRelationList();

$type = $entity->getRelationType('relationName');

$paramValue = $entity->getRelationParam('relationName', 'paramName')

Relation types:

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

Find

$collection = $entityManager
    ->getRDBRepository($entityType)
    ->where([ // where clause
        'type' => 'Customer',
    ])
    ->find();

Descending order:

$collection = $entityManager
    ->getRDBRepository($entityType)
    ->limit(0, 10)
    ->order('createdAt', true)
    ->find();

Ascending order:

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

Descending order:

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

Complex order:

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

Or:

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

Or (as of v7.0):

<?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:

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

Feeding a query to a repository:

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

Find the first one

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

Find related

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

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

Filtering by a relation column:

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

Relate entities

$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

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

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

Update columns

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

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

Check whether related

$isRelated = $entityManager
    ->getRDBRepository('Account')
    ->getRelation($account, 'opportunities')
    ->isRelated($opportunity);

Select Query Parameters

Where clause

Comparison operators

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

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

IN and NOT IN operators

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

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

LIKE operators

Supported operators:

• * - LIKE
• !* - NOT LIKE

$opportunityList = $entityManager
    ->getRDBRepository('Opportunity')
    ->where([
        'name*' => '%service%',
    ])
    ->find();

OR, AND operators

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

Sub-query operator

// $query is the instance of Espo\ORM\Query\Select

$collection = $entityManager
    ->getRDBRepository($entityType)
    ->where([
        'id=s' => $query->getRaw(),
    ])
    ->find();

Condition

Available as of v7.0.

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

use Espo\ORM\Query\Part\Condition as Cond;

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

Distinct

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

Join

Join relationship:

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

Left Join relationship:

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

'opportunities' is a relationship name.

Joining any table:

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

Join table alias:

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

Group By

$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(1) // ordering by the first column
    ->build();

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

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

Additional Params

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.

$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.

As of v7.0 it's possible to build expressions in OOP way.

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

use Espo\ORM\Query\Part\Expression as Expr;

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

As of v7.0.8 it's possible to add custom functions. 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:

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

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

Select:

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

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

$selectQuery = $entityManager
    ->getQueryBuilder()
    ->select('SUM:(someColumn)', 'value')
    ->from('SomeTable')    
    ->select('anotherColumn')
    ->groupBy('anotherColumn')
    ->build();

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

$sum = $row['value'];

Update:

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

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

Update with join:

$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:

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

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

Mass insert:

$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:

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

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

Union:

$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:

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

Mass insert

Mass insert with Mapper:

$entityManager->getMapper()->massInsert($collection);

Transaction manager

Transaction:

$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:

$tm = $entityManager->getTransactionManager();

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

Running a function in a transaction:

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

Locking:

$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

// 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

Available as of v7.0.

$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 existance:

$entityExists = $defs->hasEntity($entityType);

Entity defs:

$entityDefs = $defs->getEntity($entityType);

An attribute list:

$attributeList = $entityDefs->getAttributeNameList();

Attribute defs:

$attributeDefs = $entityDefs->getAttribute($attributeName);

A relation list:

$relationList = $entityDefs->getRelationNameList();

Relation defs:

$relationDefs = $entityDefs->getRelation($relationName);

A field list:

$fieldList = $entityDefs->getFieldNameList();

Field defs:

$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