How to set a limit in delete query?

Question

I try to delete some data with Doctrine\DBAL\Connection, I want to delete the duplicate data so I have to delete n-1 data (If n data are the same).

public function deleteDuplicateData(array $data) : bool
{
    $qb = $this->connection->createQueryBuilder();
    $qb->delete('myTable')
        ->where('id= :id')
        ->setParameter('id', $data['id'])
        ->setMaxResults($data['n']-1)
    ;
    return $qb->execute();
}

However the ->setMaxResults($data['n']-1) doesn't work, when I run my code all data are deleted. I tried this ->setMaxResults($data['n']-1) but it does'nt work so I think the method ->setMaxResults() doesn't work for the delete method.

https://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table You might want to check out this entry for all the ways to remove duplicates. Of course your best bet is to fix it so you don't get the duplicate data in the first place — Brian Hoover, Nov 03 '17 at 13:50
I can't check the entries because it's not my job, I have to code a script which is deleted duplicate entries ... You're right it will be easier to check it but I can't :/ — N.Jourdan, Nov 03 '17 at 13:57
Maybe doctrine just doesn't support this, because not all databases do. If it is an option for you you can use a native query without DQL if the underlying database supports delete with limit. — Joe, Nov 03 '17 at 14:12
Looking at the SqlWalker class of doctrine confirms the suspicion. The value for maxResults is only considered in select statements and completely ignored in delete statements. So if you have to do it this way a native query seems to be your only option — Joe, Nov 03 '17 at 14:25

score 0 · Answer 1 · answered Nov 03 '17 at 14:54

0

I cant comment, so sry for this ^^

Is it possible to count the rows with duplicate data in your DB System + Do they have the same ID? If yes, you could store the ammount - 1 to a variable $duplicatedRows and use a for loop like:

for($i;$<=$duplicatedRow;$i++){
    //Your Code to delete something  
}

answered Nov 03 '17 at 14:54

Celebrombore

170
11

Yes but the query deletes all duplicate, if I want to delete one row I have to use hte LIMIT and it does'nt work :( – N.Jourdan Nov 03 '17 at 15:07
Maybe this: http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/query-builder.html -> under "5.2.6. Limit Clause". They are writing that you "have to" use both "->setFirstResult(10)" and "->setMaxResults(20);" – Celebrombore Nov 03 '17 at 15:17

score 0 · Answer 2 · answered May 06 '21 at 14:39

0

setMaxResults works only in some cases. It seems to ignore it if it's not managed.

check the Doctrine doc : https://www.doctrine-project.org/projects/doctrine1/en/latest/manual/dql-doctrine-query-language.html#driver-portability

answered May 06 '21 at 14:39

bloub

101
1
3

score -1 · Answer 3 · answered Nov 03 '17 at 13:51

-1

use below function for set limit

public function deleteDuplicateData(array $data) : bool
    {
        $limit = 10;
        $qb = $this->connection->createQueryBuilder();
        $qb->delete('myTable')
            ->where('id= :id')
            ->setParameter('client_id', $data['id'])
            ->setMaxResults($limit);

        return $qb->execute();
    }

answered Nov 03 '17 at 13:51

Shital Marakana

2,817
1
9
12

Please add some explanation to your answer such that others can learn from it. What did you change, and why? – Nico Haase Oct 08 '21 at 10:17

How to set a limit in delete query?

3 Answers3