Hi,
I try to make a SQL request to retrieve events and images from those events. All is ok exept the pictures format, I don't know why but my request escape all quotes and stuff.
My SQL request
SELECT e.`idevents`, e.`place`, e.`description`, e.`is_online`, e.`idcategories`, e.`idusers`, e.`title`,e. `latitude`, e.`longitude`, ST_Distance_Sphere(
POINT(latitude, longitude),
POINT(?, ?)
) AS distance,
json_array((
SELECT GROUP_CONCAT(
json_object('idpictures',p.idpictures, 'url', p.url)
)
FROM pictures p
LEFT JOIN events_pictures ep
ON ep.idpictures = p.idpictures
WHERE ep.idevents = e.idevents
)) as pictures
FROM events e";
The result
[{
"idevents": 1,
"place": "10 route des Sablons, 44830 Bouaye",
"description": "Apparition de la dame du lac",
"is_online": 1,
"idcategories": 1,
"idusers": 1,
"title": "Apparition dame du lac",
"latitude": "47.134063",
"longitude": "-1.664312",
"distance": 95309.26587540086,
"pictures": "[\"{\\\"url\\\": \\\"https:\/\/picsum.photos\/300\/500\\\", \\\"idpictures\\\": 1},{\\\"url\\\": \\\"https:\/\/picsum.photos\/500\/300\\\", \\\"idpictures\\\": 2}\"]"
}, {
"idevents": 2,
"place": "16 Route du Bois Cholet, 44860 Saint-Aignan-Grandlieu, France",
"description": "Apparition trop cool",
"is_online": 1,
"idcategories": 2,
"idusers": 1,
"title": "Mon \u00e9v\u00e8nement super cool",
"latitude": "47.146919",
"longitude": "-1.640709",
"distance": 97945.99220240247,
"pictures": "[null]"
}]
Here you can see a lots of "\" added.
What I want
[{
"idevents": 1,
"place": "10 route des Sablons, 44830 Bouaye",
"description": "Apparition de la dame du lac",
"is_online": 1,
"idcategories": 1,
"idusers": 1,
"title": "Apparition dame du lac",
"latitude": "47.134063",
"longitude": "-1.664312",
"distance": 95309.26587540086,
"pictures": [{
"url": "https://picsum.photos/300/500",
"idpictures": 1
},
{
"url": "https://picsum.photos/500/300",
"idpictures": 2
}
]
},
{
"idevents": 2,
"place": "16 Route du Bois Cholet, 44860 Saint-Aignan-Grandlieu, France",
"description": "Apparition trop cool",
"is_online": 1,
"idcategories": 2,
"idusers": 1,
"title": "Mon \u00e9v\u00e8nement super cool",
"latitude": "47.146919",
"longitude": "-1.640709",
"distance": 97945.99220240247,
"pictures": [null]
}
]
Thanks
A.D
What I have tried:
I looked on internetto see if functions
JSON_OBJECT()
or
GROUP_CONCAT()
escaped characters but it seems not...