get and display the right datas with route parameters filtering and a doctrine query

Question

To begin I have this SQl request:

SELECT  pc.id, pc.nom_point_comptage, e.id, e.nom_ensemble, p.id, p.nom_parc
FROM points_comptage pc , ensembles e , parcs_immobilier p
WHERE pc.ensembles_id = e.id
AND e.parcs_immobilier_id = p.id

This query allows me to get the points comptage which are in the ensembles owned to a specific parc

So in my symfony controller I made a DQL with doctrine. This is the code for the controller:

/**
 *
 * @Route("/gestioncompteurs/pointscomptageByparcs", name="dataTablePointsComptageParc")
 * @Method("get")
 */
public function pointsComptageByParcAction($nomParc)
{
  $em=$this->getDoctrine()->getManager();

  $query = $em->createQuery('SELECT  p.nomParc, e.nomEnsemble, pc.invariantPointComptage /*and more like my SQL request...*/
                             FROM MySpaceMyBundle:ParcsImmobilier p, MySpaceMyBundle:Ensembles e, MySpaceMyBundle:PointsComptage pc
                               WHERE pc.ensembles = e.id
                               AND e.parcsImmobilier = p.id');

  $pointComptage = $query->getResult();

  return $this->render('MySpaceMyBundle:PointsComptage:dataTablePointsComptage.html.twig', array(
        'pointComptage' => $pointComptage));
}

But in my twig, first I need to filter by parc, so I create a select tag where there are all my parcs like this:

<select class="form-control input" id="filterByParc" name="filterByParc">
  <option value="" disabled selected>sélectionnez un parc</option>
    {% for parcs in parc %}
      <option value="{{ path('dataTablePointsComptageParc', {'nomParc': parcs.nomParc}) }}">{{ parcs.nomParc }}</option>
    {% endfor %}
</select>

As you can see, the value for the selected options in my select tag is a path(url) for a view. Because I need to return in javascript a datatables filtering by the parc I choose (route parameter).

All works well, that is to say, my controller and my javascript display correctly my datatable, but with no data.

On phpMyAdmin, my request works well, but not with Doctrine in my controller.

To explain exactly what I am trying to do and what I need:

first: I select a parc name in my select tag in my view; second: my choice returns me a datatable filtering by the name of my parc I choose, so the datas in my table have to be all the points comptage in my ensembles owned to the parc I choose first in the select tag.

Someone know where my problem occured?

Answer 1

I finally found the logic of filtering with a DQL and a route parameter.

See my controller code here:

/**
 *
 * @Route("/gestioncompteurs/pointscomptageByparcs", name="dataTablePointsComptageParc")
 * @Method("get")
 */
public function pointsComptageByParcAction($nomParc)
{
  $em=$this->getDoctrine()->getManager();

  $query = $em->createQuery("SELECT  p.nomParc, e.nomEnsemble, pc.invariantPointComptage /*and more like my SQL request...*/
                             FROM MySpaceMyBundle:ParcsImmobilier p, MySpaceMyBundle:Ensembles e, MySpaceMyBundle:PointsComptage pc
                               WHERE pc.ensembles = e.id
                               AND e.parcsImmobilier = p.id
                               AND p.nomParc = '$nomParc'");

  $pointComptage = $query->getResult();

  return $this->render('MySpaceMyBundle:PointsComptage:dataTablePointsComptage.html.twig', array(
        'pointComptage' => $pointComptage, 'parc' => $parc));
}

I need to pass the $nomParc in my DQl to recover the right datas in order to match with the data I pass to the route parameter.

Answer 2

I think this makes perfect sense.

Doctrine operates on entities via DQL (not SQL). At very least you could try putting getArrayResult() instead of getResult().

In fact, you should probably use DBAL Connection:

$em = .... // Your EntityManager
$sql = " .... ";
$pointComptage = $em->getConnection()->fetchAssoc($sql);

return $this->render('MySpaceMyBundle:PointsComptage:dataTablePointsComptage.html.twig', array(
    'pointComptage' => $pointComptage));

Hope this helps...