How to query relations with Knex.js?

Question

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": "some@one.com",
        "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": "some@one.com",
        "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?

Answer 1

Just drop your join on users in the second query

         const result = await this.knex('posts')
        .where('posts.author', user.id)
        .select()


      // Return OK status code and related posts
      res.status(200).send(result);