Keep a relation map in Objection.js while removing the table

Question

I'm developing a reddit-like site where votes are stored per-user (instead of per-post). Here's my relevant schema:

• content

  id | author_id | title       | text
  ---|-----------|-------------|---
  1  | 1 (adam)  | First Post  | This is a test post by adam
  

• vote: All the votes ever voted by anyone on any post

  id | voter_id    | content_id       | category_id
  ---|-------------|------------------|------------
  1  | 1 (adam)    | 1 ("First Post") | 1 (upvote)
  2  | 2 (bob)     | 1 ("First Post") | 1 (upvote)
  

• vote_count: Current tally ("count") of total votes received by a post by all users

  id | content_id       | category_id  | count
  ---|------------------|--------------|-------
  1  | 1 ("First Post") | 1 (upvote)   | 2
  

I've defined a voteCount relation in Objection.js model for the content table:

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

But I recently (learned and) decided that I don't need to keep (and update) a separate vote_count table, when in fact I can just query the vote table and essentially get the same table as a result:

SELECT content_id
     , category_id
     , COUNT(*) AS count
  FROM vote
GROUP 
    BY content_id
     , category_id

So now I wanna get rid of the vote_count table entirely.

But it seems that would break my voteCount relation since there won't be a VoteCount model (not shown here but it's the corresponding the model for the vote_count table) no more either. (Right?)

How do I keep voteCount relation while getting rid of vote_count table (and thus VoteCount model with it)?

Is there a way to somehow specify in the relation that instead of looking at a concrete table, it should look at the result of a query? Or is it possible to define a model class for the same?

My underlying database in PostgreSQL if that helps.

Answer 1

Thanks to @Belayer. Views were exactly the solution to this problem.

Objection.js supports using views (instead of table) in a Model class, so all I had to do was create a view based on the above query.

I'm also using Knex's migration strategy to create/version my database, and although it doesn't (yet) support creating views out of the box, I found you can just use raw queries:

module.exports.up = async function(knex) {
  await knex.raw(`
    CREATE OR REPLACE VIEW "vote_count" AS (
      SELECT content_id
          , category_id
          , COUNT(*) AS count
        FROM vote
      GROUP
          BY content_id
          , category_id
    )
  `);
};
module.exports.down = async function(knex) {
    await knex.raw('DROP VIEW "vote_count";');
};

The above migration step replaces my table vote_count for the equivalent view, and the Objection.js Model class for it (VoteCount) worked as usual without needing any change, and so did the relation voteCount on the Content class.