CODEWITHSUNDEEP
sqlsqliteknex.js
Harman
Harman

Reputation: 539

Knex.js Getting values from comma-separated

I have two SQlite3 tables task and tags

task is my master table and tags is storing tag names

I store comma-separated values in task

Now I want to get Tag names with use of a knex.js

table task

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

table tags

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

Now i want output as below

OUTPUT:

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

I know i will have to use joins but not sure how to actually use it in knex.js. Please do help me.

Upvotes: 0

Views: 660

Answers (2)

Shawn
Shawn

Reputation: 52579

Your task table should be redesigned to hold one tag per row, not multiple tags in a single row:

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

Then it's easy:

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;

which gives

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

A design that follows the rules of relational databases makes life much easier (And the above can be normalized further; see the other answer); while some databases do support array types, sqlite is not one of them. If you insist on keeping your current design, though, there's an ugly hack involving the JSON1 extension and turning your CSV list of numbers into a JSON array:

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;

Upvotes: 1

user2834566
user2834566

Reputation: 805

Part of the problem is that your database is not properly normalised. Instead of having two tables task and tabs, with table tasks containing multiple tag IDs in the column 'tags' you should have three tables; 'tasks', 'tags' and the 'joining' table 'task_tags'. They would store the following data...

Tasks

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

Tags

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

Now you can have as many tags as you like (whether or not any tasks use them) and as many tasks as you like (whether or not they use any tags) and you associate a task with it's tags via the task_tags table.

Then to get the result you want you would use the 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

see https://www.sqlite.org/lang_aggfunc.html for explanation of GROUP_CONCAT

Upvotes: 1

Related Questions