Update entity collection instead of looping through entities

[bandtank]

Is there a way to update every record in an entity collection that results in one SQL query? Looping through individual records requires more time and database traffic than a single query. I often write raw SQL to speed up hooks and services, which I would like to avoid. The following two examples happen all the time in my system:

foreach($claims as $claim) {
  $claim->set(array(
    "dateSubmitted" => (new Datetime())->format("Y-m-d H:i:s"),
    "status" => "Submitted",
   ));
  $this->entityManager->saveEntity($claim);​
} 

foreach($claims as $claim) {
  $this->entityManager->getRDBRepository("Document")
    ->getRelation($document, "claims")->relate($claim);
}​ 

The number of queries is equal to the number of entities in the collection; however, each update could happen in a single query. I am aware of the Query Builder, but I haven't figured out how to use it yet because it is complicated. This seems like a common thing to want to do, so native support from EntityCollection would be awesome.

[item]

Hi,
if i understand, you have a : Document one2many Claim relation.
if you look database, you will see in claim table a field : document_id ..

so you can do (work in 6.x) :

foreach($claims as $claim) {
$claim->set(array(
"dateSubmitted" => (new Datetime())->format("Y-m-d H:i:s"),
"status" => "Submitted",
"documentId" => $document->id, (or $document->getId())
));
$this->entityManager->saveEntity($claim);
}

[bandtank]

That looks like the same code as what I wrote in my original post. The idea is to avoid looping.

[yuri]

> native support from EntityCollection

Would be impossible to support any possible collection.

Query Builder:

$updateQuery = $entityManager
    ->getQueryBuilder()
    ->update()
    ->in(Claim::ENTITY_TYPE) // or simply 'Claim'
    ->set([
        'status' => 'Submitted' // or better use constant Claim::STATUS_SUBMITTED
    ])
    ->where([
        'documentId' => $document->getId(),
    ])
    ->build();

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

vs SQL:

UPDATE
IN `claim`
SET
    status = 'Submitted'
WHERE
    documentId = :documentId AND
    deleted = 0

Almost identical, nothing complex. Benefits: no need to know the table name (IDE, static analysis helps); possible to apply filters, access control; easier to work when data is dynamic; no need to care about escaping (with PDO it can be pain with some statements to escape). I haven't wrote any plain SQL in Espo and related projects for years.

[bandtank]

That helps. Thanks.

[yuri]

Mass relate:

$query = $entityManager
    ->getQueryBuilder()
    ->select()
    ->from(EntityToRelate::ENTITY_TYPE)
    ->where([
        'someField' => $someValue,
    ])
    ->build();

$entityManager
    ->getRDBRepository(MyEntity::ENTITY_TYPE)
    ->getRelation($entity, 'linkName')
    ->massRelate($query); 

[bandtank]

This is the method I was trying to use, but I couldn't figure out how to write it.

[yuri]

Note: You can build queries for relation tables.

Example for: 'account_contact':

$updateQuery = $queryBuilder
    ->update()
    ->in('AccountContact')
    ->set([
        'role' => 'Test',
    ])
    ->where($whereClause)
    ->build(); 

You can also join them:

$selectQuery = $queryBuilder
    ->select()
    ->from(Account::ENTITY_TYPE)
    ->join(
        'AccountContact',
        'accountContactAlias',
        ['accountContactAlias.accountId=:' => 'id']
    )
    ->build();