In my project with Symfony, I need to make a search form with multicriteria. I have a select for the entity Parc, a select for the entity Typesactivite and an input text for Ensembles.
I have this SQL request:
SELECT distinct e.nom FROM `ensembles` e, `parcsimmobilier` p, `batiments` b, `batiments_typesactivite` bta, `typesactivite` ta WHERE e.parcsimmobilier_id=p.id AND b.ensembles_id=e.id AND bta.batiments_id=b.id AND bta.typesactivite_id = ta.id AND p.nom="Ville de Dijon" AND ta.type="Sport"
This SQL request gives me all ensembles which belong to the Dijon parc where the batiments have Sport for activity.
I tried to transform this SQL request in DQL syntax in my EnsemblesRepository.php but it not gives me the good result, I think I do it wrong:
public function recherche($input) //$input est la valeur entrée dans l'input type text recherche
{
$qb = $this ->createQueryBuilder('e')
->select('e.nom')
->addSelect('e.parcsimmobilier')
->from('MySpaceDatabaseBundle:Parcsimmobilier', 'p')
->from('MySpaceDatabaseBundle:Typesactivite', 'ta')
->from('MySpaceDatabaseBundle:Batiments', 'b')
->where('e.parcsimmobilier = p.id')
->andWhere('b.ensembles=b.id')
->andWhere('ta.batiments=b.id')
->andWhere('e.nom LIKE :input')
->setParameters(array(
'input' => "%".$input."%"));
return $qb->getQuery()->getResult();
}
When I submit the form, I have these errors:
[2/2] QueryException: [Semantical Error] line 0, col 16 near 'parcsimmobilier': Error: Invalid PathExpression. Must be a StateFieldPathExpression.
[1/2] QueryException: SELECT e.nom, e.parcsimmobilier FROM MySpace\DatabaseBundle\Entity\Ensembles e, MySpaceDatabaseBundle:Parcsimmobilier p, MySpaceDatabaseBundle:Typesactivite ta, MySpaceDatabaseBundle:Batiments b WHERE e.parcsimmobilier = p.id AND b.ensembles=b.id AND ta.batiments=b.id AND e.nom LIKE :input
How can I make the DQL request in my repository?
This is the search form I submit for make a search on my entity Ensembles:
class RechercheType extends AbstractType
{
/**
* @param FormBuilderInterface $builder
* @param array $options
*/
public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder
//Parcsimmobilier
->add('parcs:', 'entity', array(
'class' => 'MySpaceDatabaseBundle:Parcsimmobilier',
'property' => 'nom',
'empty_value' => 'Choisir le parc immobilier',
'required' => true))
//Typesactivite
->add('typesactivite:', 'entity', array(
'class' => 'MySpaceDatabaseBundle:Typesactivite',
'property' => 'type',
'empty_value' => 'Choisir le type d\'activite',
'required' => false))
//Ensembles
->add('ensemble', 'text', array(
'attr' => array('placeholder' => 'rechercher'),
'required' => false))
;
UPDATE
There is my code for the controller, if it could helps:
public function rechercheEnsemblesAction() {
$formRecherche = $this->createForm(new RechercheType());
//si la méthode est bien en POST
if ($this->get('request')->getMethod() == 'POST' )
{
$formRecherche->bind($this->get('request'));
$em=$this->getDoctrine()->getManager();
$ensemble = $em ->getRepository('MySpaceDatabaseBundle:Ensembles')
->recherche($formRecherche['ensemble']
->getdata());
}
return $this->render('MySpaceGestionPatrimoinesBundle:Ensembles:rechercheEnsembles.html.twig', array('ensemble' => $ensemble, 'formRecherche' => $formRecherche->createView() ));
}
Let's try to do the query step by step.
In a select tag, I can choose the name of the parcs (required in my form), without enter a value in my input text for the name of ensemble. My SQl request is on phpMyAdmin:
SELECT distinct e.nom FROM `ensembles` e, `parcsimmobilier` p where e.parcsimmobilier_id=p.id
This request gives me all ensembles belonging to the parc with id 1.
In DQl, I try this in my repository EnsemblesRepository.php:
public function recherche() //$input est la valeur entrée dans l'input type text recherche
{
$qb = $this ->createQueryBuilder('e')
->select('e.nom')
->from('MySpaceDatabaseBundle:Parcsimmobilier', 'p')
->where('e.parcsimmobilier = p.id')
->andWhere('e.nom LIKE :input')
->setParameters(array(
'input' => "%".$input."%"));
return $qb->getQuery()->getResult();
}
But it doesn't return me the good result. The result is all Ensemble in my database even if I choose a parc in my select tag create in my buildform.
Someone could help me step by step?
If you need more information, this the relation between my entities. Only two classes/entities have the relation I need.
Ensemnles.php:
/**
* @var \Parcsimmobilier
*
* @ORM\ManyToOne(targetEntity="Parcsimmobilier")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="parcsimmobilier_id", referencedColumnName="id")
* })
*/
private $parcsimmobilier;
Batiments.php:
/**
* @ORM\ManyToOne(targetEntity="MySpace\DatabaseBundle\Entity\Ensembles")
* @ORM\JoinColumn(nullable=false)
*/
private $ensembles;
/**
* @ORM\ManyToMany(targetEntity="MySpace\DatabaseBundle\Entity\Typesactivite")
* @ORM\JoinColumn(nullable=true)
*/
private $typesactivite;
I really can't find the solution, do not hesitate to ask for more informations if you need.
Thank you for your comprehension.
Like I told you here, check the Jquery datatables here, for your project if it's to manage your entities in Symfony.
Then, you can use your own request with Jquery, Ajax and Symfony to have some results for a search form in an html <table>.
This is an example of .js you can use for search in datatable:
$(document).ready(function() {
$('#dataTables').DataTable( {
"dom": '<"toolbar">frtip',
responsive: true,
initComplete: function () {
var api = this.api();
api.columns().indexes().flatten().each( function ( i ) {
var column = api.column( i );
var select = $('<select><option value=""></option></select>')
.appendTo( $(column.footer()).empty() )
.on( 'change', function () {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
column
.search( val ? '^'+val+'$' : '', true, false )
.draw();
});
column.data().unique().sort().each( function ( d, j ) {
select.append( '<option value="'+d+'">'+d+'</option>' )
});
});
}
});
$("div.toolbar").html('<b>Example</b>');
});
And of course the html (don't forget the structure for a table using DataTables, all step are in the doc => For DataTables to be able to enhance an HTML table, the table must be valid, well formatted HTML, with a header (thead) and a body (tbody). An optional footer (tfoot) can also be used.):
<table id="dataTables" class="your class or bootstrap class if you are using bootstrap for example">
<thead>
<tr>
<th>Column 1</th>
<th>Column 2</th>
...number you want...
</tr>
</thead>
<tbody>
<tr>
<td>{{ entity.field }}</td>
<td>{{ entity.field2 }}</td>
...etc..
</tr>
</tbody>
</table>