Symfony2原则创建查询 - Symfony2 doctrine create query

我有3个实体:博客:

class Blog {
    /**
     * @ORM'Id
     * @ORM'Column(type="integer")
     * @ORM'GeneratedValue(strategy="AUTO")
     */
    protected $id;
//...
    /**
     * @ORM'ManyToMany(targetEntity="ProductTag", inversedBy="blogs", cascade={"persist"})
     * @ORM'JoinTable(name="blog_product_tag",
     * joinColumns={@ORM'JoinColumn(name="blog_id", referencedColumnName="id")},
     * inverseJoinColumns={@ORM'JoinColumn(name="product_tag_id", referencedColumnName="id")}
     * )
     */
    protected $product_tags;
//...
}

ProductTag

class ProductTag {
    /**
     * @ORM'Id
     * @ORM'Column(type="integer")
     * @ORM'GeneratedValue(strategy="AUTO")
     */
    protected $id;
//...
    /**
     * @ORM'ManyToMany(targetEntity="Blog", mappedBy="blogs", cascade={"persist"})
     */
    private $blogs;
//...
}

产品

class Product {
    /**
     * @ORM'Id
     * @ORM'Column(type="integer")
     * @ORM'GeneratedValue(strategy="AUTO")
     */
    protected $id;
//...

    /**
     * @ORM'ManyToMany(targetEntity="ProductTag", inversedBy="products", cascade={"persist"})
     * @ORM'JoinTable(name="product_product_tag",
     * joinColumns={@ORM'JoinColumn(name="product_id", referencedColumnName="id")},
     * inverseJoinColumns={@ORM'JoinColumn(name="product_tag_id", referencedColumnName="id")}
     * )
     */
    protected $product_tags;
//...
}

我想做一个查询它会给我博客对应于product_tags对应于产品

SELECT b . * 
FROM  `product` p
JOIN product_product_tag ppt ON ( p.id = ppt.product_id ) 
JOIN product_tag pt ON ( ppt.product_tag_id = pt.id ) 
JOIN blog_product_tag bpt ON ( pt.id = bpt.product_tag_id ) 
JOIN blog b ON ( bpt.blog_id = b.id ) 
WHERE p.id =5

怎么做?我需要将它用于knp_paginator:(

)

我找到解决办法了。问题是我在blog - product_tag和product - product_tag实体中创建了一个方向关系。根据这里的信息:http://doctrine-orm.readthedocs.org/en/latest/reference/association-mapping.html我已经更改了ProductTag实体的定义如下:

//...
class ProductTag {
//...
/**
 * @ORM'ManyToMany(targetEntity="Blog", mappedBy="product_tags", cascade={"persist"})
 */
private $blogs;
/**
 * @ORM'ManyToMany(targetEntity="Product", mappedBy="product_tags", cascade={"persist"})
 */
private $products;
//...

它允许我在blogrerepository中创建以下方法:

public function getRelatedBlogsByProduct(Product $product) {
    return $this->createQueryBuilder('b')
            ->select('b')
            ->join('b.product_tags', 'pt')
            ->join('pt.products', 'p')
            ->where('p.id = :product_id')
            ->setParameter('product_id', $product->getId())
            ->getQuery()
            ->getResult();
}

生成如下SQL:

SELECT b0_.id AS id0, b0_.title AS title1, b0_.author AS author2, b0_.blog AS blog3, b0_.image AS image4, b0_.created AS created5, b0_.updated AS updated6, b0_.published AS published7, b0_.is_homepage_slider AS is_homepage_slider8, b0_.is_category_slider AS is_category_slider9, b0_.is_breaking_news AS is_breaking_news10, b0_.category_id AS category_id11 FROM blog b0_ INNER JOIN blog_product_tag b2_ ON b0_.id = b2_.blog_id INNER JOIN product_tag p1_ ON p1_.id = b2_.product_tag_id INNER JOIN product_product_tag p4_ ON p1_.id = p4_.product_tag_id INNER JOIN Product p3_ ON p3_.id = p4_.product_id WHERE p3_.id = 2

在1 SQL中得到我想要的所有博客。

谢谢你Ajeet的帮助，它帮助了我很多:)

我不太懂查询，但你也可以使用魔法方法来获取博客，如下所示。

         $blogs = array();
         $product = $em->getRepository('ProductBundle:Product')->find($id);
         $productTags = $product->getProductTags();
         foreach($productTags as $productTag){
            $blogs = $productTag->getBlogs();
         }
        $paginator  = $this->get('knp_paginator');
        $pagination = $paginator->paginate(
        $blogs,
        $this->get('request')->query->get('page', 1)/*page number*/,
        10/*limit per page*/
         );
         return array(
                'pagination' => $pagination,
         );

和在你的twig文件

       {% for blog in pagination %}
          {% if loop.last %}
             {{ blog.text }}
          {% endif %}
       {% endfor %}
        {{ knp_pagination_render(pagination) }}

无论如何，如果你的查询是正确的，你也可以这样做:

          $dql = SELECT b . * 
                 FROM  `product` p
                 JOIN product_product_tag ppt ON ( p.id = ppt.product_id ) 
                 JOIN product_tag pt ON ( ppt.product_tag_id = pt.id ) 
                 JOIN blog_product_tag bpt ON ( pt.id = bpt.product_tag_id ) 
                 JOIN blog b ON ( bpt.blog_id = b.id ) 
                 WHERE p.id =$id;
          $query = $em->createQuery($dql);
          $paginator  = $this->get('knp_paginator');
          $pagination = $paginator->paginate(
                $query,
                $this->get('request')->query->get('page', 1)/*page number*/,
                10/*limit per page*/
               );
          return array(
                   'pagination' => $pagination,
               );