Cómo definir las funciones YEAR y MONTH con Doctrine usando SQL Server

Hola.

Estoy usando el lexik filter y tengo un problema, ya que usa createQueryBuilder para manipular la consulta consulta.

Los datos los consulto de una base de datos Microsoft SQL Server y no puedo usar Doctrine para crear la consulta porque no están soportadas las funciones como YEAR(), MONTH(), etc.

Instalé una extensión de Doctrine que es para MySQL y usé la forma directa para crear SQL nativo y funciona:

$db = $em->getConnection();
$sql = "",
$stmt = $db->prepare($sql);
$stmt->execute();        
return $stmt->fetchAll()

La otra forma de realizar SQL nativo con ResultSetMappingBuilder no me funciona. Devuelve un objeto, pero al hacer ->getResult() el resultado es vacío. Mi pregunta: ¿se pueden implementar las funciones YEAR(), etc para Microsoft SQL Server?

Gracias.

Respuestas

Buenas, doctrine permite definir funciones propias para las consultas DQL:

Aparte existen algunos tutoriales en español:

Espero te sirvan, Saludos!

@manuel_j555

8 mayo 2015, 0:59

Servirán para cualquier motor de base de datos.

Yo utilizo mysql y ms sql server.

@dariongg

8 mayo 2015, 13:38

Si la funcion que creas existe en esos dos motores de base de datos y funciona exactamente igual, seguro que te servirá.

Generalmente yo la he usado para funciones muy especificas de Mysql, por lo que para otros motores se que no me servirian.

@manuel_j555

8 mayo 2015, 14:07

Defini las funciones YEAR, MONTH y DAY, funcionan pero cuando intento agruparlas no.

Consulta original (Funciona)

SELECT  
    YEAR([d_FechaCreacion]) AS anio  ,
    MONTH([d_FechaCreacion]) AS mes,
    DAY([d_FechaCreacion]) AS dia,
    COUNT([kn_Folio]) AS cant 
FROM [DB].[dbo].[TAVALE_CONSUMO] c
WHERE x_TipoConsumo = 'Almuerzo' 
GROUP BY YEAR([d_FechaCreacion]),
    MONTH([d_FechaCreacion]),
    DAY([d_FechaCreacion])
ORDER BY anio DESC, mes DESC, dia DESC

este una parte de la consulta

$consulta = $em->createQueryBuilder()
                ->select('YEAR(c.dFechacreacion) AS anio', 'MONTH(c.dFechacreacion) AS mes', 'DAY(c.dFechacreacion) AS dia, COUNT(c.id) as cantidad')                
                ->from('TicketBundle:TavaleConsumo', 'c')                                                
                ->where('c.xTipoconsumo = :tipo')                
                ->groupBy('YEAR(c.dFechacreacion)')
                ->addGroupBy('MONTH(c.dFechacreacion)')
                ->addGroupBy('DAY(c.dFechacreacion)')

Pero me arroja este error

[Semantical Error] line 0, col 242 near 'YEAR(c.dFechacreacion),': Error: Cannot group by undefined identification or result variable.

@dariongg

8 mayo 2015, 18:55

¿Si haces el equivalente a ese DQL directamente en SQL en la base de datos, si te funciona?

@manuel_j555

8 mayo 2015, 20:13

La consulta original que esta al comienzo #4, funciona y me devuelve los valores señalados.

pero al tratar de agrupar los Funciones (YEAR, MONTH, DAY y CONVERT) creadas con DQL para que me devuelva esto

Fecha	Cantidad
2015-05-8	115
2015-05-7	110

Me arroja error.

"Error: No se puede agrupar por identificación no definido o resultar variables"

El problema esta estan funciones dql al tratar de agruparlas por esos valores.

Tambien lo tengo hecho Native SQL y funciona, pero no es lo que necesito.

@dariongg

8 mayo 2015, 21:27

Intenta usando los alias en el groupBy a ver:

$consulta = $em->createQueryBuilder()
              ->select('YEAR(c.dFechacreacion) AS anio, 
                       MONTH(c.dFechacreacion) AS mes, 
                       DAY(c.dFechacreacion) AS dia, 
                       COUNT(c.id) as cantidad')
              ->from('TicketBundle:TavaleConsumo', 'c')  
              ->where('c.xTipoconsumo = :tipo')                
              ->groupBy('anio')
              ->addGroupBy('mes')
              ->addGroupBy('dia')

@manuel_j555

8 mayo 2015, 21:49

Lo hice anteriormente y me arroja este error.

DBALException: An exception occurred while executing 'SELECT YEAR(t0_.dFechaCreacion) AS sclr0, MONTH(t0.dFechaCreacion) AS sclr1, DAY(t0.dFechaCreacion) AS sclr2, COUNT(t0.d_FechaCreacion) AS sclr3 FROM TAVALECONSUMO t0 WHERE t0_.xTipoConsumo = ? AND YEAR(t0.dFechaCreacion) = ? AND MONTH(t0.d_FechaCreacion) = ? GROUP BY sclr0, sclr1, sclr2' with params {"1":"Almuerzo","2":2015,"3":4}:

SQLSTATE [42S22, 207]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'sclr0'. SQLSTATE [42S22, 207]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'sclr1'. SQLSTATE [42S22, 207]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'sclr2'.

Tampoco puedo ocupar los alias cuando agrupo en bd y solo los puedo ocupar cuando lo orderno.

@dariongg

8 mayo 2015, 22:17

Me parece entonces que para ese caso especifico, tocará tirar de una consulta nativa.

@manuel_j555

8 mayo 2015, 22:25