Using InnerJoin with Limit

0

I have the A table with a OneToMany relation to the B table.

The A table is small, has 10 records.

The B table has N records for each record in the A table.

From X to X minutes a script is executed that exports the 50 new records from the B table (for each record in the A table) to a txt file, for example: A table has 10 registers, then the system looks in the B table for the 50 new records for each record in the A table, in this case it will be 10x50, then it will be exported to the txt 500 records file.

For this I made a innerjoin in the repository:

class AppsRepository extends EntityRepository
{

    public function findNewClients()
    {
        $qb = $this->createQueryBuilder('a');
        $qb
            ->innerJoin('AppBundle\Entity\Clients', 'b', 'WITH', 'b.appId = a.id')
            ->where('b.proccessed is null')
    ;

        return $qb->getQuery()->getResult();
    }
}

I need to limit the innerjoin to return only 50 records for each record in the A table as explained above.

    
asked by anonymous 07.06.2016 / 13:43

2 answers

0

Do this:

class AppsRepository extends EntityRepository
{
    public function findNewClients()
    {
        return $this
            ->getEntityManager()
            ->createQuery('
                SELECT AppBundle:Apps a
                JOIN a.clients c
                WHERE c.processed IS NULL')
            ->setMaxResults(50)
            ->getResult();
    }
}
    
24.06.2016 / 08:43
-1

What if you use a LEFT JOIN or RIGHT JOIN?

Something like this:

SELECT t1.coluna1, t2.coluna1 FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.coluna1 = t2.coluna1 LIMIT 50

    
07.06.2016 / 14:57