Symfony2 doctrine join returns too much data

Question

Im having two tables - categories and topics. Below are those structures:

/**
 * ForumCategories
 * @ORM\Table(name="forum_categories")
 * @ORM\Entity
 */

class ForumCategories
{


    /**
     * @ORM\OneToMany(targetEntity="ForumTopics", mappedBy="category")
     */
    protected $topics;

    public function __construct()
    {
        $this->topics = new ArrayCollection();
        $this->children = new ArrayCollection();

    }

    /**
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;
    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255)
     */
    private $name;

    /**
     * @var boolean
     *
     * @ORM\Column(name="top_menu", type="boolean")
     */
    private $topMenu;

    /**
     * @var integer
     *
     * @ORM\Column(name="ord", type="integer", type="decimal", options={"default"="0"})
     */
    private $ord;

    /**
     * @var string
     *
     * @ORM\Column(name="color_class", type="string", length=255, nullable=true)
     */
    private $colorClass;

    /**
     * @ORM\OneToMany(targetEntity="ForumCategories", mappedBy="parent")
     **/
    private $children;

    /**
     * @ORM\ManyToOne(targetEntity="ForumCategories", inversedBy="children")
     * @ORM\JoinColumn(name="parent", referencedColumnName="id")
     **/
    private $parent;


    private $parentMenu;
    private $parentCats;
    /**
     * Set name
     *
     * @param string $name
     * @return ForumCategories
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string 
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Set topMenu
     *
     * @param boolean $topMenu
     * @return ForumCategories
     */
    public function setTopMenu($topMenu)
    {
        $this->topMenu = $topMenu;

        return $this;
    }

    /**
     * Get topMenu
     *
     * @return boolean 
     */
    public function getTopMenu()
    {
        return $this->topMenu;
    }

    /**
     * Set ord
     *
     * @param integer $ord
     * @return ForumCategories
     */
    public function setOrd($ord)
    {
        $this->ord = $ord;

        return $this;
    }

    /**
     * Get ord
     *
     * @return integer 
     */
    public function getOrd()
    {
        return $this->ord;
    }

    /**
     * Set colorClass
     *
     * @param string $colorClass
     * @return ForumCategories
     */
    public function setColorClass($colorClass)
    {
        $this->colorClass = $colorClass;

        return $this;
    }

    /**
     * Get colorClass
     *
     * @return string
     */
    public function getColorClass()
    {
        return $this->colorClass;
    }

    /**
     * Set parent
     *
     * @param string $parent
     * @return ForumCategories
     */
    public function setParent($parent)
    {
        $this->parent = $parent;

        return $this;
    }

    /**
     * Get parent
     *
     * @return string
     */
    public function getParent()
    {
        return $this->parent;
    }



    /**
     * @return ArrayCollection[]
     */
    public function getChildren()
    {
        return $this->children;
    }

    /**
     * @return array[int]
     */
    public function getChildrenId()
    {
        $result = [];
        foreach ($this->getChildren() as $child) {
            $result[] = $child->getId();
        }
        return $result;
    }
    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }


    /**
     * Add topics
     *
     * @param \Test\ForumBundle\Entity\ForumTopics $topics
     * @return ForumCategories
     */
    public function addTopic(\Test\ForumBundle\Entity\ForumTopics $topics)
    {
        $this->topics[] = $topics;

        return $this;
    }

    /**
     * Remove topics
     *
     * @param \Test\ForumBundle\Entity\ForumTopics $topics
     */
    public function removeTopic(\Test\ForumBundle\Entity\ForumTopics $topics)
    {
        $this->topics->removeElement($topics);
    }

    /**
     * Get topics
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getTopics()
    {
        return $this->topics;
    }

And Topics:

/**
 * ForumCategories
 * @ORM\Table(name="forum_topics")
 * @ORM\Entity
 */
class ForumTopics
{


