javascript - How to query many to many relations with Knex.js? - Stack Overflow

admin管理员组

文章数量:1315831

I have this many to many relation in Postgres:

// migrations/2020_create_initial_tables.js

exports.up = function(knex) {
  return knex.schema
    .createTable('students', function(table) {
      table.increments('id').primary()
      table
        .string('email')
        .unique()
        .index()
      table.string('password')
    })
    .createTable('courses', function(table) {
      table.increments('id').primary()
      table.string('title').notNullable()
      table.text('description')
    })
    // A student can enroll many courses
    // A course can have many students
    .createTable('student_courses', function(table) {
      table.increments('id').primary()
      table
        .integer('student_id')
        .references('id')
        .inTable('students')
      table
        .integer('course_id')
        .references('id')
        .inTable('courses')
    })
    .catch(err => {
      console.error(err)
      throw err
    })
  // .finally(() => knex.destroy());
}

exports.down = function(knex) {
  return knex.schema
    .dropTableIfExists('students')
    .dropTableIfExists('courses')
    .dropTableIfExists('student_courses')
    .catch(err => {
      console.error(err)
      throw err
    })
}

I need to show a student's enrolled courses. How do I query (all/an array of) courses by student.id?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Stack: TypeScript, [email protected], Postgres@12-alpine, [email protected]

I have this many to many relation in Postgres:

// migrations/2020_create_initial_tables.js

exports.up = function(knex) {
  return knex.schema
    .createTable('students', function(table) {
      table.increments('id').primary()
      table
        .string('email')
        .unique()
        .index()
      table.string('password')
    })
    .createTable('courses', function(table) {
      table.increments('id').primary()
      table.string('title').notNullable()
      table.text('description')
    })
    // A student can enroll many courses
    // A course can have many students
    .createTable('student_courses', function(table) {
      table.increments('id').primary()
      table
        .integer('student_id')
        .references('id')
        .inTable('students')
      table
        .integer('course_id')
        .references('id')
        .inTable('courses')
    })
    .catch(err => {
      console.error(err)
      throw err
    })
  // .finally(() => knex.destroy());
}

exports.down = function(knex) {
  return knex.schema
    .dropTableIfExists('students')
    .dropTableIfExists('courses')
    .dropTableIfExists('student_courses')
    .catch(err => {
      console.error(err)
      throw err
    })
}

I need to show a student's enrolled courses. How do I query (all/an array of) courses by student.id?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Stack: TypeScript, [email protected], Postgres@12-alpine, [email protected]

• javascript
• sql
• node.js
• postgresql
• knex.js

Share Improve this question Follow asked Apr 8, 2020 at 3:25 SwixSwix 2,1231111 gold badges3838 silver badges5757 bronze badges

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 8

const coursesOfSingleStudent = await knex('courses').whereIn('id',
   knex('student_courses').select('course_id').where('student_id', studentId)
)

Though you might be better off using objection.js which allows you to declare relation mappings and then query directly:

const studentWithCourses = await Student.query().findById(studentId).withGraphFetched('courses');

本文标签： javascriptHow to query many to many relations with KnexjsStack Overflow