I would like to do an update-query with a LIMIT like that:
UPDATE anytable SET anycolumn = 'anyvalue' WHERE anothercolumn='anothervalue' LIMIT 20
How is this possible with doctrine 2.1 ?
I would like to do an update-query with a LIMIT like that:
UPDATE anytable SET anycolumn = 'anyvalue' WHERE anothercolumn='anothervalue' LIMIT 20
How is this possible with doctrine 2.1 ?
Not doctrine specific, but maybe possible with a subquery ?
UPDATE messages SET test_read=1
WHERE id IN (
SELECT id FROM (
SELECT id FROM messages
ORDER BY date_added DESC
LIMIT 5, 5
) tmp
);
I found I had to fetch the connection from the entityManager and call executeUpdate:
$em->getConnection()->executeUpdate(
"UPDATE anytable SET anycolumn = 'anyvalue'
WHERE anothercolumn='anothervalue'
LIMIT 20");
The doctrine page about native queries says:
If you want to execute DELETE, UPDATE or INSERT statements the Native SQL API cannot be used and will probably throw errors. Use EntityManager#getConnection() to access the native database connection and call the executeUpdate() method for these queries.
EDIT:
you can go about it in 2 different ways:
1 - Create query directly using DQL:
$query = $entityManager->createQuery('UPDATE Entities\User u SET u.someValue = newValue WHERE u.id = someId');
// this will add the LIMIT statement
$query->setMaxResults(20);
$query->execute();
2 - Create query using QueryBuilder:
$qb = $this->_em->createQueryBuilder();
$query = $qb->update('Entities\User', 'u')
->set('u.someValue', newValue)
->where('u.id = someId')
->getQuery();
// this will add the LIMIT statement
$query->setMaxResults(20);
$query->execute();
you should do: echo $query->getSQL();
to check out the sql generated for these two
EDIT: another alternative (not highly recommended) is to use Native SQL