How do I populate a foreign key field with data instead of just foreign key id on select query?

I have 2 tables with class and subjects.

• class(id,name,student_count)
• subjects(id,name,class_id)

I want to populate class_id with name,student_count, and id on the select query. How do I do this using SQL or Knex?

I tried using join, but I get only one field not all fields in a row.

The expected result would be:

subject{
   name: "data"
   id: "data"
   class: {
     id: "data"
     name: "data"
     student_count: "data"
  }
}

By the way, answer in Knex or plain SQL would help!

Answer

You have to either use an inner join or a left join.

In SQL it would look something like this:

SELECT
      s.id            AS subject_id
    , s.name          AS subject_name
    , c.id            AS class_id
    , c.name          AS class_name
    , c.student_count AS class_student_count
FROM subjects s
INNER JOIN class ON s.class_id = c.id
WHERE s.id = 1

In Knex it looks something like this:

getOne: () => knex
    .select(   's.id AS subject_id'
             , 's.name AS subject_name'
             , 'c.id AS class_id'
             , 'c.name AS class_name'
             , 'c.student_count AS class_student_count'
           )
    .from('subjects AS s')
    .innerJoin('class AS c', 's.class_id', 'c.id')
    .where('s.id': 1)

You will get an object with the variable names after AS as field names. To get it into the format you want you have to create a mapper function:

function mapToWantedFormat(subject) {
  return {
    name: subject.subject_name,
    id: subject.subject_id
    class: {
      id: subject.class_id,
      name: subject.class_name,
      student_count: subject.class_student_count
    }
  };
};

Which is, for example, called used like this:

module.exports.getOne = async (req, res, next) => {
  try {
    const subject = await getOne();
    if (subject) {
      res.status(200).json(mapToWantedFormat(subject));
    } else {
      res.status(404).json();
    }
  } catch (error) {
    next(error);
  }
};