I am doing a Node.js REST API tutorial. I use Express, Knex.js (0.19.0) and PostgreSQL.

I have two database tables, users:

// user_migration.js



exports.up = function(knex) {

  return knex.schema.createTable('users', function(table) {

    table

      .increments('id')

      .primary()

      .unsigned();

    table.string('firstName');

    table

      .string('lastName')

      .index()

      .notNullable();

    table

      .string('email')

      .unique()

      .index()

      .notNullable();

    table.string('password').notNullable();

    table.string('role').defaultTo('STAFF');

    table.boolean('isActive').defaultTo(false);

    table.timestamp('createdAt').defaultTo(knex.fn.now());

    table.timestamp('updatedAt').defaultTo(knex.fn.now());

  });

};

and posts:

// post_migration.js



exports.up = function(knex) {

  return knex.schema.createTable('posts', function(table) {

    table

      .increments('id')

      .primary()

      .unsigned();

    table.string('title').notNullable();

    table.text('body');

    table.boolean('published').defaultTo(false);

    table

      .integer('author')

      .unsigned()

      .index()

      .references('id')

      .inTable('users')

      .onDelete('SET NULL');

    table.timestamp('createdAt').defaultTo(knex.fn.now());

    table.timestamp('updatedAt').defaultTo(knex.fn.now());

  });

};

I want to make a GET request at http://localhost:8081/users/1/posts to show user.id 1's posts.

// user_get.js



  async getPosts(req, res, next) {

    try {

      // Check if user exists

      const user = await this.knex('users')

        .where('id', req.params.id)

        .first();



      // If not, return NOT FOUND status code

      if (!user) return next(createError(404, 'User not found'));



      /**

       * Right here, I am not sure if I am doing it right.

       */

      // Get from database and filter

      const result = await this.knex('users')

        .join('posts', 'posts.author', '=', 'users.id')

        .select()

        .then(posts => posts.filter(post => post.author === user.id));



      // Return OK status code and related posts

      res.status(200).send(result);

    } catch (error) {

      // Return BAD REQUEST status code

      return next(createError(400, error);

    }

  }

What I expected is an array of posts belong to user 1:

[

    {

        id: 1,

        title: Number One Post,

        body: This is the one body,

        published: true,

        author: 1,

        createdAt: 2019-07-23T06:14:04.281Z,

        updatedAt: 2019-07-23T06:14:04.281Z

    },

    {

        id: 2,

        title: Number Two Post,

        body: This is two body,

        published: false,

        author: 1,

        createdAt: 2019-07-23T06:14:04.281Z,

        updatedAt: 2019-07-23T06:14:04.281Z

    }

]

But I got like this:

[

    {

        id: 1,

        firstName: Some,

        lastName: One,

        email: [email protected],

        password: password789,

        role: STAFF,

        isActive: false,

        createdAt: 2019-07-23T06:14:04.281Z,

        updatedAt: 2019-07-23T06:14:04.281Z,

        title: Number One Post,

        body: This is the one body,

        published: true,

        author: 1

    },

    {

        id: 2,

        firstName: Some,

        lastName: One,

        email: [email protected],

        password: password789,

        role: STAFF,

        isActive: false,

        createdAt: 2019-07-23T09:21:34.285Z,

        updatedAt: 2019-07-23T09:21:34.285Z,

        title: Number Two Post,

        body: This is two body,

        published: false,

        author: 1

    }

]

How should I query user 1's posts without mashing up with user info?
Please help.

P.S. Also updatedAt in Knex.js does not work correctly. It does not update the timestamp when I update. How do I fix this?