Bonjour à tous,
bonne vacances pour les chanceux ! :p

je suis en train de dev une API alimentée par un form vue.js,
qui à terme sera consultable depuis un backoffice et une feuille GoogleSheet via une API REST.

J'utilise Laravel qui est idéal pour gérer tout ça ( routes REST ready & l'authentification OAUTH via Passport )

Je souhaite ajouter des filtres coté API / backoffice,
par exemple entre deux dates, je passe mes critères de recherche via l'url en GET:
http://api.localhost/leads?date_min=2018-05-30&date_max=2018-06-30
No problem ça fonctionne, j'ai un champ created_at sur lequel je passe mes critères dans le controller:

    $query = (new Lead)->newQuery();

    $now = new \DateTimeImmutable();

    $date_min = ($request->has('date_min'))
        ?   $request->input('date_min')
        :   $now->sub(new \DateInterval('P1M'))->format('Y-m-d');

    $date_max = ($request->has('date_max'))
        ?   $request->input('date_max')
        :   $now->add(new \DateInterval('P1D'))->format('Y-m-d');

    $query
        ->where('created_at', '<=', $date_max )
        ->where('created_at', '>=', $date_min );
        //->groupBy(DB::raw("DATE_FORMAT(created_at, '%Y-%m-%d')"));

    $leads = $query->paginate(10);

Là où je galère:
j'ai du JSON stocké en bdd mysql dans un champ de type text:
par ex dans ma table j'ai un champ contact qui contient:
{"email": "jojo@test.fr", "phone": "123456", "lastname": "moustache", "firstname": "jojo"}

Je souhaiterais ajouter un filtre par nom de famille ( ou autre ) mais je n'arrive pas à chercher dans le json,
même en échappant les accents:

$search = ($request->has('lastname'))
        ?   $request->input('lastname')
        :   '';

if(strlen($lastname) > 2 ){
        $query->where('contact', 'LIKE', "%\"lastname\": \"$lastname%\"");
    }

ça marche pô :/ aucun enregistrement trouvé pourtant M. moustache existe bien en bdd..
ç'est pas un problème de majuscule / minuscule
j'ai essayé avec du concat, json_extract / json_contains ( cf stackoverflow ) sans succès, en même temps mon champ est de type TEXT ça me parait normal...

En fait je n'arrive même pas à avoir une requête SQL valide avec HeidiSQL...

je ne sais pas si ç'est possible de faire :
SELECT * FROM leads WHERE contact LIKE '"lastname": "'%moust%"'

Bref, quelqu'un aurait-il rencontré le même problème et surtout trouvé la solution ?

Merci d'avance de vos retours !

3 réponses


Huggy
Réponse acceptée

Pour info, depuis MySQL 5.7.8 il existe le type JSON qui offre plein de possibilités

// avec contact de type JSON
 SELECT contact FROM leads WHERE JSON_EXTRACT(contact, '$.lastname') LIKE "%mous%";

Juste une piste comme ça, en utilisant les REGEXP de MySQL

SELECT contact FROM `leads` WHERE (contact REGEXP '.*"lastname":"[a-zA-Z]*mous[a-zA-Z]*"')= 1

En effet cela fonctionne (en local sous WAMP avec MYSQL 5.7.19 ) avec JSON_EXTRACT mais pas sur mon serveur mutualisé de test ( MYSQL 5.0.12-dev).
Par contre j'ai testé les REGEXP sans succès, et j'ai peur que ce soit galère avec les accents.
Du coup je vais créer des champs ( last_name, first_name,... ) sur les propriétés que je veux filtrer ce sera le plus simple.
Merci de vos réponses !