    /**
     * @ORM\ManyToOne(targetEntity="ForumCategories", inversedBy="forum_topics")
     * @ORM\JoinColumn(name="cat_id", referencedColumnName="id")
     */
    private $cat_id;
        /**
        * @ORM\ManyToOne(targetEntity="ForumCategories", inversedBy="forum_topics")
        * @ORM\JoinColumn(name="cat_id", referencedColumnName="id", nullable=true)
*/
    private $category;


    /**
     * @var integer
     *
     * @ORM\Column(name="user_id", type="integer", type="decimal", options={"default"="0"})
     */
    private $userId;
    /**
     * @ORM\ManyToOne(targetEntity="User", inversedBy="forum_topics")
     * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
     */

    protected $userName;


    /**
     * @ORM\OneToMany(targetEntity="ForumPosts", mappedBy="topic_id")
     */
    public $topic_id;
    protected $posts;

    public function __construct()
    {
        //parent::__construct();
        $this->posts = new ArrayCollection();


    }




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

    /**
     * @var integer
     *
     * @ORM\Column(name="cat_id", type="integer", type="decimal", options={"default"="0"})
     */
    private $catId;



    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255)
     */
    private $name;



    /**
     * @var \DateTime
     *
     * * @ORM\Column(name="cdate", type="datetime")
     */
    private $cdate;

    /**
     * @var \DateTime
     *
     * * @ORM\Column(name="lastpost", type="datetime")
     */
    private $lastpost;




    /**
     * Set catId
     *
     * @param integer $catId
     * @return ForumTopics
     */
    public function setCatId($catId)
    {
        $this->catId = $catId;

        return $this;
    }

    /**
     * Get catId
     *
     * @return integer 
     */
    public function getCatId()
    {
        return $this->catId;
    }

    /**
     * Set name
     *
     * @param string $name
     * @return ForumTopics
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string 
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Set userId
     *
     * @param integer $userId
     * @return User
     */
    public function setUserId($userId)
    {
        $this->userId = $userId;

        return $this;
    }

    /**
     * Get userId
     *
     * @return integer 
     */
    public function getUserId()
    {
        return $this->userId;
    }

    /**
     * Set cdate
     *
     * @param \DateTime $cdate
     * @return ForumTopics
     */
    public function setCdate($cdate)
    {
        $this->cdate = $cdate;

        return $this;
    }

    /**
     * Get cdate
     *
     * @return \DateTime 
     */
    public function getCdate()
    {
        return $this->cdate;
    }

    /**
     * Set lastpost
     *
     * @param \DateTime $lastpost
     * @return ForumTopics
     */
    public function setLastpost($lastpost)
    {
        $this->lastpost = $lastpost;

        return $this;
    }

    /**
     * Get lastpost
     *
     * @return \DateTime 
     */
    public function getLastpost()
    {
        return $this->lastpost;
    }

    /**
     * Get id
     *
     * @return integer 
     */
    public function getId()
    {
        return $this->id;
    }



    /**
     * Set category
     *
     * @param \Test\ForumBundle\Entity\ForumCategories $category
     * @return ForumTopics
     */
    public function setCategory(\Test\ForumBundle\Entity\ForumCategories $category = null)
    {
        $this->category = $category;

        return $this;
    }

    /**
     * Get catId
     *
     * @return \Test\ForumBundle\Entity\ForumCategories 
     */
    public function getCategory()
    {
        return $this->catId;
    }


    /**
     * Add posts
     *
     * @param \Test\ForumBundle\Entity\ForumPosts $posts
     * @return ForumTopics
     */
    public function addPost(\Test\ForumBundle\Entity\ForumPosts $posts)
    {
        $this->posts[] = $posts;

        return $this;
    }

    /**
     * Remove posts
     *
     * @param \Test\ForumBundle\Entity\ForumPosts $posts
     */
    public function removePost(\Test\ForumBundle\Entity\ForumPosts $posts)
    {
        $this->posts->removeElement($posts);
    }

    /**
     * Get posts
     *
     * @return \Doctrine\Common\Collections\Collection
     */
    public function getPosts()
    {
        return $this->posts;
    }



    /**
     * Set userName
     *
     * @param \Test\ForumBundle\Entity\User $userName
     * @return ForumTopics
     */
    public function setUserName(\Test\ForumBundle\Entity\User $userName = null)
    {
        $this->userName = $userName;

        return $this;
    }

    /**
     * Get userName
     *
     * @return \Test\ForumBundle\Entity\User 
     */
    public function getUserName()
    {
        return $this->userName;
    }

    /**
     * Add topic_id
     *
     * @param \Test\ForumBundle\Entity\ForumPosts $topicId
     * @return ForumTopics
     */
    public function addTopicId(\Test\ForumBundle\Entity\ForumPosts $topicId)
    {
        $this->topic_id[] = $topicId;

        return $this;
    }

    /**
     * Remove topic_id
     *
     * @param \Test\ForumBundle\Entity\ForumPosts $topicId
     */
    public function removeTopicId(\Test\ForumBundle\Entity\ForumPosts $topicId)
    {
        $this->topic_id->removeElement($topicId);
    }

    /**
     * Get topic_id
     *
     * @return \Doctrine\Common\Collections\Collection 
     */
    public function getTopicId()
    {
        return $this->topic_id;
    }
}

