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.

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

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

Get all values¶

$account->getValueMap();

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.

Attribues¶

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¶

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

Descending order:

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

Ascending order:

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

or:

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

Complex order:

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

Ordering by list:

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

Find the first one¶

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

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

Relate entities¶

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

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

Unrelate entities¶

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

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

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

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

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¶

$selectParams = [
  'select' => ['MONTH:closeDate', 'SUM:amountConverted']
  'groupBy' => ['MONTH:closeDate'],
  'whereClause' => [
    'stage' => 'Closed Won'
  ],
  'orderBy' => 1, // ordering by the first column
];

$pdo = $this->getEntityManager()->getPDO();
$sql = $this->getEntityManager()->getQuery()->createSelectQuery('Opportunity', $selectParams);
$sth = $pdo->prepare($sql);
$sth->execute();

// results
$rowList = $sth->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.