0

I have a table called votes which stores votes made by users. The table stores the user_id, a group_id, a timestamp of the vote and the item_id for what the user voted for:

votes
------------
user_id
group_id
item_id
vote_datetime

The way I setup the table means that users can vote for the same item multiple times, and the only difference between these votes would be the datetime.

My question is how can I select all elements of that table with the same group_id, but with the condition to count only one vote per user for an item? thanks in advance carl

EDIT: Here is a start.

select all elements for group_id equal 1

allvotes = models.votes.query.filter_by(group_id=1).all()

so how would I modify this to make sure that users can't vote twice for the same item?

Bulat
  • 6,869
  • 1
  • 29
  • 52
carl
  • 4,216
  • 9
  • 55
  • 103

1 Answers1

0

You can assign primary key on combination of (user, group, item) like this:

user_id = Column(Integer, primary_key = True)
group_id = Column(Integer, primary_key = True)
item_id = Column(Integer, primary_key = True)

If you want just to query unique votes, use aggregate function with group:

query(user_id, group_id, item_id, func.min(vote_datetime)).
group_by(User_id, group_id, item_id)

Above should be close to the syntax you need.

Bulat
  • 6,869
  • 1
  • 29
  • 52
  • Hi Bulat, I don't quite understand how this solves my problem? – carl Aug 04 '15 at 00:05
  • It will not allow the duplication of user vote per item within a group, which means 'that users can't vote twice'. Or that was not the question? – Bulat Aug 04 '15 at 00:08
  • no I want users to vote multiple times, I just need to know how to select samples in which I only have unique votes. E.g. just pick the first vote with the corresponding group_id and user_id... – carl Aug 04 '15 at 01:34
  • I guess you need to rephrase your question to make it more clear. – Bulat Aug 04 '15 at 01:38
  • I have updated the answer, basically you need to group by results by user, group, item and get the min value for datetime. – Bulat Aug 04 '15 at 01:49