Now i like to take only categories with parameter top_menu = 1. But everything i do is giving me categories with subcategories with all topics assigned to every category and all posts assigned to every topic.. A lot of data. And i want only those categories names. Heres my controller functions:

/**
    * Lists only top categories
    *
    * @Route("/top.{_format}", defaults={"_format"="html"})
    * @Method("GET")
    * @ApiDoc()
    */
    public function listTopAction()
    {
        $em = $this->getDoctrine()->getManager();

        $entities = $em->getRepository('TestForumBundle:ForumCategories')->findBy(
            array('parent' => NULL, 'topMenu' => '1')

        );

        return  $entities;

    }

    /**
     * Lists only top categories WDQL
     *
     * @Route("/tops.{_format}", defaults={"_format"="html"})
     * @Method("GET")
     * @ApiDoc()
     */
    public function listTopDQLAction()
    {


        $em = $this->getDoctrine()->getManager();



        $query = $em->getRepository("TestForumBundle:ForumCategories")->createQueryBuilder('c')
            ->where('c.topMenu = 1')
            ->getQuery();

        $results = $query->getResult();
        return $results;
    }

Is theres a way to get only those data i need not all data from all other tables combined by keys?? Thanks for help!

Answer 1

For what I can understand you probably want something like this

Symfony2 Select one column in doctrine

but I can not really understand what exact data structure you want to get, and to which query in your controller you are referring to.

Is theres a way to get only those data i need not all data from all other tables combined by keys??

also do not understand because these queries will return you object with properties not arrays with keys. Moreover by default doctrine won't fetch all data only single row, and only when you access method will ask db for more data.

Answer 2

What I would do in this case is to create a specific method in the repository of ForumCategories where you would return the names of the categories you want. So you will have to create a file named: ForumCategoriesRepository inside folder Entity/Repository of your bundle.

Then you will have to tell ForumCategories it has a repository at this way:

@ORM\Entity(repositoryClass="{YourNamespacePath}\Entity\Repository\ForumCategoriesRepository")

Now you can create a method like this:

public function getCitiesNames($stateId) {
        $cities = $this->_em->createQueryBuilder()->select('city.name')
                ->from('\MSD\CommonBundle\Entity\City', 'city')
                ->where('city.state = :id')
                ->orderBy('city.name', 'ASC')
                ->setParameter('id', $stateId)
                ->getQuery()
                ->getResult();

        return $cities;
    }

And the call it at your Controller:

$cities = $em->getRepository('MSDCommonBundle:City')
                ->getCitiesNames($stateId);