CODEWITHSUNDEEP
phpsymfonydoctrine-orm
Calcimicium
Calcimicium

Reputation: 115

Doctrine query one entity in one-to-many unidirectional with join table

I have two entities linked by a one-to-many unidirectional with join table association.

use Doctrine\ORM\Mapping as ORM;

/**
 * @Entity(repositoryClass="FooRepository")
 */
class Foo
{
    /**
     * @var Collection
     * @ORM\ManyToMany(targetEntity="Bar")
     * @ORM\JoinTable(
     *      name="foo_bar",
     *      inverseJoinColumns={@ORM\JoinColumn(unique=true)}
     * )
     */
    private $bars;

    /**
     * @var int
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    // [...]
}

/**
 * @Entity(repositoryClass="BarRepository")
 */
class Bar
{
    /**
     * @var int
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    // [...]
}

I'd like to create a method into my BarRepository class using the a foo id and a bar id which return one or null Bar object.

Actually my class looks like this:

use Doctrine\ORM\EntityRepository;

class BarRepository extends EntityRepository
{
    /**
     * Finds a single bar.
     * @param int $fooId The foo identifier.
     * @param int $barId The bar identifier.
     * @return Bar|null
     */
    public function findOneByFoo($fooId, $barId)
    {
        $qb = $this->createQueryBuilder('b');
        $qb->innerJoin('Foo', 'f', Expr\Join::WITH, 'f.id = :fooId')
        ->where('b.id = :barId')
        ->setParameter('fooId', $fooId)
        ->setParameter('barId', $barId)
        ->getQuery()->getOneOrNullResult();
    }
}

But this always returns a bar object even if the bar id is not associated to the foo object.

Upvotes: 3

Views: 4542

Answers (2)

Calcimicium
Calcimicium

Reputation: 115

OK, thanks to staskrak, I rewrite my "query" like this, and it works fine. The Foo and Bar entities are the same. I kept the same base of the query but added an inner join between the Foo->bars property and the Bar entity.

use Doctrine\ORM\EntityRepository;

class BarRepository extends EntityRepository
{
    public function findOneByFoo($fooId, $barId)
    {
        $parameters = [
            ':fooId' => $fooId,
            ':barId' => $barId
        ];
        $qb = $this->createQueryBuilder('b');
        return $qb
            ->innerJoin('Foo',    'f',  'WITH', 'f.id = :fooId')
            // below, the added inner join
            // it makes the link between the Foo->bars property and the Bar entity
            ->innerJoin('f.bars', 'fb', 'WITH', 'b.id = fb.id')
            ->where('b.id = :barId')
            ->setParameters($parameters)
            ->getQuery()
            ->getOneOrNullResult();
    }
}

Upvotes: 2

staskrak
staskrak

Reputation: 863

  1. First of all! It's not the required but I suggest you always write the full mapping in annotations.

    Let's look at you entities. We can make the next assertion about your One-To-Many:

    One Foo objects can have many Bar objects, but every Bar object refer to only one and no more Foo object. For example One person can have a lot of credit cards, but every credit card belongs to one single person.

Therefore we can write down: 

/**
 * @Entity(repositoryClass="FooRepository")
 */
class Foo
{
     /**
      * Unidirectional One-To-Many
      * One Foo has many Bar, however Bar has only one Foo
      * 
      * @ORM\ManyToMany(targetEntity="Bar")
      * @ORM\JoinTable(
      *      name="foo_bar_table",
      *      joinColumns={@JoinColumn(name="foo_id", referencedColumnName="id")},
      *      inverseJoinColumns={@JoinColumn(name="bar_id", referencedColumnName="id", unique=true)
      */
      private $bars;

     /**
      * Foo constructor
      */
      public function __construct()
      {
         $this->bars = new \Doctrine\Common\Collections\ArrayCollection();
      }

  1. Of course you always will have a bar object with id you have typed. Why? Let's look at your relations(tables). Foo - this is a table, which has field id.
    Bar - this is a table, which has field id. foo_bar_table - this table has foo_id, bar_id.

    When you make the join - you just add to the one table another one. These tables don't have association between each other. So you wanted the Bar object - you got it.
    You need to get the Bar object from the Bar repository. It will be better.

Upvotes: 1

Related Questions