如何添加适当的“Doctrine查询”构建器并加入表

Question

I am trying to build this SQL query into query builder but when adding a join, it breaks. If I take out join it works but there are the item's in Join that id like to have access to.

I have tried different methods of using query builder, I have read the documentation and follow its instructions but its not working.

Here is my SQL query

SELECT * FROM invoice i
      JOIN item it

WHERE  i.created_at >= '2019-03-26 08:00:00' and
i.created_at <= '2019-03-26 08:05:00' and i.status=2;

Here is my Query builder inside Symfony.

       /** @var \Doctrine\ORM\EntityManager $em *
    $em = $container->get('doctrine')->getManager();

    $em->getConnection()->getConfiguration()->setSQLLogger(null);

      /** @var EntityRepository $repository */
    $repository = $em->getRepository('GivingBundle:Invoice');

      /** @var QueryBuilder $qb */
     $qb = $repository->createQueryBuilder('i');
    $qb->join('i.item', 'it')
         ->Where($qb->expr()->between('i.created_at', ':starts_at', ':ends_at'))->andWhere('i.status = 1')

        ->setParameter('starts_at',$startsAt,\Doctrine\DBAL\Types\Type::DATETIME)
        ->setParameter('ends_at', $endsAt,\Doctrine\DBAL\Types\Type::DATETIME)
    ;

    $query = $qb->getQuery();
    $results = $query->getResult();

However, I am getting an error (below)

Doctrine\ORM\Query\QueryException]                                                                                                          
  [Semantical Error] line 0, col 66 near 'it WHERE (i.created_at': 
 Error: Class KCM\GivingBundle\Entity\Invoice has no association 
named item  

What id like to do is select the Invoice table base along with the Item table so I can access both of its properties. I am trying to create a Symfony command that runs an SQL query, report results for items in status 2 and eventually adds it as a cronjob.

Answer 1

Can you show code for associations in your entity? From your question it looks like, it should be something like

/** @Entity */
class Invoice
{
    // ...
    /**
     * This is the inverse side.
     * @OneToMany(targetEntity="Items", mappedBy="invoice")
     */
    private $items;
    // ...

    public function __construct() {
        $this->items= new ArrayCollection();
    }
}

/** @Entity */
class Items
{
    // ...
    /**
     * This is the owning side.
     * @ManyToOne(targetEntity="Invoice", inversedBy="items")
     * @JoinColumn(name="invoice_id", referencedColumnName="id")
     */
    private $invoice;
    // ...
}

More on associations at https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/association-mapping.html#one-to-many-bidirectional