Inner join in symfony query builder

Question

I have problem to write this sql query in QueryBuilder:

SELECT SUM(TableA.SUM) AS TOTAL, TableB.name 
    FROM TableA INNER JOIN TableB ON TableA.category_id = TableB.id GROUP BY TableB.name

it's work when i called it into mysql query. And i get something like:

Category | TOTAL category a | 40 category b | 67 category c | 5

My Expenditure Entity class:

/**
 * Expenditure
 *
 * @ORM\Table(name="accountant_expenditure")
 * @ORM\Entity(repositoryClass="accountant\ExpenditureBundle\Repository\ExpenditureRepository")
 */
class Expenditure
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="accountant\UserBundle\Entity\User")
     * @ORM\JoinColumn(name="create_by_user_id", referencedColumnName="id", onDelete="CASCADE")
     */
    private $createBy;

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

    /**
     * @var integer
     *
     * @ORM\ManyToOne(targetEntity="accountant\ExpenditureBundle\Entity\ExpenditureCategories")
     * @ORM\JoinColumn(name="category_id", referencedColumnName="id", onDelete="CASCADE")
     */
    private $category;

    /**
     * @var float
     *
     * @ORM\Column(name="sum", type="float")
     */
    private $sum;
//...

ExpenditureCategories entity class:

/**
 * ExpenditureCategories
 *
 * @ORM\Table()
 * @ORM\Entity(repositoryClass="accountant\ExpenditureBundle\Repository\ExpenditureCategoriesRepository")
 */
class ExpenditureCategories
{
    /**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

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

I write something like this but it's not correct:

public function findCurrentMonthExpendituresSumByCategory()
    {
        $firstDay = new \DateTime('first day of this month');
        $lastDay = new \DateTime('last day of this month');

        return $this->createQueryBuilder('e')
            ->select('SUM(e.sum) AS TOTAL, c.name')
            ->from('ExpenditureBundle:ExpenditureCategories', 'c')
            ->leftJoin('e.category', 'c')
            ->where('e.createAt BETWEEN :start AND :end')
            ->setParameter('start', $firstDay->format('Y-m-d'))
            ->setParameter('end', $lastDay->format('Y-m-d'))
            ->groupBy('c.name')
            ->getQuery()
            ->getResult();
    }

Maybe it's problem with my annotations in entities? I get:

[Semantical Error] line 0, col 154 near 'c WHERE e.createAt': Error: 'c' is already defined.

Really thanks for help anyway ;)

I would just use a JOIN. (In MySql Inner Join, is a Join) https://stackoverflow.com/questions/565620/difference-between-join-and-inner-join — ArtisticPhoenix, Oct 17 '18 at 19:38
@ArtisticPhoenix I change for join.I think it's more correctly. Thanks. — JuniorWithEverything, Oct 17 '18 at 20:15
@newManInSymfony - use a left join when there may not be data in the second table. Use a right join if there may not be data in the first table, use an inner join (join) when data has to be in both tables. That's pretty much all you have to remember. — ArtisticPhoenix, Oct 17 '18 at 20:23

ArtisticPhoenix · Accepted Answer · 2018-10-17T20:26:00.890

It's right here

->from('ExpenditureBundle:ExpenditureCategories', 'c')
        ->leftJoin('e.category', 'c')

From c left join as c, your using the same alias 2x.

The DB complains that it's already defined:

[Semantical Error] line 0, col 154 near 'c WHERE e.createAt': Error: 'c' is already defined

This should be (I changed leftJoin to join):

  ->from('ExpenditureBundle:ExpenditureCategories', 'e')
     ->join('e.category', 'c')

It's been a minute sense I played with Doctrine, though I am pretty sure the alias is the second argument.

Basically you want to join e.category_id to c.id.

UPDATE

When it comes to Join(inner Join) vs Left Join. The difference is that a Join will only return results if there is a row in both tables (both sides of the join). For a left join there only needs to be data in the first table, and a right join is the opposite.

An example would be Posts and Comments. You may want to use a query to pull the post together with the comments. If you use a Join, you will only get posts that have comments, if you use a left join you will get posts that may not have comments. In the case they don't those fields for the comment table will be NULL.

score 0 · Answer 2 · answered Oct 17 '18 at 19:59

I change as you write and i have to comment ->from to get correctly result.

 public function findCurrentMonthExpendituresSumByCategory()
    {
        $firstDay = new \DateTime('first day of this month');
        $lastDay = new \DateTime('last day of this month');

        return $this->createQueryBuilder('e')
            ->select('SUM(e.sum) AS TOTAL, c.name')
            //->from('ExpenditureBundle:ExpenditureCategories', 'e')
            ->leftJoin('e.category', 'c')
            ->where('e.createAt BETWEEN :start AND :end')
            ->setParameter('start', $firstDay->format('Y-m-d'))
            ->setParameter('end', $lastDay->format('Y-m-d'))
            ->groupBy('c.name')
            ->getQuery()
            ->getResult();
    }

It's work now. Thanks you very much.

Inner join in symfony query builder

2 Answers2