CODEWITHSUNDEEP
node.jssqliteexpressknex.js
Jayaram
Jayaram

Reputation: 6606

Knex Js returns a different output from raw SQL

I have two tables. One is a categories table and the other is a projects table with a foreign key to categories ( one category can have multiple projects)

I'm trying to expose all projects from the below code

router.route('/projects')
  .get(function(req,res){
    //knex('projects').select(['projects.id','projects.name','projects.category_id','categories.name']).innerJoin('categories','projects.category_id','categories.id')
    knex.from('projects').innerJoin('categories','projects.category_id','categories.id')
    .then(function(collection){

      console.log(collection);
      res.json({
        error:false,
        data: collection
      })
    })
    .catch(function(err){
      res.json({
        error:true,
        data:{
          message:err.message
        }
      })
    })
  })

both the knex statements return pretty much a truncated output similar to below

{
  "error": false,
  "data": [
    {
      "id": 1,
      "name": "Music",
      "category_id": 1,
      "created_at": 1458126413858
    },
    {
      "id": 2,
      "name": "Science",
      "category_id": 2,
      "created_at": 1458126413858
    }
  ]
}

However , the raw SQL seems to be the right 

select * from "projects" inner join "categories" on "projects"."category_id" = "categories"."id"

or

select "projects"."id", "projects"."name", "projects"."category_id", "categories"."name" from "projects" inner join "categories" on "projects"."category_id" = "categories"."id"

running the above statements separately on sqlite seem to return the entire table result

Where am i going wrong with the knex statement?

for reference, this is the schema

projects table

var table = (table) => {
  table.increments().primary();
  table.string('name');
  table.integer('category_id').references('categories.id');
  table.timestamp('created_at').defaultTo(Date.now());
}

categories table

var table = (table) => {
  table.increments().primary();
  table.string('name');
  table.timestamp('created_at').defaultTo(Date.now());
}

Upvotes: 1

Views: 1996

Answers (1)

Jayaram
Jayaram

Reputation: 6606

Solved it by using aliases for columnNames like below

knex('projects').select(['projects.id as projectId','projects.name as projectName','projects.category_id','categories.name as categoryName']).innerJoin('categories','projects.category_id','categories.id')

Upvotes: 1

Related Questions