Syncing aggregate data from one table to a single column on another

I'm new with Laravel. Trying to determine the best way to do some Count aggregation queries on a few tables and add the results to a column on another table. So for example:

Story Model Vote Model

A Story has a field called "score" which is used for sorting/searching etc in the app. This score gets updated each time a new Vote model entry is created. The votes can be either Up or Down (or Flat if they "unvote"). Once a user has cast a vote, that vote simply gets updated if the user changes their mind. So the Vote table ends up with a bunch of rows (one per user, per story), with three "vote" options:

1 = up 2 = down 3 = flat

To make sure that the counts are always correct, I was thinking a nightly Job to re-count the votes and update the master Story score column would be wise. So run a query to get the count of Up votes for each Story, then a query to get the count of Down votes of each Story, subtract to get a number and then update the Story.

This seems easy enough, but if I have 125k stories... what method should I be using to be the most performant? What's the best way to do this?

Initial thought was create a Job, then Queue that Job from a scheduler. Seems like running 125k queries to get ups, 125k queries to get downs, and 125k updates is a bad idea...

lostdreamer_nl

8 years ago

Best Answer

Level 53

The most efficient way is to change the vote options to:

-1 (down)
0 (flat)
1 (up)

Now you can do a SUM() on the field to get the actual score

I would then use a listener to update the score field every time a vote gets saved.

If you really also want to have something on the CRON to make sure it's always correct, then a single query should suffice (instead of 375.000).

untested, but should work:

UPDATE stories s 
LEFT JOIN
(
   SELECT story_id, SUM(vote) as total
   FROM votes 
   GROUP BY story_id
) v ON s.id = v.story_id
SET s.score = v.total

Update all stories.score fields with the sum of their votes in 1 single query.

note: You can do it with this query as well even if you keep the 1,2,3 values, but you'll need to include some IF checks within the inner query to make sure 2 maps to -1 and 3 maps to 0 to get the correct SUM

1 like

noahkuhn

8 years ago

Level 1

Seems like a good option, I was wary of putting negative numbers in as the vote value, but I suppose it's not a big deal, will test and report back. Thank you.

Please or to participate in this conversation.