CODEWITHSUNDEEP
phppostgresqlsymfonydoctrine-orm
user6578454
user6578454

Reputation:

Create a group by query with inner join using Doctrine Query Builder

I struggle to create a query to get information from two tables. I want to count rows and group it by category and type.

My normal PostgresSQL query looks like this:

SELECT c.name AS category_name, i.type, count(i) AS number_of_items
FROM item i
INNER JOIN category c
ON i.category_id = c.id
GROUP BY c.name, i.type

How do I build this query using Doctrine Query Builder?

What I have tried:

$qb->select(['c.name', 'i.type', 'count(i)'])
    ->from('AppBundle:Item', 'i')
    ->innerJoin('i', 'AppBundle:Category', 'c', 'i.category_id = c.id')
    ->groupBy('c.name')
    ->groupBy('i.type');

return $qb->getQuery()->getResult();

But this give me an error:

[Semantical Error] line 0, col 80 near 'i AppBundle:Category': Error: Class 'i' is not defined.

I'm trying to follow the principle in the documentation found here: http://doctrine-orm.readthedocs.io/projects/doctrine-dbal/en/latest/reference/query-builder.html#join-clauses

Any help would be appreciated.

Upvotes: 2

Views: 7526

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Using doctrine and without defining any mapping between your related entities is not a good practice you should start from Association Mapping

Once you have defined mappings in your entities you can simply join your main entity using the properties which holds the reference of linked entities, doctrine will automatically detects the join criteria you don't need to specify in query builder, sample mapping for your entities can be defined as 

class Item
{

    /**
     * @ORM\ManyToOne(targetEntity="Category", inversedBy="items")
     * @ORM\JoinColumn(name="category_id", referencedColumnName="id")
     */
    private $category;
}

-

use Doctrine\Common\Collections\ArrayCollection;
class Category
{
    /**
     * @ORM\OneToMany(targetEntity="Item", mappedBy="category")
     */
    private $items;

    public function __construct()
    {
        $this->items = new ArrayCollection();
    }
}

And then your query builder will look like

$qb->select('c.name', 'i.type', 'count(i)'])
    ->from('AppBundle:Category', 'c')
    ->innerJoin('c.items','i')
    ->groupBy('c.name')
    ->addGroupBy('i.type');

Relationship Mapping Metadata

Or if you still don't want to have mappings and use the other approach you have to use WITH clause in doctrine 

$qb->select(['c.name', 'i.type', 'count(i)'])
    ->from('AppBundle:Item', 'i')
    ->innerJoin('AppBundle:Category', 'c', 'WITH' , 'i.category_id = c.id')
    ->groupBy('c.name')
    ->addGroupBy('i.type');

Upvotes: 2

Dhia Eddine Farah
Dhia Eddine Farah

Reputation: 260

Tried to use full expression for your entity Like this : 

 ->from('AppBundle\Entity\Item','i')

Upvotes: 0

Alessandro Minoccheri
Alessandro Minoccheri

Reputation: 35973

you don't need to use array inside select try this:

$qb->select('c.name', 'i.type', 'count(i)')
    ->from('AppBundle:Item', 'i')
    ->innerJoin('i', 'AppBundle:Category', 'c', 'i.category_id = c.id')
    ->groupBy('c.name')
    ->groupBy('i.type');

return $qb->getQuery()->getResult();

Upvotes: 0

Related Questions