在Objection.js中移除vote_count表后如何保留voteCount关联？

Nice question! I've dealt with similar scenarios before when trying to avoid maintaining redundant summary tables in Objection.js. Here's how you can keep that voteCount association alive while ditching the vote_count table entirely:

Option 1: Use a PostgreSQL View (Recommended for Performance) #

This approach leverages a database view to replicate the vote_count table's behavior, but it dynamically calculates aggregates from the vote table instead of storing static data.

Step 1: Create the View in PostgreSQL #

First, run this SQL to create a view that mirrors your old vote_count table's output:

CREATE VIEW vote_aggregate AS
SELECT 
  content_id, 
  category_id, 
  COUNT(*) AS count
FROM vote
GROUP BY content_id, category_id;

Step 2: Create a Model for the View #

Next, define an Objection.js model that maps to this view (just like your old VoteCount model):

class VoteAggregate extends Model {
  static tableName = 'vote_aggregate';
  
  // Since our view uses a composite key (content_id + category_id), we need to define it explicitly
  static get idColumn() {
    return ['content_id', 'category_id'];
  }
}

Step 3: Update the Content Model's Association #

Modify your Content model's relationMappings to point to the new VoteAggregate model instead of VoteCount:

class Content extends Model {
  static tableName = 'content';
  
  static relationMappings = {
    voteCount: {
      relation: Model.HasManyRelation,
      modelClass: VoteAggregate,
      join: {
        from: 'content.id',
        to: 'vote_aggregate.content_id'
      }
    }
  }
}

Option 2: Pure Code-Based Subquery (No Database Changes) #

If you don't want to create a database view, you can define a model that uses a subquery directly as its "table" in Objection.js.

Step 1: Create a Subquery-Based Model #

Define a model that dynamically runs your aggregation query whenever it's accessed:

class VoteAggregate extends Model {
  static tableName() {
    // Return a raw subquery instead of a static table name
    return knex.raw(`
      (SELECT 
        content_id, 
        category_id, 
        COUNT(*) AS count
       FROM vote
       GROUP BY content_id, category_id) AS vote_aggregate
    `);
  }
  
  static get idColumn() {
    return ['content_id', 'category_id'];
  }
}

Step 2: Update the Content Model's Association #

Same as Option 1—update the Content model's voteCount association to use VoteAggregate.

How to Use the Updated Association #

Your existing queries will work exactly as before! For example:

// Fetch a post with its vote counts
const post = await Content.query()
  .withGraphFetched('voteCount')
  .findById(1);

// Output will match your old structure:
// post.voteCount = [{ content_id: 1, category_id: 1, count: 2 }]

Key Benefits #

• No more manual syncing of vote_count when votes are added/removed—aggregates are always up-to-date.
• You retain the same voteCount association interface, so you don't have to rewrite existing query logic.
• The PostgreSQL view option is optimized for performance, as the database can cache aggregation results.

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