How do you store and display if a user has voted or not on something?

Question

I'm working on a voting site and I'm wondering how I should handle votes.

For example on SO when you vote for a question (or answer) your vote is stored, and each time I go back on the page I can see that I already voted for this question because the up/down button are colored.

How do you do that? I mean I've several ideas but I'm wondering if it won't be an heavy load for the database.

Here is my ideas:

• Write an helper which will check for every question if a voted has been casted

  That's means that the number of queries will depends on the number of items displayed on the page (usually ~20)

• Loop on my items get the ids and for each page write a query which will returns if a vote has been casted or NULL

  Looks ok because only one query doesn't matter how much items on the page but may be break some MVC/Domain Model design, dunno.

• When User log in (or a guest for whom an anonymous user is created) retrieve all votes, store them in session, if a new vote is casted, just add it to the session.

  Looks nice because no queries is needed at all except the first one, however, this one and, depending on the number of votes casted (maybe a bunch for each user) can increase the size of the session for each users and potentially make the authentification slow.

How do you do? Any other ideas?

Marilynnmarimba

Assuming your votes are in a join table (users <-> questions) you can just get all the votes in the same query that use to load the questions, with a join for the current user. I guess what I mean is, the query strategy you'll need is dependent on how you're storing the data. – Fireworks

What ORM are you using ? If you are using ORM then, you will be able to fetch the necessary things you need, right ? – Lusty

I know how to retrieve the data I need, I'm wondering on which strategy choose from the list I given. The suggestion to fetch votes with the question is a good idea but not applicable in my case because I can't break my entities in such case or it'll be a lack of consistency over the other result (which are actually used in webservices) – Marilynnmarimba

Answer 1

For eg : Lets assume you have a table to store votes and the user who cast it.

Lets assume you keep votes in user_votes when a vote is cast with a table structure something like the below one.

id of type int autoincrement
user_id type int, Foreign key representing users table
question_id type of int, Foreign key representing questions table

Now as the user will be logged in , when you are doing a fetch for the questions do a left join with the user_id in the user_votes table.

Something like

SELECT q.id, q.question, uv.id 
   FROM questions AS q 
   LEFT JOIN user_votes AS uv ON 
      uv.question_id = q.id AND 
      uv.user_id = <logged_in_user_id> 
   WHERE <Your criteria>

From the view you can check whether the id is present. If so mark voted, else not.

You may need to change your fields of the questions table and all. I am assuming you store questions in questions table and users in user table so and so. All having the primary key id .

Thanks

Lusty

Hi, thank you for your answer, the problem with this solution is I'm currently working with an ORM, so I can't break my Entity which is not necessarily a logic representation of the database Schema. – Marilynnmarimba

@johnT even i am using Zend Framework with Doctrine, and i see no problem implementing the above solution. +1 for Hari K T. – Lengel

Answer 2

You could use a combination of your suggested strategies.

Retrieve all the votes made by the logged in user for recent/active questions only and store them in the session.

You then have the ones that are more likely to be needed while still reducing the amount you need to store in the session.

In the less likely event that you need other results, query for just those as and when you need to.

This strategy will reduce the amount you need to store in the session and also reduce the number of calls you make to your database.

Bonnybonnyclabber

Answer 3

Just based on the information than you've given so far, I would take the second approach: get the IDs of all the items on the page, and then do a single query to get all the user's votes for that list of item IDs. Then pass the collection of the user's item votes to your view, so it can render items differently when the user has voted for that item.

The other two approaches seem like they would tend to be less efficient, if I understood you correctly. Using a view helper to initiate an individual query for each item to check if the user has voted on it could lead to a lot of unnecessary queries. And preloading all the user's voting history at login seems to add unnecessary overhead, getting data that isn't always needed and adding the burden of keeping it up to date for the duration of the session.

Tekla