how can I create this mysql query in doctrine is it possible?

Question

how can I create this mysql query in doctrine is it possible?

Navigation

#1 by (2 votes)

0

I have this query that I did in mysql

SELECT 'formulario' as formulario, 'persona' as persona, 'uds' as uds, GROUP_CONCAT(pg.enunciado SEPARATOR ';') as valores_respuestas 
    FROM preguntas_de_formularios as pdf 
    INNER JOIN preguntas as pg on pg.id_pregunta = pdf.pregunta WHERE pdf.formulario = 5 
UNION
SELECT q.nombre as formulario, q.nombres as persona , q.nombre_uds as uds, GROUP_CONCAT(IFNULL(q.valor_respuesta, 0 ) SEPARATOR ';') as valores_respuestas
 from (
   SELECT f.nombre, pf.posicion, pr.id_pregunta, r.id_respuesta , df.respuesta, p.id_persona,CONCAT(p.nombres, ' ', p.apellidos ) as nombres, u.nombre_uds, pr.enunciado as pregunta, 
    IF(da.valor IS NOT NULL, da.valor, GROUP_CONCAT(DISTINCT CONCAT(o.enunciado, IF(df.otro IS NULL,'', CONCAT(': ',df.otro))) SEPARATOR '\n\n')) as valor_respuesta
   from 
    preguntas_de_formularios as pf
    inner join preguntas as pr on pr.id_pregunta = pf.pregunta
    LEFT join formularios as f on f.id_formulario = pf.formulario
    inner join soluciones as s on s.formulario = f.id_formulario 
    LEFT join persona as p on p.id_persona = s.persona
    LEFT join uds as u on u.id_uds = s.uds
    LEFT join respuestas as r on r.solucion = s.id_solucion and r.pregunta = pf.pregunta and (r.categoria = pf.categoria  OR (r.categoria IS NULL and pf.categoria IS NULL))
    LEFT join detalle_respuesta_fija as df on  df.respuesta = r.id_respuesta
    LEFT join opciones as o on o.id_opcion = df.opcion
    LEFT join detalle_respuesta_abierta as da on da.respuesta = r.id_respuesta
    where f.id_formulario = 5
    group by  df.respuesta, pr.id_pregunta, p.id_persona
    order by nombres, pf.posicion, r.id_respuesta) as q
  group by q.id_persona

I would like to create it in doctrine, it is possible with the group concat and everything or I must create it with

$this->em->createNativeQuery($sql, $rsm);

mysql symfony3 doctrine2

asked by ANDRES FERNANDO MARTINEZ VALEN 26.02.2018 в 06:24

source

1 answer

How to get part of a group of data that is repeated only once and the rest is repeated in MySQL? How to debug a try / catch block (android-xamarin development)

score 2 · Answer 1

Depending on where you are doing the query; Assuming you are in the repository file, the syntax would be as follows:

$conn = $this->getEntityManager()->getConnection();
$sql = '//Tu query';


$stmt = $conn->prepare($sql);

// $ stmt-> boundValue ('expression', $ value) this function must be used for binoring external values

$stmt->execute();

$stmt->fetchAll();  //Retorna el resultado