Doctrine update query with LIMIT

Question

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 ?

Postnatal

Answer 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
 );

Licentious

SELECT': Error: Class '(' is not defined – Eccrinology

Answer 2

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.

Blim

Answer 3

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

Durazzo

No it does not help. I need a query that updates multiple, but not all rows of a table. All that in one query. I dont want to select some and then update one by one. – Postnatal

@Postnatal see if that's close to what you need – Durazzo

Your provided way 1 and way 2 do not work. I tried it before posting here. You should have done that, too. Your suggestion of Native SQL works. – Postnatal

I've just tried something similar but $query->setMaxResults(20); doesn't seem to work for update queries - no limit was placed on the update statement that shows in Symfony2's logs. – Blim