Symfony2 doctrine create query

Question

I have 3 Entities: Blog:

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;
//...
}

Product

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;
//...
}

And i want to make query which will give me blogs which are corresponded to product_tags which are corresponded to products:

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

How to do that? I need to use this to knp_paginator :(

user3257442 · Accepted Answer · 2014-07-08 12:48:28Z

I have found solution. The problem was that I had created one directional relation in blog - product_tag and product - product_tag entities. Based on the information here: http://doctrine-orm.readthedocs.org/en/latest/reference/association-mapping.html I have changed definition of ProductTag entity as fallow:

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

/**
 * @ORM\ManyToMany(targetEntity="Product", mappedBy="product_tags", cascade={"persist"})
 */
private $products;
//...

And it allowed me to create in BlogRepository following method:

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();
}

And it is generating following 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

Which in 1 SQL is getting all blogs I wanted.

Thank you Ajeet for help it helped me a lot :)

Ajeet Varma · Accepted Answer · 2014-07-05 10:05:37Z

0

i do't understand query too much , but you can also use magic method to fetch blogs as given below ..

         $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,
         );

and in your twig file

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

        {{ knp_pagination_render(pagination) }}

Anyway if your query is correct you can also do as below :

          $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,
               );

and in twig file will be remained as i described above ..

edited Jul 5, 2014 at 10:05

answered Jul 5, 2014 at 10:00

Ajeet Varma

7264 silver badges21 bronze badges

2 Comments

user3257442 Over a year ago

Unfortunately the SQL is not working. When I am using the SQL way I am getting the "No such class error" corresponding to the product. So I changed the product table in SQL to BundleName:Product and that's fine however I don't have entity for joining table (product_product_tag and blog_product_tag). Always I can loop through objects however it is not efective way. What if i will have thousend of product_tags and thousend of blogs? Then iterationg through all of them will take ages! It should be possible to get results just from the SQL server.

user3257442 Over a year ago

You mean by iterating through objects and geting objects from objects? This is very not effective. Each getter will probably send another SQL query to database. So there ill be 1 SQL for getting product then 1 SQL to getproduct_tags and for each proguct tag 1 SQL to get blogs related to this tag. For 10 product_tags it is 1+1+10 = 12 queries! And it is possible to get in 1 query.

Collectives™ on Stack Overflow

Symfony2 doctrine create query

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related