How do you Query Junction tables using knexjs?

Question

Im setting up a basic blog API with (NodeJS, ExpressJS, KnexJS, and Postgres). And each blog has many categories. So when I query a blog I also get the categories for each blog. At the same time each category can have many blogs. I may want to query blog post by category. So I set up 3 tables:

Blog TABLE () COLUMNS {id, title, description, slug}

Category TABLE COLUMNS {id,name,slug}

CatBlog TABLE (Junction table) COLUMNS {cat_id, blog_id}

When I query blog post I also want to have an array of categories.

{
      "id": 14,
      "title": "Title for a recipe coming soon",
      "description":"",
      "slug": "title-for-a-recipe-coming-soon",
      "categories": [
        {
          "id": 6,
          "name": "Business",
          "slug": "business"
        },
        {
          "id": 7,
          "name": "Marketing",
          "slug": "marketing"
        },
        {
          "id": 8,
          "name": "Chef",
          "slug": "chef"
        }
      ]
    }

Im using KnexJS to handle my query but I don't see anything on how to deal with a junction table. How would I add categories from the Category table using the CatBlog table to get them?

Answer 1

Not sure sure this code works, but here is my approach

I suggest you to use underscore/lodash and some control-flow module like asyncjs.

function loadBlogPosts(callback){
    // fetching 10 blogpost
    knex("blog").select("*").limit(10)
      .then(function(posts){
         callback(null,posts)
      })
      .catch(function(err){
        callback(err);
      })
}

function loadCategories(posts,callback){
    // making an array from post ids
    var postIds = _.pluck(posts,'id');
    // join with blog_category table
    knex("category").join("blog_category","category.id","blog_category.cat_id").whereIn({"blog_category.blog_id":postIds}).select("category.*","blog_category.blog_is as blog_id")
        .then(function(categories){
            callback(null,posts,categories)
        })
        .catch(function(err){
            callback(err);
        })
}


async.waterfall([loadBlogPosts,loadCategories],function(err,posts,categories){
    var post_list = _.map(posts,function(post){
        post.categories = _.find(categories,{blog_id: post.id});
    })
})

In this example, you only send request 2 query (1 for posts, 1 for categories) which is much better in performance.