Bonjour,

Voila je rencontre un petit problème avec mon code.

J'essaie de filtrer mes utilisateurs par date début et date fin, mode de paiement zone de livraison(nom=stocke le nom de la zone de livraison) et nb Commandes, j'ai créé un form Type, une action une fonction dans le repository

class FilterClientsType extends AbstractType
{
    public function buildForm(FormBuilderInterface $builder, array $options)
    {
        $builder
            ->add('dateDebut', DateType::class, array(
                'label'=>false,
                'widget' => 'single_text',
                'html5' => true,

            ))
            ->add('dateFin', DateType::class, array(
                'label'=>false,
                'widget' => 'single_text',
                'html5' => true,

            ))

            ->add('Filter', SubmitType::class, array(
                'attr' => array(
                    'class' => 'btn btn-primary',
                )
            ));
    }/**
 * {@inheritdoc}
 */
    public function configureOptions(OptionsResolver $resolver)
    {
        $resolver->setDefaults(array(
            'data_class' => null
        ));
    }

}

class UtilisateurController extends Controller
{

    public function listAction(Request $request)
    {
        $form = $this->createForm(FilterType::class);
        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $dateDebut = $form->get('dateDebut')->getData();
            $dateFin = $form->get('dateFin')->getData();
            $modePaiement = $form->get('modePaiement')->getData();
            $nbCommande = $form->get('nbCommande')->getData();
            $zoneLivraison = $form->get('zoneLivraison')->getData();
        } else {
            $dateDebut = null;
            $dateFin = null;
            $modePaiement=null;
            $nbCommande=null;
            $zoneLivraison=null;
        }

        $userRepos = $this
            ->getDoctrine()
            ->getManager()
            ->getRepository('CeUtilisateurBundle:User');
            $listUsers = $userRepos->filterUser($dateDebut, $dateFin, $modePaiement, $nbCommande, $zoneLivraison);

             return $this->render('CeUtilisateurBundle:Utilisateur:list.html.twig', array(
            'form' => $form->createView(),
            'listUsers' => $listUsers,
        ));
    }

    class UserRepository extends \Doctrine\ORM\EntityRepository
{
    public function filterUser($dateDebut, $dateFin, $modePaiement, $nom, $nbCommandes)
    {
        $qb = $this
            ->createQueryBuilder('u')
            ->leftJoin('u.commandes', 'cmd')
            ->leftJoin('u.zoneLivraison', 'zone')
            ->addSelect('cmd')
            ->addSelect('zone')
            ->addSelect('COUNT(cmd.id) as nbCommandes');

        if (!is_null($dateDebut) && !is_null($dateFin) && $dateFin >= $dateDebut && !is_null($modePaiement) && !is_null($nom) && !is_null($nbCommandes)) {
            $qb->where('cmd.dateCommande BETWEEN :dateDebut AND :dateFin')
           ->andWhere ('cmd.modePaiement = :modePaiement')
           ->andWhere ('zone.nom = :nom')
            ->setParameter('dateDebut', $dateDebut)
            ->setParameter('dateFin', $dateFin)
            ->setParameter('modePaiement', $modePaiement)
            ->setParameter('nom', $nom)
            ->setParameter('nbCommandes', $nbCommandes);
        }
            return $qb
                ->getQuery()
                ->getResult();

    }

    {% extends "adminbase.html.twig" %}
{% block title %}Clients - {{ parent() }} {% endblock %}
{% block stylesheets %}
    <link rel="stylesheet" href="{{ asset('assets/vendor/datatables.net-bs/css/dataTables.bootstrap.min.css') }}">
    <link rel="stylesheet" href="{{ asset('assets/vendor/datatables.net/css/dataTables.responsive.css') }}">
    <link rel="stylesheet" href="{{ asset('assets/css/fosuser.css') }}">
    <link rel="stylesheet" href="{{ asset('assets/bundles/CeUtilisateur/css/utilisateur_list.css') }}">
{% endblock %}
{% block javascripts %}
    <script src="{{ asset('assets/vendor/datatables.net/js/jquery.dataTables.min.js') }}"></script>
    <script src="{{ asset('assets/vendor/datatables.net-bs/js/dataTables.bootstrap.min.js') }}"></script>
    <script src="{{ asset('assets/vendor/datatables.net/js/dataTables.responsive.js') }}"></script>
    <script src="{{ asset('assets/bundles/CeUtilisateur/js/utilisateur_list.js') }}"></script>
{% endblock %}
{% block content_title %}Liste des clients{% endblock %}
{% block content %}
    {% import "@CeCommande/Commande/cmd_modal-macro.html.twig" as detailsModal %}
    <div class="box box-warning">
        <form method="post" action="#" id="lbad_form_sms">
            <div class="box-header">
                <div class="pull-left">
                    <p class="lbad_etat-datatable label lbad_month_filter">Filtrer les Clients :
                    <div class="container">
                        <div class='col-md-6'>
                            <div class="form-group">
                                {{ form(form) }}
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <div class="box-body">
                <div class="table-responsive">

