node.js - How i can define index for a table in node.js (adonis)

Question

Actually i have this table:

ClassBookHistoric
-----------------
Id (Primary Key), 
Class_Id (Unique key, foreign key of table Class),
BookUnit_Id (Unique key, foreign key of table BookUnits) 
Book_Id 
Status

I need to make queries to search data like this: SELECT WHERE Class_Id = (parameter), Book_Id = (parameter) and Status = 1 (active)

I'm studying about indexes and i'm thinking it is necessary to create a index using the columns that i will use to search data (Class_Id, Book_Id and Status) to increase the performance. There's a way to create a index to a group of columns: (Class_Id, Book_Id, Status)? If it's possible, how i can create a group of index in node.js/adonis?

标签： node.jsadonis.js

Answer 1

Adonis.js 用于knex.js定义列类型和其他修饰符，您可以从docs中看到。

因此，作为基于您的架构的示例（仅用于演示）：

'use strict'

const Schema = use('Schema')

class ClassBookHistoric extends Schema {
  up () {
    this.create('class_books', (table) => {
      table.increments()
      table.integer('class_id').notNullable().unique()
      table.integer('book_unit_Id').notNullable().unique()
      table.index(['class_id','book_unit_Id'], 'class_book_index');
      table.timestamps()
    })
  }

  down () {
    this.drop('class_books')
  }
}

module.exports = UsersSchema