CODEWITHSUNDEEP
node.jsknex.js
artsmc
artsmc

Reputation: 133

How do you Query Junction tables using knexjs?

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?

Upvotes: 0

Views: 828

Answers (2)

Anakin
Anakin

Reputation: 3120

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.

Upvotes: 1

artsmc
artsmc

Reputation: 133

I'm not sure if this is the best answer but I was able to do the query

//Run the select on the table I am attempting to query to get all the data I need.
knex.select('*').from('Blog')
	.then(function(data){
  //Once I have the data run a then with the array so that I can iterate over each item.
		data.map(function(item){
          //For each Item I am querying the CatBlog table and then if the item.id matches the blog_id I will grab that cat_id. Once I am inside the this query I also run a join to add the categories table on each cat_id that I find. and then lastly I can also select the columns I would like to display.
			return knex('CatBlog').whereIn('blog_id',item.id).join('Categories', 'cat_id','=','Categories.id').select('id','name','slug')
            //I take that data and pass a new key for each item called categories. Each key has a value of the query.
			.then(function(data){
				item['categories'] = data;
			});
          
		});
    	res.json(data);
    })

So the first thing I do is query the table I want to display so if I want blogs with many categories I query the blogs. if I want categories with many blogs I query the Categories.

Next I query the junction table for the relationship. Since I am in the blog table I compare against the blog_id to find the category id's that I need. Lastly I run a join on the id and select the columns I would like to display.

Upvotes: 0

Related Questions