knex js query many to many

Question

i'm having trouble with node & knex.js

I'm trying to build a mini blog, with posts & adding functionality to add multiple tags to post

I have a POST model with following properties:

id SERIAL PRIMARY KEY NOT NULL,
name TEXT,

Second I have Tags model that is used for storing tags:

id SERIAL PRIMARY KEY NOT NULL,
 name TEXT

And I have many to many table: Post Tags that references post & tags:

 id SERIAL PRIMARY KEY NOT NULL,
 post_id INTEGER NOT NULL REFERENCES posts ON DELETE CASCADE,
 tag_id INTEGER NOT NULL REFERENCES tags ON DELETE CASCADE

I have managed to insert tags, and create post with tags, But when I want to fetch Post data with Tags attached to that post I'm having a trouble

Here is a problem:

 const data = await knex.select('posts.name as postName', 'tags.name as tagName'
            .from('posts')
            .leftJoin('post_tags', 'posts.id', 'post_tags.post_id')
            .leftJoin('tags', 'tags.id', 'post_tags.tag_id')
            .where('posts.id', id)

Following query returns this result:

[
  {
    postName: 'Post 1',
    tagName: 'Youtube',
  },
  {
    postName: 'Post 1',
    tagName: 'Funny',
  }
]

But I want the result to be formated & returned like this:

  {
    postName: 'Post 1',
    tagName: ['Youtube', 'Funny'],
  }

Is that even possible with query or do I have to manually format data ?

Answer 1

One way of doing this is to use some kind of aggregate function. If you're using PostgreSQL:

const data = await knex.select('posts.name as postName', knex.raw('ARRAY_AGG (tags.name) tags'))
    .from('posts')
    .innerJoin('post_tags', 'posts.id', 'post_tags.post_id')
    .innerJoin('tags', 'tags.id', 'post_tags.tag_id')
    .where('posts.id', id)
    .groupBy("postName")
    .orderBy("postName")
    .first();

->

{ postName: 'post1', tags: [ 'tag1', 'tag2', 'tag3' ] }

For MySQL:

const data = await knex.select('posts.name as postName', knex.raw('GROUP_CONCAT (tags.name) as tags'))
    .from('posts')
    .innerJoin('post_tags', 'posts.id', 'post_tags.post_id')
    .innerJoin('tags', 'tags.id', 'post_tags.tag_id')
    .where('posts.id', id)
    .groupBy("postName")
    .orderBy("postName")
    .first()
    .then(res => Object.assign(res, { tags: res.tags.split(',')}))

There are no arrays in MySQL, and GROUP_CONCAT will just concat all tags into a string, so we need to split them manually.

->

RowDataPacket { postName: 'post1', tags: [ 'tag1', 'tag2', 'tag3' ] }