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