                    <table id="tableuser"
                           class="table table-bordered table-striped table-hover dt-responsive display nowrap"
                           cellspacing="0">
                        <thead>
                        <tr>
                            <th>
                                Client
                            </th>
                            <th>Téléphone</th>
                            <th>Nombre de commandes</th>
                            <th>Date de la dernière commande</th>
                            <th class="nosort">Action</th>
                        </tr>
                        </thead>
                        <tbody>
                        {% if listUsers is not empty %}
                            {% for usr in listUsers %}
                                <tr>
                                    <td>
                                        <div class="checkbox">
                                            <label>
                                                <input name="usr_{{ usr.id }}" type="checkbox"
                                                       value="{{ usr.telephone }}">
                                                <span> {{ usr.username }}</span>
                                            </label>
                                        </div>
                                    </td>
                                    <td>{{ usr.telephone.count }}</td>
                                    <td>{{ usr.commandes.count }}</td>
                                    {% if usr.commandes is empty %}
                                        <td>-</td>
                                    {% else %}
                                        <td>{{ usr.getLastCommande.dateCommande|date('d/m/Y à H:i') }}</td>
                                    {% endif %}
                                    <td>
                                        <a class="btn btn-xs bg-navy" name="Voir détails"
                                           data-toggle="modal" data-target="#{{ usr.id }}modal">
                                            <i class="fa fa-eye"></i>
                                        </a>
                                    </td>
                                </tr>
                            {% endfor %}
                        {% endif %}
                        </tbody>
                        <tfoot>
                        <tr>
                            <th>Client</th>
                            <th>Téléphone</th>
                            <th>Nombre de commandes</th>
                            <th>Date de la dernière commande</th>
                            <th class="nosort">Action</th>
                        </tr>
                        </tfoot>
                    </table>
                </div>
            </div>
            <div class="box-footer">
                <button type="submit" class="btn btn-primary lbad_btngc">Envoyer un sms</button>
            </div>
        </form>
    </div>
    <div>
        {% if listUsers is not empty %}
            {% for usr in listUsers %}
                <div class="lbad_cmddetails_modal lbad_cmd_list_modal">
                    {{ detailsModal.modalUser(usr) }}
                </div>
            {% endfor %}
        {% endif %}
    </div>
{% endblock %}

Je cherche à faire fonctionner ma requête car pour l'instant c'est ça qui bloque le problème est dans le nbCommandes qui n'existent pas dans la bdd donc je dois le calculer avec le compte de nombre de commandes de chaque utilisateur
Merci pour l'aide
Bonne journée

l'erreur est:

An exception occurred while executing 'SELECT COUNT(c0_.id) AS sclr0, f1.username AS username1, f1.username_canonical AS username_canonical2, f1.email AS email3, f1.email_canonical AS email_canonical4, f1.enabled AS enabled5, f1.salt AS salt6, f1.password AS password7, f1.last_login AS last_login8, f1.confirmation_token AS confirmation_token9, f1.password_requested_at AS password_requested_at10, f1.roles AS roles11, f1.id AS id12, f1.nom AS nom13, f1.prenom AS prenom14, f1.adresse_liv AS adresse_liv15, f1.ville_liv AS ville_liv16, f1.telephone AS telephone17, c0.id AS id18, c0.panier AS panier19, c0.dateCommande AS dateCommande20, c0.typeCommande AS typeCommande21, c0.adresseLiv AS adresseLiv22, c0.villeLiv AS villeLiv23, c0.modePaiement AS modePaiement24, c0.etat AS etat25, c0.infoComplementaire AS infoComplementaire26, z2.id AS id27, z2.nom AS nom28, z2.codePostal AS codePostal29, z2.frais AS frais30, z2.minimumlivr AS minimumlivr31, f1.zone_livraison_id AS zone_livraison_id32, c0.user_id AS user_id33, c0.zone_livraison_id AS zone_livraison_id_34 FROM fosuser f1 LEFT JOIN commande c0 ON f1.id = c0_.user_id LEFT JOIN zonelivraison z2 ON f1_.zone_livraisonid = z2.id':

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'tacosspeyp72.f1_.username'; this is incompatible with sql_mode=only_full_group_by

1 réponse