从逗号分隔的Knex.js获取值

Question

我有两个SQlite3表task和tags

task是我的主表，tags存储标记名

我将逗号分隔的值存储在task中。

现在我想通过使用一个Tag names来获得knex.js

表task

id   task   tags
---------------------
1     abc     1,2,3
2     xyz     3,1
3     apple   2  

表tags

id   tag   
------------
1     cold
2     hot     
3     normal

现在我要输出如下

产出：

id   task   tags
---------------------
1     abc     cold,hot,normal
2     xyz     normal,cold
3     apple   hot

我知道我必须使用联接，但不确定如何在knex.js中实际使用它。请帮帮我。

Answer 1

问题的一部分是你的数据库没有被正确的标准化。与其有两个表任务和制表符，而且在列‘tag’中包含多个标记in，您应该有三个表；‘Task’、‘tag’和'joining‘table’task _tag‘。他们会存储以下数据..。

任务

id   task 
----------
1     abc 
2     xyz 
3     apple 

标签

id   tag   
------------
1     cold
2     hot     
3     normal

task_tags

task_id tag_id
1         1
1         2
1         3
2         1
2         3
3         2

现在，您可以拥有任意数量的标记(无论是否有任何任务使用它们)和任意多个任务(不管它们是否使用任何标记)，并通过task_tags表将任务与其标记关联起来。

然后，要获得想要的结果，可以使用select

SELECT 
   tasks.id,
   tasks.task,
   GROUP_CONCAT(tags.tag) -- this gives you the csv line eg cold,hot,normal
from tasks
left join task_tags
ON tasks.id = task_tags.task_id
left join tags
on tags.id = task_tags.tag_id
GROUP BY task.id, tags.id

关于aggfunc.html关于GROUP_CONCAT的解释，请参阅

Answer 2

您的任务表应该重新设计为每一行保存一个标记，而不是单行中的多个标记：

id          task        tag       
----------  ----------  ----------
1           abc         1         
1           abc         2         
1           abc         3         
2           xyz         3
2           xyz         1         
3           apple       2  

那就很简单：

SELECT task.id, task.task, group_concat(tags.tag, ',') AS tags
FROM task
JOIN tags ON task.tag = tags.id
GROUP BY task.id, task.task
ORDER BY task.id;

这给

id          task        tags           
----------  ----------  ---------------
1           abc         cold,hot,normal
2           xyz         normal,cold    
3           apple       hot        

遵循关系数据库规则的设计使生活变得更加简单(上面的内容可以进一步规范化；请参阅另一个答案)；虽然有些数据库确实支持数组类型，但sqlite不是其中之一。但是，如果您坚持保留当前的设计，那么就会有一个涉及JSON1扩展并将您的CSV数字列表转换为JSON数组的丑陋的攻击：

SELECT task.id, task.task, group_concat(tags.tag, ',') AS tags
FROM task
JOIN json_each('[' || task.tags || ']') AS j
JOIN tags ON tags.id = j.value
GROUP BY task.id, task.task
ORDER BY task.id;