symfony查询构建器中的内部联接

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

Answer 1

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.