Retrieving data from two join tables in a REST API

In the previous article we have seen how to set up anAPI endpoint that retrieves posts associated with an userid.
What we got back from the database, apart from the post content, was an integer that represented the user id.

We know that each user also has aname saved in theusers table. What if we want to display the user name in addition to the post?
To achieve this outcome, we need tojoin two tables (users, andposts) and create a query in Knex that pulls data from both tables.
This is the code we currently have in the User router:

router.get('/:id/posts',async(req,res)=>{const{id}=req.params;try{constposts=awaitdb('posts').where({user_id:id})res.status(200).json(posts)}catch(err){res.status(500).json({message:"can't get posts"})}})

This code creates a route that retrieves the posts with a specific user id.
If we want to grab data from theusers table, though, we need a join statement in our query.

We have seen in an earlier article how to retrieve data from two joined tables with plainSQL.
The syntax would look something like this:

SELECT posts.id, users.username, posts.content FROM posts JOIN usersON users.id = posts.user_id;

When using Knex we write a similar syntax. We can refactor our query to use a join statement like this:

constposts=awaitdb('posts').join('users','users.id','posts.user_id').select('posts.id','users.username','posts.contents').where({user_id:id})

The.join() method above takes the join table name as the first parameter. The next two parameters are the columns that contain the values on which the join is based, that is, the userid and the postuser_id.

We also add a.select() method, that lets us pick the columns we want to display, similar to theSELECT statement inSQL.

If we now try out this endpoint with aREST client, we correctly get back the full username from the users table:

Using aliases

Our code works fine, but we can do better.

When we use joins, since the data is coming from two or more tables, we must specify which table we refer to in our code.
We have to write things like 'posts.id', 'users.username', and 'posts.contents' becauseSQL needs to know without ambiguity which tables and columns we refer to.

That's a lot of typing. If we want to avoid some keystrokes, SQL provides us with a feature calledaliases. An alias is an alternative name we give to an entity. For example, we could refer to theposts table with the alias 'p', which is much shorter to write than 'posts'.

And we could refer to theusers table with the alias 'u'.

This makes it much easier, and faster, to type table names. Here's an example, using aliases:

constposts=awaitdb('posts as p').join('users as u','u.id','p.user_id').select('p.id','u.username','p.contents').where({user_id:id})

We first define aliases in our code with the keyword 'as', like 'posts as p', and 'users as u'. After that, we can use the shortcuts any time we need to type in the full table name.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox bysubscribing to my newsletter.