javascript - 使用 BookshelfJS/KnexJS 的 NodeJS 脚本中的基本表关联

Question

我的第一个 BookshelfJS/KnexJS 脚本有一些问题，它有一个表关联。我非常仔细地复制了一对多示例，只是将其从 Books and Pages 切换为 Driers and Cars。但是，每当我执行查询并包含 withRelated 项时，它不会返回关联数据(在本例中为汽车)，它只会返回驱动程序。

协会是许多“汽车”的一个“司机”。我什至在演示中使用了 KnexJS 脚本来创建表格，因此它们与示例表格几乎相同。这是 JS 脚本:

'use strict';

const Config        = require('./config');
const Knex = require( 'knex' )( require('./config').database );

Knex.schema
    .createTable('drivers', function(table) {
        table.increments('driver_id').primary();
        table.string('name');
        table.timestamps();
    })
    .createTable('cars', function(table) {
        // using Myisam, since Innodb was throwing an error
        table.engine('myisam');
        table.increments('car_id').primary();
        table.integer('driver_id').references('drivers.driver_id');
        table.string('make');
        table.string('model');
        table.integer('year');
        table.timestamps();
    })
    .then(function(data){
        console.log('DONE',data);
    })
    .catch(function(err){
        console.log('ERROR',err);
    });

为了获得更多信息，这里是表格结构和表格内容:

mysql> explain drivers;
 +------------+------------------+------+-----+---------+----------------+
 | Field      | Type             | Null | Key | Default | Extra          |
 +------------+------------------+------+-----+---------+----------------+
 | driver_id  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
 | name       | varchar(255)     | YES  |     | NULL    |                |
 | created_at | datetime         | YES  |     | NULL    |                |
 | updated_at | datetime         | YES  |     | NULL    |                |
 +------------+------------------+------+-----+---------+----------------+
 4 rows in set (0.00 sec)

 mysql> explain cars;
 +------------+------------------+------+-----+---------+----------------+
 | Field      | Type             | Null | Key | Default | Extra          |
 +------------+------------------+------+-----+---------+----------------+
 | car_id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
 | driver_id  | int(11)          | YES  | MUL | NULL    |                |
 | make       | varchar(255)     | YES  |     | NULL    |                |
 | model      | varchar(255)     | YES  |     | NULL    |                |
 | year       | int(11)          | YES  |     | NULL    |                |
 | created_at | datetime         | YES  |     | NULL    |                |
 | updated_at | datetime         | YES  |     | NULL    |                |
 +------------+------------------+------+-----+---------+----------------+
 7 rows in set (0.00 sec)

 mysql> select * from drivers;
 +-----------+----------+---------------------+------------+
 | driver_id | name     | created_at          | updated_at |
 +-----------+----------+---------------------+------------+
 |         1 | John Doe | 2015-12-17 00:00:00 | NULL       |
 |         2 | The Stig | 2015-12-08 00:00:00 | NULL       |
 +-----------+----------+---------------------+------------+
 2 rows in set (0.00 sec)

 mysql> select * from cars;
 +--------+-----------+-----------+--------+------+---------------------+------------+
 | car_id | driver_id | make      | model  | year | created_at          | updated_at |
 +--------+-----------+-----------+--------+------+---------------------+------------+
 |      1 |         1 | Chevrolet | Camaro | 2014 | 2015-12-18 00:00:00 | NULL       |
 |      2 |         1 | Acura     | RSX-S  | 2004 | 2015-12-11 00:00:00 | NULL       |
 |      3 |         2 | Ford      | Focus  | 2004 | 2015-12-18 00:00:00 | NULL       |
 |      4 |         2 | Nissan    | Maxima | 2001 | 2015-12-17 00:00:00 | NULL       |
 |      5 |         2 | Geo       | Metro  | 1998 | 2015-12-18 00:00:00 | NULL       |
 +--------+-----------+-----------+--------+------+---------------------+------------+
 5 rows in set (0.00 sec)

然后是带有模型和查询执行的实际 NodeJS 脚本:

// app.js
'use strict';

const Config        = require('./config');
const Bookshelf     = require('./bookshelf');

var Driver = Bookshelf.Model.extend({
    tableName: 'drivers',
    cars: function() {
        return this.hasMany(Car);
    }
});

var Car = Bookshelf.Model.extend({
    tableName: 'cars',
    driver: function() {
        return this.belongsTo( Driver );
    }
});

new Driver()
    .where({
        driver_id: 2
    })
    .fetch({
        withRelated: ['cars']
    })
    .then(function(driver) {
        console.log('RELATED CAR:', JSON.stringify(driver));

        console.log('DRIVER DATA', driver);
    });

此外，这里是 bookshelf.js 文件，其中包含 KnexJS 和 BookshelfJS 连接:

// bookshelf.js
'use strict';

var knex = require( 'knex' )( require('./config').database );
module.exports = require('bookshelf')( knex );

这是执行 app.js 时的控制台输出

RELATED CAR: {"driver_id":2,"name":"The Stig","created_at":"2015-12-08T07:00:00.000Z","updated_at":null,"cars":[]}
 DRIVER DATA ModelBase {
  attributes:
   { driver_id: 2,
     name: 'The Stig',
     created_at: Tue Dec 08 2015 00:00:00 GMT-0700 (MST),
     updated_at: null },
  _previousAttributes:
   { driver_id: 2,
     name: 'The Stig',
     created_at: Tue Dec 08 2015 00:00:00 GMT-0700 (MST),
     updated_at: null },
  changed: {},
  relations:
   { cars:
      CollectionBase {
        model: [Object],
        length: 0,
        models: [],
        _byId: {},
        relatedData: [Object] } },
  cid: 'c1',
  _knex: null }

我不确定问题是什么，我觉得它是我忽略的相当简单的东西。

谢谢!

Answer 1

有两个问题

1. cars.driver_id 需要未签名，就像它在 drivers 表中引用的 ID 列一样
2. 我将表索引列创建为 ${singular_table_name}_id，根据我的经验，这是最常见的方法，但 BookshelfJS 期望它只是 id。因此，我可以将 ID 列名称更改为 id，或者将 idAttribute 值设置为 ${singular_table_name}_id

经过上述更改后一切正常。