Announcement

Collapse
No announcement yet.

Update entity collection instead of looping through entities

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Update entity collection instead of looping through entities

    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:

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

    PHP Code:
    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.

  • #2
    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);​
    }​
    Last edited by item; 09-25-2022, 06:39 PM.

    Comment


    • bandtank
      bandtank commented
      Editing a comment
      That looks like the same code as what I wrote in my original post. The idea is to avoid looping.

  • #3
    > native support from EntityCollection

    Would be impossible to support any possible collection.

    Query Builder:

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

    Code:
    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.
    Last edited by yuri; 09-25-2022, 07:49 PM.

    Comment


    • bandtank
      bandtank commented
      Editing a comment
      That helps. Thanks.

  • #4
    Mass relate:

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

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

    Comment


    • bandtank
      bandtank commented
      Editing a comment
      This is the method I was trying to use, but I couldn't figure out how to write it.

  • #5
    Note: You can build queries for relation tables.

    Example for: 'account_contact':

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

    You can also join them:

    PHP Code:
    $selectQuery $queryBuilder
        
    ->select()
        ->
    from(Account::ENTITY_TYPE)
        ->
    join(
            
    'AccountContact',
            
    'accountContactAlias',
            [
    'accountContactAlias.accountId=:' => 'id']
        )
        ->
    build(); 
    Last edited by yuri; 09-26-2022, 02:30 PM.

    Comment

    Working...
    X