我是教义新手,想使用它在我的数据库上进行查询
SELECT DATE_FORMAT(DATE(created_at), '%m-%Y') AS 'date',
COUNT(DISTINCT(id)) AS 'value'
FROM users
GROUP BY YEAR(DATE(created_at)), MONTH(DATE(created_at))
我阅读了一些文档,但仍然有几个问题
如何选择多个元素;为选定的元素提供别名,执行左连接。WEEK(( MONTH(( 和 YEAR(( 函数可用吗?DATE_FORMAT(来自MySQL(是否可用?
谢谢
我相信你应该使用查询生成器(http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/query-builder.html(。
考虑到日期,您可以使用symfony日期时间对象并格式化它们。对于左连接,可以使用左连接方法 (http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/query-builder.html(
<?php
// $qb instanceof QueryBuilder
$qb->select('u as aliasName') //see for alias
->from('User', 'u')
->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id') //see for left join
->where('u.id = ?1')
->addwhere(u.date < :date)
->orderBy('u.name', 'ASC')
->setParameter('date', $symfonyDateTime->format('Y-m-d')); //see for date
除了 QueryBuilder,您还可以尝试 DQL(http://symfony.com/doc/current/doctrine.html 和 http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html(:
$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
'SELECT p
FROM AppBundle:Product p
WHERE p.price > :price
ORDER BY p.price ASC'
)
->setParameter('price', 19.99);
$products = $query->getResult();
您可以安装原则扩展以使用以下函数:
DATE, MINUTE, HOUR, DAY, WEEK
等等。
例:
学说配置
doctrine:
dbal:
....
orm:
auto_generate_proxy_classes: "%kernel.debug%"
entity_managers:
default:
dql:
datetime_functions:
DATE_FORMAT: DoctrineExtensionsQueryMysqlDATE_FORMAT
然后,您可以像这样轻松添加别名:
DATE_FORMAT(s.createdAt,'%Y-%m-01') as date