Symfony 2 - fetch the last inserted row from table

Question

How can I rewrite this code in order to get last inserted record from the table?

$repository = $entityManager->getRepository('AdminBundle:MyTable');
$product = $repository->find($id);

I tried something like

$repository->findBy(array('id','DESC')->setMaxResults(1);

But it did not work for me.

Roethke

Answer 1

You could get the latest record by using findBy() with order by, limit and offset parameters

$results = $repository->findBy(array(),array('id'=>'DESC'),1,0);

• First argument is for filter criteria
• Second argument takes order by criteria
• Third argument is for limit
• Fourth argument sets offset

Note it will return you the results set as array of objects so you can get single object from result as $results[0]

FindBy() Examples

Heidt

I used your code and get this error: "[Doctrine\ORM\ORMException] Invalid order by orientation specified for Backend\AdminBundle\Entity\MyTable#0" – Roethke

@Roethke My bad it was key/value pair 'id'=>'DESC' check the updated answer – Heidt

I think that works! I get no errors. But I stil have a problem with fetching data from $results array. I mean when I try to type something like $output->writeln($results[0]); I get error: "PHP Notice: Undefined offset: 0". I'm a little newbie in PHP/Symfony so maybe it's very dummy question but I don't know how I can get info from this variable. Can you help? – Roethke

@Roethke what do you see if you dump $results – Heidt

Ok I found where was the problem. I change argument for limit from 0 to 1 and now everything works fine. Thanks a lot for your help! – Roethke

Slight nuance, you passed 0 as limit and 1 as offset, you rather want to reverse that with $results = $repository->findBy(array(),array('id'=>'DESC'),1,0); – Hild

Answer 2

Instead of hacking code where you want to use it, you can also create a repository method and call it when necessary.

/**
 * Repository method for finding the newest inserted
 * entry inside the database. Will return the latest
 * entry when one is existent, otherwise will return
 * null.
 *
 * @return MyTable|null
 */
public function findLastInserted()
{
    return $this
        ->createQueryBuilder("e")
        ->orderBy("id", "DESC")
        ->setMaxResults(1)
        ->getQuery()
        ->getOneOrNullResult();
}

References: https://symfony.com/doc/current/doctrine.html#querying-for-objects-the-repository

Nurserymaid

Replacing id by e.id seems to be necessary. – Palpebrate

While the answer provided by @M Khalid Junaid offers simplicity, the one posted by @Nurserymaid has better reusability and allows your repository/query logic remain in one place, i.e. inside the repository class and therefore should be preferred in most cases. – Rationale

Answer 3

After looking for one I decided to try it myself, I think it was much less verbose:

$myRepository->findOneBy([], ['id' => 'DESC']);

Professorate

Answer 4

Please try the below one

$repository = $entityManager->getRepository('AdminBundle:MyTable');
$repository->setMaxResults(1)->orderBy('id', 'DESC');
$results = $repository->getQuery()->getSingleResult();

Reference: https://undebugable.wordpress.com/2016/01/27/symfony2-querybuilder-find-first-and-find-last-record-in-table/

Penalize

I used your approach and get this error: "Undefined method 'orderBy'. The method must star with either findBy or findOneBy!" – Roethke

Answer 5

You can add these functions to your repository:

public function getLastRow(): ?YourEntity
{
    return $this->findOneBy([], ['id' => 'DESC']);
}

public function getLastId(): int
{
    $lastRow = $this->getLastRow();
    return $lastRow ? $lastRow->getId() : 0;
}

Herodotus

Answer 6

You can be collected by getting the id of the inserted object

$em->persist($entity);
$em->flush();
$entity->getId();

OR

$entitymanager->getRepository("entity")->findBy([],["id"=>desc])->getId();

Sachs