Symfony4.1 Doctrine ManyToMany Reduce No of Queries

Question

I'm working on a project. Entity are Blog,Category,Tags. Blog and Tags are in ManyToMany Relation. My repository query to fetch data by Tags filter is.

CODE1:

/**
 * @return BlogPost[]
 */
public function getAllActivePostsByTags($value, $order = "DESC", $currentPage = 1, $limit = 10)
{
    $query = $this->createQueryBuilder('p')
        // ->select('p','t')
        ->innerJoin('p.blogTags', 't')
        ->where('t.slug = :val')
        ->setParameter('val', $value)
        ->orderBy('p.id', $order)
        ->getQuery();

    $paginator = $this->paginate($query, $currentPage, $limit);

    return $paginator;
}

This code works fine. All the tags(No of tags in a post)are displayed correctly. But the No of DB Query is 14. Then When I uncomment select as this,

CODE2:

/**
 * @return BlogPost[]
 */
public function getAllActivePostsByTags($value, $order = "DESC", $currentPage = 1, $limit = 10)
{
    $query = $this->createQueryBuilder('p')
        ->select('p','t')
        ->innerJoin('p.blogTags', 't')
        ->where('t.slug = :val')
        ->setParameter('val', $value)
        ->orderBy('p.id', $order)
        ->getQuery();

    $paginator = $this->paginate($query, $currentPage, $limit);

    return $paginator;
}

No of Query is 9. But The Tags per Post is only one(Not displaying all the tags of a single post).

To be clear info:

• It displays entire list of BlogPost.
• But not all Tags of a Post.
• Only one Tag per Post is shown.

Question: Is code1 is correct (No of DB Query = 14) or Do I have to tweak little bit to reduce no of DB Hits. Please guide me on this.

Answer 1

Thanks @Jannes Botis for refresh. But in my case the code itself is wrong. There need a slight change in it.

BlogTags.php

/**
 * @ORM\ManyToMany(targetEntity="BlogPost", mappedBy="blogTags")
 */
private $blogPosts;

BlogPost.php

/**
 * @var Collection|BlogTags[]
 *
 * @ORM\ManyToMany(targetEntity="BlogTags", inversedBy="blogPosts", cascade={"refresh"})
 * @ORM\JoinTable(
 *  name="fz__blog_n_tag",
 *  joinColumns={
 *      @ORM\JoinColumn(name="blog_id", referencedColumnName="id")
 *  },
 *  inverseJoinColumns={
 *      @ORM\JoinColumn(name="tag_id", referencedColumnName="id")
 *  }
 * )
 * @ORM\OrderBy({"name": "ASC"})
 */
private $blogTags;

This created the join_table. Allready I have a join_table. Although This code is for reference to someone.

Controller.php

// This is my old Code
$bp = $em->getRepository('App:BlogPost')->getAllActivePostsByTags($slug, "DESC", $page, self::PAGE_LIMIT);
// This is my New Code
$bp = $em->getRepository('App:BlogTags')->getAllActivePostsByTags($slug, "DESC", $page, self::PAGE_LIMIT);

Repository.php

public function getAllActivePostsByTags($value, $order = "DESC", $currentPage = 1, $limit = 10)
    {
        $query = $this->createQueryBuilder('t')
            ->select('t','p','tx')
            ->innerJoin('t.blogPosts', 'p')
            ->innerJoin('p.blogTags', 'tx')
            ->where('p.isActive = :val1')
            ->andWhere('t.slug = :val2')
            ->setParameter('val1', true)
            ->setParameter('val2', $value)
            ->orderBy('p.id', $order)
            ->getQuery();

        $paginator = $this->paginate($query, $currentPage, $limit);

        return $paginator;
    }

I not changed my old twig file completely. As it throws error at many places. Because now i'm using tags repo instead of blog. So i modified the twig with

{% include 'frontend/page/blog_home.html.twig' with { 'bp':bp|first.blogPosts } %}

Help me on this (twig file): There is only one tag, that's why |first twig filter Clarify me with this twig filter. Do I'm doing right. Give me suggestion to improve on it. I tried bp[0] This trows error.

Finally: By using old code in controller it returns 14 db hits. Now it returns only 8. Even there are more tags in a post (but old one returns more).