如何使用Bookshelf实现子记录统计？对应指定SQL的查询实现

Hey there! Let's walk through how to tackle your two Bookshelf.js requirements step by step. First, I'll assume you've already set up your Models for tables A and B—here's a quick reminder of what those might look like:

const A = bookshelf.model('A', {
  tableName: 'A',
  // Define the relationship: A has many B records
  bRecords() {
    return this.hasMany('B', 'a_id');
  }
});

const B = bookshelf.model('B', {
  tableName: 'B',
  // Define the inverse relationship: B belongs to A
  aRecord() {
    return this.belongsTo('A', 'a_id');
  }
});

1. Getting Child Record Counts (General Approach) #

There are a couple of practical ways to get the count of child records for a parent in Bookshelf:

Option 1: Fetch related records and count (simple, but less efficient for large datasets) #

If you're working with a single parent record, you can fetch its related child records and grab the length directly:

// Get count of B records for a specific A record (id = 1)
A.where('id', 1)
  .fetch({ withRelated: ['bRecords'] })
  .then(aRecord => {
    const childCount = aRecord.related('bRecords').length;
    console.log(`Total child records: ${childCount}`);
  })
  .catch(err => console.error(err));

Option 2: Use a database query for efficient counting (better for large datasets) #

For better performance—especially if you don't need the actual child records—you can use a direct aggregated query:

A.where('id', 1)
  .query(qb => {
    qb.select('A.*')
      .count('B.id as child_count')
      .leftJoin('B', 'A.id', '=', 'B.a_id')
      .groupBy('A.id');
  })
  .fetch()
  .then(aRecord => {
    console.log(`Total child records: ${aRecord.get('child_count')}`);
  })
  .catch(err => console.error(err));

2. Implementing the Specific SQL Query #

Your target SQL is:

SELECT A., (SELECT COUNT() FROM B WHERE B.a_id = A.id) AS TOT FROM A

To replicate this exact query in Bookshelf, you can use bookshelf.knex.raw() to embed the subquery as a custom column. Here's how:

A.query(qb => {
  // Select all columns from table A
  qb.select('A.*');
  // Add the subquery as a calculated column named 'tot'
  qb.select(bookshelf.knex.raw('(SELECT COUNT(*) FROM B WHERE B.a_id = A.id) AS tot'));
})
.fetchAll()
.then(allARecords => {
  // Iterate through each A record and access the count
  allARecords.each(aRecord => {
    console.log(`A ID: ${aRecord.get('id')}, Total B records: ${aRecord.get('tot')}`);
  });
})
.catch(err => console.error(err));

Alternative: Using Left Join + Group By (equivalent result) #

If you prefer a join-based approach instead of a subquery, this will give you the same result with slightly different SQL under the hood:

A.query(qb => {
  qb.select('A.*')
    .leftJoin('B', 'A.id', '=', 'B.a_id')
    .groupBy('A.id')
    .count('B.id as tot');
})
.fetchAll()
.then(allARecords => {
  // Handle records as needed
})
.catch(err => console.error(err));

内容的提问来源于stack exchange，提问作者Muhaimin CS