orm.md

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.

See also

• Complex expressions
• Value objects

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)
    {}
}

Usage

Create new entity

$entity = $entityManager->getNewEntity($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->getEntityById($entityType, $id);

Store entity

$entityManager->saveEntity($entity);

With options:

use Espo\Core\ORM\Repository\SaveOption;

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

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.

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
$attributeChanged = $entity->isAttributeChanged('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 v7.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 v7.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

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.

Join alias:

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

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

$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

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

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.

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

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

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:

$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 (not implemented for PostgreSQL):

$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

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

$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