Nest TypeORM Postgres update user's column('number of posts') based on the userId in-CodePudding

I'm wondering if it's possible to auto update the User's column('number of posts') if the Posts table updates. The Post entity has a ManyToOne relation with User('userId'). Is there a way to make the User Table "listen" to the Post Table and automatically updates the number of post column, or i need to write it in the post service create function to do so. I'm new to sql so i'm just trying new stuff. I'm using NestJS,typeORM, Postgres and Graphql

CodePudding user response：

@Kendle's answer does work and has the advantage of pushing the computation and complexity down onto your DB server. Alternatively, you can keep that logic in the application by leveraging TypeORM's Subscribers functionality. Documentation can be found here.

In your specific use case, you could register a subscriber for your Post entity implementing afterInsert and afterRemove (or afterSoftRemove if you soft delete posts) to increment and the decrement the counter respectively.

CodePudding user response：

You don't want to duplicate that data. That's the whole idea of a relational database that different data is kept in different tables.
You can create a view if you want to avoid typing a query with a JOIN each time.
For example you might create the view below:

CREATE VIEW userPosts AS
SELECT
  user.id,
  user.name,
  COUNT(posts.id)
FROM users
LEFT JOIN posts ON user.id = posts.user_id
ORDER BY  user.id;

Once you have created the view your can query it as if it were a table.

SELECT * FROM userDate WHERE id = '0001';

Of course I don't have your table definitions and data so you will need to adapt this code to your tables.