Explain Codes LogoExplain Codes Logo

Doctrine 2: Update query with query builder

php
query-builder
doctrine-2
update-query
Anton ShumikhinbyAnton Shumikhin·Nov 3, 2024
TLDR

Quickly construct an update operation with the Doctrine Query Builder like so:

$qb->update('YourEntity', 'e') ->set('e.property', ':prop') ->where('e.criteria = :crit') ->setParameter('prop', $newValue) ->setParameter('crit', $criteriaValue) ->getQuery() ->execute();

In summary:

  • Substitute YourEntity for your exact entity class.
  • Replace e.property and e.criteria with your entity's specific fields.
  • Input $newValue and $criteriaValue to agree with your required updates and conditions.
  • Use the .execute() to apply the update.

Level up your Input Safety

In reshaping records in Doctrine 2 through the Query Builder, it's vital to maintain input security. This is where the flexible setParameter() method shines by making sure all inputted values are parameterized, significantly deterring SQL injection. Here's an enhanced snippet showcasing secure usage of variables such as $userName, $email, and $editId:

$qb->update(User::class, 'u') // "username" & "email" walk into an update bar... ->set('u.username', ':username') ->set('u.email', ':email') // ...they ask for a change… ->where('u.id = :id') // ...but only if "id" can vouch for them ->setParameter('username', $userName) ->setParameter('email', $email) ->setParameter('id', $editId) ->getQuery() ->execute(); // ...and voilà! They got their update.

Be sure to initialize variables before usage in the query to negate 'undefined variable' errors. Also, thoroughly test your queries with varying sample data to guarantee ascertained functionality.

Syntax and Structure - The Twins of Correctness

To ensure the appropriate syntax and structure of your update query, pay close attention to the QueryBuilder's method: update(). This method is crafted strictly for creating UPDATE commands with fields and conditions tethered via the set() and where() methods respectively.

For scenarios that are constant-value cases such as adjusting a status, make use of the literal() method to incorporate unchangeable values to your query ensuring integrity:

$qb->update(Order::class, 'o') // "status": "What's my value today?" ->set('o.status', $qb->expr()->literal('closed')) // "query": "You're 'closed'. No negotiations!" ->where('o.id = :id') ->setParameter('id', $orderId) ->getQuery() ->execute(); // "status": "Well, that's just great!"

Precision is indispensable when describing the update condition via the where() method. A small misstep could lead to unanticipated updates or catastrophic fatal errors. Always double-check your entire query and scrutinize error messages for rapid understanding and resolution of issues.

Speak the Expressive Language of Conditions

For complex conditions, the QueryBuilder's expr() method enables you to fabricate artful update statements. Whether you're gunning for a subset of records or utilizing comparison operators, expr() is your compass to advanced update logic:

$qb->update(Account::class, 'a') // "balance": "Who's up for a little bonus?" ->set('a.balance', 'a.balance + :bonus') ->where($qb->expr()->gte('a.balance', ':minBalance')) // "balance": "Only if you ask nicely!" ->andWhere($qb->expr()->eq('a.status', ':status')) ->setParameter('bonus', $bonusAmount) ->setParameter('minBalance', $minimumBalance) ->setParameter('status', 'active') ->getQuery() ->execute(); // "balance": "Well, when you put it that way..."

Beyond the Basics: Enhance your Update Operation

Handling AJAX: The Art of Efficiency

When grooving with AJAX requests, ensure your update operations are handled efficiently. It’s commonplace to retrieve data like user IDs from the request to perform updates. Confirm the success of these operations by ensuring the result is not empty:

$userId = $request->get('user_id'); // "AJAX": "Parcel for User ID!" if ($userId) { $result = $qb->update(User::class, 'u') ->set('u.lastLogin', ':now') ->where('u.id = :userId') ->setParameter('now', new \DateTime()) ->setParameter('userId', $userId) ->getQuery() ->execute(); // "User ID": "Well, good morning!" if ($result) { // "query": "The update was a success!" // Return success response } else { // Handle error } }

Exploiting Repositories for Efficiency

When coding within an entity's repository, there’s no need to specify the entity's name explicitly. You may utilize an alias, such as 'u', thus illustrating the underlying context of a repository:

public function updateUserName($userId, $newName) { return $this->createQueryBuilder('u') // "username": "Can I get a new name please?" ->update() ->set('u.name', ':newName') ->where('u.id = :userId') ->setParameter('newName', $newName) ->setParameter('userId', $userId) ->getQuery() ->execute(); // "username": "Ah! feels just like a new haircut" }

This snippet highlights the ease of using the repository's context to simplify the query.

Acknowledging a triumphant update

To acknowledge a successful update, you may need to verify if a non-empty result is returned. Critically checking if the number of affected rows matches expectations is crucial when conducting data consistency checks:

$affectedRows = $qb->update(/* ... */)->execute(); if ($affectedRows > 0) { // "query": "Did I do a good job?" // Update successful // "Database": "Couldn't have done it better myself!" }