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 can be obtained in record services by method #getEntityManager(). It provides an access to repositories.

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

Entity represents a single record. Each entity type has it's own entity class. Base classes: Espo\ORM\Entity, Espo\Core\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.

Obtaining the entity manager in the record service:

$entityManager = $this->getEntityManager();

Create new entity¶

$account = $entityManager->getEntity('Account')

// or
$account = $entityManager->getRepository('Account')->get();

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

Fetch existing¶

$account = $entityManager->getRepository('Account')->get($accountId);

// or from entity manager
$account = $entityManager->getEntity('Account', $accountId);

Get value¶

$fieldValue = $account->get('attributeName');

Has value¶

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

$fieldNameIsSet = $account->has('fieldName'); // true or false

Set value¶

One:

$account->set('attributeName', 'Test Account');

Multiple:

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

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

Reset¶

Resets all attributes.

$entity->reset();

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

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

Store¶

$entityManager->saveEntity($account);

// or
$entityManager->getRepository('Account')->save($account);

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($account, $options);

// or
$entityManager->getRepository('Account')->save($account, $options);

Create and store¶

$account = $entityManager->createEntity('Account', [
    'name' => 'Test',
]);

Remove¶

$entityManager->removeEntity($account);

// or
$entityManager->getRepository('Account')->remove($account);

Delete from DB¶

$entityManager->getRepository('Account')->deleteFromDb($id);

This will delete a record permanently.

Attributes¶

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:

id
varchar
int
float
text
bool
foreign
foreignId
foreignType
date
datetime
jsonArray
jsonObject

Relations¶

$relationList = $entity->getRelationList();

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

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

Relation types:

manyMany
hasMany
belongsTo
hasOne
belongsToParent
hasChildren

Find¶

$collection = $entityManager
    ->getRepository('Account')
    ->where([
        'type' => 'Customer',
    ])->find();

Descending order:

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

Ascending order:

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

or:

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

Complex order:

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

Ordering by a value list:

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

Feeding a query to a repository:

$collection = $entityManager->getRepository('SomeEntityType')
    ->clone($query)
    ->limit(0, 10)
    ->find();

Find the first one¶

$account = $entityManager
    ->getRepository('Account')
    ->where([
        'type' => 'Customer',
    ])
    ->findOne();

Before v6.0:

$opportunityCollection = $entityManager
    ->getRepository('Account')
    ->findRelated($account, 'opportunities');

Since v6.0:

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

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

Filtering by a relation column:

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

Relate entities¶

Before v6.0:

$entityManager->getRepository('Account')->relate($account, 'opportunities', $opportunity);

// or
$entityManager->getRepository('Account')->relate($account, 'opportunities', $opportunityId);

Since v6.0:

$entityManager
    ->getRepository('Account')
    ->getRelation($account, 'opportunities')
    ->relate($opportunity);

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

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

Unrelate entities¶

Before v6.0:

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

// or
$entityManager->getRepository('Account')->unrelate($account, 'opportunities', $opportunityId);

Since v6.0:

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

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

Update columns¶

Since v6.0:

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

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

Before v6.0:

$entityManager->getRepository('EntityType')->isRelated($entity, 'relationName', $relatedEntity);

// or
$entityManager->getRepository('EntityType')->isRelated($entity, 'relationName', $id);

Since v6.0:

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

Select Query Parameters¶

Where clause¶

Comparison operators¶

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

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

IN and NOT IN operators¶

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

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

LIKE operators¶

Supported operators:

* - LIKE
!* - NOT LIKE

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

OR, AND operators¶

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

Distinct¶

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

Join¶

Join relationship:

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

Left Join relationship:

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

'opportunities' is a relationship name.

Joining any table:

$meetingList = $entityManager
    ->getRepository('Meeting')
    ->join(
        'MeetingUser', // meeting_user table
        'meetingUser', // it's an alias
        [
          'meetingUser.meetingId:' => 'meeting.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
    ->getRepository('Contact')
    ->distinct()
    ->join('opportunities', 'aliasForJoinedTable')
    ->where([
      'aliasForJoinedTable.stage' => 'Closed Won'
    ])
    ->find();

Group By¶

$query = $entityManager
    ->getQueryBuilder()
    ->select() // indicates that we build a SELECT query
    ->from('Opportunity') // entity type
    ->select(['MONTH:(closeDate)', 'SUM:(amountConverted)']) // complex expressions
    ->groupBy('MONTH:(closeDate)') // complex expression
    ->whereClause([
        '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¶

Can be used with find and findRelated methods. With this param provided, they will return a collection that doesn't allocate memory for all result data.

$collection = $entityManager->getRepository('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.

Query builder¶

Delete:

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

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

Select:

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

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

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

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

$sum = $row['value'];

Update:

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

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

Update with join:

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

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

Insert:

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

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

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:

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

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

Union:

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

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

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

ORM, How to manage entities and perform queries¶

Create new entity¶

Fetch existing¶

Get value¶

Has value¶

Set value¶

Clear value¶

Reset¶

Fetched attributes¶

Get all values¶

Store¶

Create and store¶

Remove¶

Delete from DB¶

Attributes¶

Relations¶

Find¶

Find the first one¶

Find related¶

Relate entities¶

Unrelate entities¶

Update columns¶

Check related¶

Select Query Parameters¶

Where clause¶

Comparison operators¶

IN and NOT IN operators¶

LIKE operators¶

OR, AND operators¶

Distinct¶

Join¶

Group By¶

Additional Params¶

STH collection¶

Complex expressions¶

Query builder¶

Mass insert¶

Transaction manager¶

Locker¶