Doctrine 2 で InnerJoin にサブクエリを用いる方法

Question

###Doctrine 2 で下記のSQL文を実現したい。

商品ごとに毎日の売上数が保存されているテーブル

CREATE TABLE IF NOT EXISTS `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `value` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE(`item_id`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

任意の日付で参照した時その日付以前の累計値も参照したい。

SET @today = '任意の日付';
SELECT
    a.*, b.`total`
FROM
    `sales` a
INNER JOIN
    (
        SELECT
            `item_id`, SUM(`value`) AS `total`
        FROM
            `sales`
        WHERE
            `date` <= @today
        GROUP BY
            `item_id`
    ) b
ON
    b.`item_id` = a.`item_id`
WHERE
    a.`date` = @today;

###試したこと

/**
 * 
 * @param Doctrine\ORM\EntityManager $em
 * @param \DateTime $date
 */

$qb1 = $em->createQueryBuilder();
$qb2 = clone $qb1;

$qb1->from('Application\Entity\Sales', 'a')
    ->select('a')
    ->andWhere($qb1->expr()->eq('a.date', ':date'));

$qb2->from('Application\Entity\Sales', 'b')
    ->select('b.itemId, SUM(b.value) AS total')
    ->andWhere($qb1->expr()->lte('b.date', ':date'))
    ->addGroupBy('b.itemId');

$qb1->addSelect('b.total')
    ->innerJoin(sprintf('(%s)', $qb2->getDQL()), 'b', Doctrine\ORM\Query\Expr\Join::WITH, $qb1->expr()->eq('b.itemId', 'a.itemId'))
    ->setParameter('date', $date);

$results = $qb1->getQuery()->getResult();

$qb1, $qb2 を innerJoin する前ソースコードに setParameter を設定して結果を確認すると問題はないので Entity には問題ないです。
エラーメッセージが出力しているクエリ文は理想に近い形なのですが
innerJoin にサブクエリは使えないのでしょうか？
または他のやり方があるのでしょうか？

###発生している問題・エラーメッセージ

Additional information:
Doctrine\ORM\Query\QueryException

File:
/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:63

Message:
[Semantical Error] line 0, col 69 near '(SELECT b.itemId,': Error: Class '(' is not defined.


Previous exceptions:
Doctrine\ORM\Query\QueryException

File:
/vendor/doctrine/orm/lib/Doctrine/ORM/Query/QueryException.php:41

Message:
SELECT a, b.total FROM Application\Entity\Sales a INNER JOIN (SELECT b.itemId, SUM(b.value) AS total FROM Application\Entity\Sales b WHERE b.date <= :date GROUP BY b.itemId) b WITH b.itemId = a.itemId WHERE a.date = :date

###解決した記述

$qb1 = $em->createQueryBuilder();
$qb2 = clone $qb1;

$qb2->from('Application\Entity\Sales', 'b')
    ->select('SUM(b.value)')
    ->andWhere($qb2->expr()->lte('b.date', ':date'))
    ->andWhere($qb2->expr()->eq('b.itemId', 'a.itemId'));

$qb1->from('Application\Entity\Sales', 'a')
    ->select('a')
    ->addSelect(sprintf('(%s) AS total', $qb2->getDQL()))
    ->andWhere($qb1->expr()->eq('a.date', ':date'))
    ->setParameter('date', $date);

$results = $qb1->getQuery()->getResult();

Answer 1

以下で行けると思います。

SELECT 
    *
    , (SELECT SUM(value)
      FROM sales b
      WHERE date <= @today 
            AND b.item_id = a.item_id) as total
FROM sales a where date = @today;

Comments

[退会済みユーザー]

解答ありがとうございます。
早速Doctrineに書き直してやってみます。

[退会済みユーザー]

ありがとうございました。
無事にDoctrineで書き直すことができました。

Answer 2

例示がないので正確に何をしたいのかわかりませんが

SET @today = '任意の日付';
select *,(select sum(value*(date<@today)) from sales) as sum
from sales where date=@today;

追記

set @today:='任意の日付';
select t1.id,t1.item_id,t1.date,t1.value,t2.shokei,t3.gokei
from sales t1
inner join (select item_id,sum(value) as shokei  from sales where date<@today group by item_id) as t2 on t1.item_id=t2.item_id
inner join (select sum(value) as gokei  from sales where date<@today) as t3 on 1
where date=@today

Comments

[退会済みユーザー]

解答ありがとうございます。
任意の日付で参照した時その日付以前の累計値も参照したいのです。
このSQL文でDoctrineに書き直してやってみます。

[yambejp]

>任意の日付で参照した時その日付以前の累計値も参照したい

それはよくわかります。
今回とくにわからなかったのは
・item_id毎にしなくてもいいのか？
・当日のデータは集計しなくてもいいのか？
ということです。
単に指定日以前のデータをまるっと合計していいなら提示したソースでいけると思います

[退会済みユーザー]

item_id毎検索した日付も集計したいと思っております。

[yambejp]

一応item_idごとの小計と全体の合計を追記しておきました

[退会済みユーザー]

ありがとうございます。
ですがいま問題としているのはSQL文ではなくて
Doctrine 2 で InnerJoin にサブクエリを用いる方法なので
join 区の中にサブクエリを用いない方法のSQL文なのならば書き換えられると思います。

[yambejp]

Doctrine 2への知識が不足しているので明確な解決策は提示できませんが
>join 区の中にサブクエリを用いない方法
を前提とするなら、もし複数SQLを連続して発行できるなら
サブクエリの部分をテンポラリテーブルに流し込んで
結合する手もあるかと思います