Doctrine 2 Model One-to-Many Many-to-One Queries

Question

I have such doctrine entities:

<?php

namespace App\Entity;
use Doctrine\Common\Collections\ArrayCollection;
/**
 * @Entity(repositoryClass="App\Repository\Page")
 * @Table(name="page")

 */
class Page
{
    /**
     * @Id @Column(type="integer", name="p_id")
     * @GeneratedValue
     */
    private $p_id;
    /** @Column(type="string", name="p_title") */
    private $p_title;
    /** @Column(type="datetime", name="p_created") */
    private $p_created_at;
    /** @Column(type="datetime", name="p_updated_at") */
    private $p_updated_at;
    /** @Column(type="text", name="p_abstract") */
    private $p_abstract;
     /** @Column(type="text", name="p_fulltext", nullable=false) */
    private $p_fulltext;
     /** @Column(type="string", name="p_author", nullable=true) */
    private $p_author;
     /** @Column(type="string", name="p_url",nullable=true) */
    private $p_url;
     /** @Column(type="string", name="p_meta_title",nullable=true) */
    private $p_meta_title;
     /** @Column(type="string", name="p_meta_keywords",nullable=true) */
    private $p_meta_keywords;
     /** @Column(type="string", name="p_meta_description",nullable=true) */
    private $p_meta_description;
      /** @Column(type="string", name="p_status") */
    private $p_status; 
    /**
     * @ManyToOne(targetEntity="User", inversedBy="pages")
     * @JoinColumn(name="p_u_id", referencedColumnName="u_id")
    */
    private $user;
    /**
     * @OneToMany(targetEntity="App\Entity\Page\Media", mappedBy="pages")
     * @var \Doctrine\Common\Collections\Collection
     */
    protected $pageMedia;
      /**
     * @OneToMany(targetEntity="App\Entity\Page\Basket", mappedBy="baskets")
     * @var \Doctrine\Common\Collections\Collection
     */
    protected $pageBasket;

    public function __construct()
    {
        $this->pageMedia = new App\Entity\Page\Media();
        $this->medias = new ArrayCollection();
    }
    public function __get($property)
    {
        return $this->property;
    }
    public function __set($property,$value)
    {
        $this->$property = $value;
    }
    public function setUser(user $user)
    {
        $this->user = $user;
    }
    public function setMedia(media $media)
    {
        $this->pageMedia->setPageAndMedia($this,$media);
    }
    /**
     * Set Page Values
     * @var array $values
     */
    public function setPageProperties(array $values)
    {
        $this->p_updated_at =  new \DateTime("now");
        $this->p_title = $values['p_title'];
        $this->p_abstract = $values['p_abstract'];
        $this->p_meta_title = $values['p_meta_title'];
        $this->p_meta_keywords = $values['p_meta_keywords'];
        $this->p_meta_description = $values['p_meta_description'];
        $this->p_url = $values['p_url'];
        $this->p_fulltext = $values['p_abstract'];
        $this->p_author = '';
        $this->p_status = 1;

    }
}

?>


<?php
namespace App\Entity\Page;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @Entity
 * @Table(name="page_basket")

 */
class Basket
{
    /**
     * @Id @Column(type="integer", name="pb_id")
     * @GeneratedValue
     */
    private $pb_id;
    /**
     * @ManyToOne(targetEntity="App\Entity\Page")
     * @JoinColumn(name="pb_p_id", referencedColumnName="p_id")
    */
    private $pages;
    /**
     * @ManyToOne(targetEntity="App\Entity\Basket",inversedBy="pageBasket")
     * @JoinColumn(name="pb_b_id", referencedColumnName="b_id")
    */
    private $baskets;
    public function __construct()
    {
        $this->baskets = new ArrayCollection();
        $this->pages = new ArrayCollection();
    }
    public function __get($property)
    {
        return $this->property;
    }
    public function __set($property,$value)
    {
        $this->$property = $value;
    }
    /**
     * 
     */
    public function setPageAnBasket(page $page,basket $basket) 
    {
        $this->pages[] = $page;
        $this->baskets[] =  $basket;
    }

}

?>

And method in repository:

<?php
namespace App\Repository;

use Doctrine\ORM\EntityRepository;

class Page extends EntityRepository
{
    /**
     * Find pages by basket Id
     * @var int $basketId
     * @return array $pages[]
     */
    public function findPagesByBasket($basketId)
    {

        $dql = $this->_em->createQueryBuilder();
        $dql->select('u')
            ->from('App\Entity\Page', 'p')
            ->leftJoin('p.App\Entity\Page\Basket','pb_b_id = p_id')


  ->andWhere('pb_b_id = :basketId')
        ->setParameter('basketId', $basketId);

    return $dql->getQuery()->getArrayResult();    
}

}

But when I ry to run dql all I'm getting:

string '[Semantical Error] line 0, col 67 near 'pb_b_id = p_id': Error: Class App\Entity\Page has no association named App\Entity\Page\Basket'

What I'm doing wrong because I don't want to use many to many relation because I wanna have additional fields in join table.

Answer 1

I needed a good excuse to make myself a simple test case so here it is.

namespace Entity;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @Entity()
 * @Table(name="page")
 */
class Page
{
    /**
     * @Id @Column(type="integer", name="p_id")
     * @GeneratedValue
     */
    private $id;

    /**
     * @OneToMany(targetEntity="Entity\PageBasket", mappedBy="page")
     */
    protected $pageBaskets;

    public function __construct()
    {
        $this->pageBaskets = new ArrayCollection();
    }
    public function getId()          { return $this->id; }
    public function getPageBaskets() { return $this->pageBaskets; }
}

namespace Entity;
use Doctrine\Common\Collections\ArrayCollection;

/**
 * @Entity
 * @Table(name="page_basket")
 */
class PageBasket
{
    /**
     * @Id @Column(type="integer", name="pb_id")
     * @GeneratedValue
     */
    private $id;

    /**
     * @ManyToOne(targetEntity="Entity\Page")
     * @JoinColumn(name="page_id", referencedColumnName="p_id")
    */
    private $page;

    public function setPage($page)
    {
        $this->page = $page;
    }
    public function getId() { return $this->id; }

}

And a working test query

protected function testQuery()
{
    $basketId = 1;

    $em = $this->getEntityManager();
    $qb = $em->createQueryBuilder();

    $qb->addSelect('page');
    $qb->addSelect('pageBasket');

    $qb->from('\Entity\Page','page');
    $qb->leftJoin('page.pageBaskets','pageBasket');

    $qb->andWhere($qb->expr()->in('pageBasket.id',$basketId));

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

    $page = $results[0];
    $pageBaskets = $page->getPageBaskets();
    $pageBasket = $pageBaskets[0];

    echo 'Result Count ' . count($results) . "\n";
    echo 'Page ID ' . $page->getId() . "\n";
    echo 'Page Basket ID ' . $pageBasket->getId() . "\n";
    echo $query->getSQL() . "\n";
}

The generated sql look like:

SELECT p0_.p_id AS p_id0, p1_.pb_id AS pb_id1, p1_.page_id AS page_id2 
FROM page p0_ 
LEFT JOIN page_basket p1_ ON p0_.p_id = p1_.page_id 
WHERE p1_.pb_id IN (1)