I would like to use some MySQL functions such as DATE_FORMAT in my QueryBuilder.
I saw this post did not understand totally how I should achieve it:
SELECT DISTINCT YEAR Doctrine
class SubmissionManuscriptRepository extends EntityRepository
{
public function findLayoutDoneSubmissions( $fromDate, $endDate, $journals )
{
if( true === is_null($fromDate) )
return null;
$commQB = $this->createQueryBuilder( 'c' )
->join('c.submission_logs', 'k')
->select("DATE_FORMAT(k.log_date,'%Y-%m-%d')")
->addSelect('c.journal_id')
->addSelect('COUNT(c.journal_id) AS numArticles');
$commQB->where("k.hash_key = c.hash_key");
$commQB->andWhere("k.log_date >= '$fromDate'");
$commQB->andWhere("k.log_date <= '$endDate'");
if( $journals != null && is_array($journals) && count($journals)>0 )
$commQB->andWhere("c.journal_id in (" . implode(",", $journals) . ")");
$commQB->andWhere("k.new_status = '20'");
$commQB->orderBy("k.log_date", "ASC");
$commQB->groupBy("c.hash_key");
$commQB->addGroupBy("c.journal_id");
$commQB->addGroupBy("DATE_FORMAT(k.log_date,'%Y-%m-%d')");
return $commQB->getQuery()->getResult();
}
}
Entity SubmissionManuscript
/**
* MDPI\SusyBundle\Entity\SubmissionManuscript
*
* @ORM\Entity(repositoryClass="MDPI\SusyBundle\Repository\SubmissionManuscriptRepository")
* @ORM\Table(name="submission_manuscript")
* @ORM\HasLifecycleCallbacks()
*/
class SubmissionManuscript
{
...
/**
* @ORM\OneToMany(targetEntity="SubmissionManuscriptLog", mappedBy="submission_manuscript")
*/
protected $submission_logs;
...
}
Entity SubmissionManuscriptLog
/**
* MDPI\SusyBundle\Entity\SubmissionManuscriptLog
*
* @ORM\Entity(repositoryClass="MDPI\SusyBundle\Repository\SubmissionManuscriptLogRepository")
* @ORM\Table(name="submission_manuscript_log")
* @ORM\HasLifecycleCallbacks()
*/
class SubmissionManuscriptLog
{
...
/**
* @ORM\ManyToOne(targetEntity="SubmissionManuscript", inversedBy="submission_logs")
* @ORM\JoinColumn(name="hash_key", referencedColumnName="hash_key")
*/
protected $submission_manuscript;
...
}
any help I would appreciate a lot.
EDIT 1
I have now successfully be able to add the Custom Function DATE_FORMAT.
But now if I try with my Group By I get the following Error:
[Semantical Error] line 0, col 614 near '(k.logdate,'%Y-%m-%d')': Error: Cannot group by undefined identification variable.
Anyone knows about this?
