使用Knex+Objection.js实现roomType与关联roomImage表批量插入

Let's work through this together—you're on the right track using transactions and insertGraph, but there are a few critical issues in your database schema, model relations, and route code that are blocking the multi-image insert from working as expected. Here's how to fix it step by step:

1. Fix the Database Schema (Reverse the Foreign Key) #

Your current schema has image_id on roomType, which sets up a one-to-one relationship (one room type can only have one image). For a one-to-many (one room type → multiple images), the foreign key needs to live on the roomImage table instead, pointing back to roomType.id.

Updated Schema Code:

.createTable(tableName.roomImage, (table) => { 
  table.increments('id').notNullable() 
  table.string('imageUrl').notNullable() 
  // Add foreign key linking to roomType
  table.integer('room_type_id')
    .references('id')
    .inTable(tableName.roomType)
    .onDelete('CASCADE') // Optional: delete images if room type is deleted
}).createTable(tableName.roomType, (table) => { 
  table.increments('id').notNullable() 
  table.string('type').notNullable() 
  table.string('description').notNullable() 
  table.float('price').notNullable() 
  table.integer('bed').notNullable() 
  // Remove the old image_id column here
})

2. Correct the Model Relationships #

Your relational mapping was reversed and used the wrong keys. We need to define that a RoomType has many Images, and link the tables correctly using the new room_type_id foreign key. Also, you had a typo: relationalMapping should be relationMappings (plural) for Objection.js to recognize it.

Updated Model Code:

const { Model } = require('objection') 
const tableNames = require('../../constants/tablename') 

class Image extends Model { 
  static get tableName() { return tableNames.roomImage } 
  static get idColumn() { return 'id' }

  // Optional: Add reverse relation for easier querying later
  static get relationMappings() {
    return {
      roomType: {
        relation: Model.BelongsToOneRelation,
        modelClass: require('./RoomType'), // Adjust path as needed
        join: {
          from: 'roomImage.room_type_id',
          to: 'roomType.id'
        }
      }
    }
  }
} 

class RoomType extends Model { 
  static get tableName() { return tableNames.roomType } 
  static get idColumn() { return 'id' } 

  static get relationMappings() {
    return { 
      images: { // Rename to a clear, plural name
        relation: Model.HasManyRelation, 
        modelClass: Image, 
        join: { 
          from: 'roomType.id', // Parent table's primary key
          to: 'roomImage.room_type_id' // Child table's foreign key
        } 
      } 
    } 
  } 
} 

module.exports = { RoomType, Image }

3. Fix the Route Code #

Your transaction had duplicate res.json() calls (which would cause errors), and you need to match the nested data structure to your model's relation name (images instead of roomImage). Also, use return after sending error responses to stop further code execution.

Updated Route Code:

router.post('/create', async (req, res, next) => { 
  const { type, price, bed, description, images } = req.body // Match relation name "images"
  try { 
    await schema.validate(req.body, { abortEarly: false }) 
    
    const existRoomType = await RoomType.query().findOne({ type })
    if (existRoomType) { 
      return res.status(400).json({ message: 'This room type already exists' })
    } 

    // Use transaction correctly with insertGraph
    const newRoomType = await RoomType.transaction(async trx => {
      return RoomType.query(trx).insertGraph({
        type,
        price,
        bed,
        description,
        images // Nested array of image objects
      }, { 
        relate: true, 
        // Optional: Use `insert: true` if you want to force insert even if images exist
      })
    })

    res.status(201).json({ data: newRoomType }) // 201 is the correct status for creation
  } catch (e) { 
    next(e) 
  } 
})

4. Example Request Body #

Send a POST request with this JSON structure to create a room type with multiple images:

{
  "type": "Deluxe Suite",
  "price": 199.99,
  "bed": 2,
  "description": "Spacious suite with ocean view",
  "images": [
    { "imageUrl": "https://example.com/image1.jpg" },
    { "imageUrl": "https://example.com/image2.jpg" },
    { "imageUrl": "https://example.com/image3.jpg" }
  ]
}

Key Notes: #

• insertGraph automatically handles nested relations as long as your model mappings are correct.
• Using onDelete('CASCADE') in the schema ensures that deleting a room type will also delete its associated images—adjust this if you don't want that behavior.
• Always use proper HTTP status codes (like 201 for successful creation) to follow REST best practices.

内容的提问来源于stack exchange，提问作者Him Meng sieng