I have the following code in php it works great
if ($result = $mysqli->query("SELECT t.*, GROUP_CONCAT(c.category) categories, GROUP_CONCAT(k.keyword) keywords FROM dataclayTemplates t LEFT JOIN dataclayCategoryLink cl JOIN dataclayCategories c ON cl.categoryId=c.id ON t.id=cl.templateId LEFT JOIN dataclayKeywordLink kl JOIN dataclayKeywords k ON kl.keywordId=k.id ON t.id=kl.templateId GROUP BY t.id"))
{
while($row = $result->fetch_array(MYSQLI_ASSOC)) {
if($row["categories"] == null) {
$row["categoryArray"] = [];
} else {
$row["categoryArray"] = array_unique(explode(",",$row["categories"]));
}
unset($row["categories"]);
if($row["keywords"] == null) {
$row["keywordArray"] = [];
} else {
$row["keywordArray"] = array_unique(explode(",",$row["keywords"]));
}
unset($row["keywords"]);
$templateArray[] = $row;
}
}
$result->close();
I want to do the same thing in nodejs using mysql (add a categoryArray array on the end of each result and fill it with the categories that are in each results categories) I have this node js code and a pull the data just fine I just need to build the categoryArray and keywordArray.
import express from 'express';
import connection from '../index.js'
const router = express.Router();
router.get('/allTemplates', function (req, res) {
let queryString="SELECT t.*, GROUP_CONCAT(c.category) categories, GROUP_CONCAT(k.keyword) keywords FROM dataclayTemplates t LEFT JOIN dataclayCategoryLink cl JOIN dataclayCategories c ON cl.categoryId=c.id ON t.id=cl.templateId LEFT JOIN dataclayKeywordLink kl JOIN dataclayKeywords k ON kl.keywordId=k.id ON t.id=kl.templateId GROUP BY t.id";
let query = connection.query(queryString, (error, result) => {
if(error) {throw error;}
res.json(result);
})
});
module.exports = router;
below is my attempt to do it in nodejs however it does not work.
import express from 'express';
import connection from '../index.js'
const router = express.Router();
router.get('/allTemplates', function (req, res) {
let queryString="SELECT t.*, GROUP_CONCAT(c.category) categories, GROUP_CONCAT(k.keyword) keywords FROM dataclayTemplates t LEFT JOIN dataclayCategoryLink cl JOIN dataclayCategories c ON cl.categoryId=c.id ON t.id=cl.templateId LEFT JOIN dataclayKeywordLink kl JOIN dataclayKeywords k ON kl.keywordId=k.id ON t.id=kl.templateId GROUP BY t.id";
let query = connection.query(queryString, (error, result) => {
if(error) {throw error;}
let categoryArray=[];
result.forEach(function(template){
template.concat(categoryArray);
template.categories.forEach(function(category) {
if(template.categories!=null){
template.categoryArray.push(category);
}
});
});
})
});
module.exports = router;
Any help would be great thanks.