1

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.

rasocbo
  • 35
  • 6
  • Welcome to StackOverflow community, can't notice during review stage that you question has pretty nice formatting for a new user! (Seems your have read this already: https://stackoverflow.com/help/how-to-ask) So if someone will help you with your problem, don't forget to upvote sugested answer and mark question as answered. Or press `thank you` button at least. – AlexZeDim Jul 19 '20 at 07:00
  • 1
    Should be ok however I do not know about your ORM. But "the result of a query" is a VIEW in the database. [From the Docs](https://www.postgresql.org/docs/current/sql-createview.html) : A "view is not physically materialized. Instead, the query is run every time the view is referenced in a query" – Belayer Jul 19 '20 at 07:00

1 Answers1

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.

rasocbo
  • 35
  • 6