时间戳

首页 > 解决方案 > 带有while循环的mysql节点js动态数组

php - 带有while循环的mysql节点js动态数组

问题描述

我在php中有以下代码，效果很好

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();

我想在nodejs中使用mysql做同样的事情（在每个结果的末尾添加一个categoryArray数组，并用每个结果类别中的类别填充它）我有这个节点js代码和一个拉数据就好了我只是需要构建categoryArray和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;

下面是我尝试在 nodejs 中执行此操作，但它不起作用。

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;

任何帮助都会非常感谢。

标签： phpmysqlnode.jsexpress

解决方案

我不得不稍微修改我的代码，但这就是我让它工作的方式。希望它可以帮助别人。快乐编码！

router.get("/", async (req, res, next) => {
  const data = {};
  const sqlStatement =
    "SELECT t.*, GROUP_CONCAT(DISTINCT c.category) categories, GROUP_CONCAT(DISTINCT 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;";

  try {
    data.categories = await database.query("SELECT * FROM dataclayCategories");
    data.keywords = await database.query("SELECT * FROM dataclayKeywords");
    data.templates = await database.query(sqlStatement);
    for (let i = 0; i < data.templates.length; i++) {
      const current = data.templates[i];
      if (current.categories) {
        current.categories = current.categories.split(",");
      }
      if (current.keywords) {
        current.keywords = current.keywords.split(",");
      }
    }

    res.json(data);
  } catch (error) {
    next(error);
  }
});

推荐阅读