Databases
StackOverflow2013
Postsdbo
PODetermine most recent 'vote type' for a record, returning 2 fields for HasVotedUp/Down?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODetermine most recent 'vote type' for a record, returning 2 fields for HasVotedUp/Down?
    primarykey
    data
    text
    <p>I have a SQL Server 2008 table containing 'votes'. They are either a 'VoteUp' or 'VoteDown' <code>VoteTypeId</code> (1 or 2 respectively).</p> <p>Each vote has a timestamp of when the vote was inserted in the table.<br> Each vote row belongs to a post (FK <code>PostId</code>).<br> Each vote row belongs to a user (FK <code>UserId</code>).</p> <p>I have a query to bring back a list of posts, and in that same query, I want to identify if the current user has voted that post up or down (or not at all).</p> <p>In some cases, a single post may contain both an Up AND a Down vote, where the user changed their mind at a later date (changing a Down vote to an Up vote). In this scenario, I want to identify the latest vote as being the relevant vote (the original down vote is simply held in the DB for historic purposes).</p> <p>Ideally, I'd like 2 simple BIT fields returned within the Query: <code>HasVotedUp</code> and <code>HasVotedDown</code>.</p> <p>I <em>could</em> include something like this (cut down for brevity), to simply return whether the most recent vote for that user/post is an Up or Down vote, then assign the 'HasVotedUp' and 'HasVotedDown' properties in code based on the returned VoteTypeId...</p> <pre><code>select p.id, p.body, (select top 1 votetypeid from votes where userid = 1 and postid = p.id order by creationdate desc) as CurrentVoteTypeId from posts p </code></pre> <p>... but I wondered if there was a similarly simple (but importantly, efficient) approach that I could use to return the 2 'HasVotedUp/Down' fields within the query, so it's on a plate, ready to use?</p> <p>This nested approach was all I came up with so far but I'm sure this is inefficient?!</p> <pre><code>select cast((select count(1) from votes where userid = 1 and postid = p.id and votetypeid = 1 and creationdate &gt; (select creationdate from votes where userid = 1 and postid = p.id and votetypeid = 2)) as bit) as HasVotedUp, cast((select count(1) from votes where userid = 1 and postid = p.id and votetypeid = 2 and creationdate &gt; (select creationdate from votes where userid = 1 and postid = p.id and votetypeid = 1)) as bit) as HasVotedDown from posts p </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMartin Smith
    1. USmarcusstarnes
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODetermine most recent 'vote type' for a record, returning 2 fields for HasVotedUp/Down?
      1. USNullCoder
      1. VTFavorite
    1. COSQL Server? What version? Also, we need to see a bit more of your query - I'm guessing what you've shown us is in the SELECT clause of your main query?
      singulars
      1. PODetermine most recent 'vote type' for a record, returning 2 fields for HasVotedUp/Down?
      1. USJohn N
    2. COSorry, it's SQL Server 2008. Yes, those snippets are from within the SELECT clause of the main query, which itself just selects * from posts (to keep things simpler). I'll update my question slightly. Thanks.
      singulars
      1. PODetermine most recent 'vote type' for a record, returning 2 fields for HasVotedUp/Down?
      1. USmarcusstarnes
    3. COI'm not sure what the `and creationdate > ` bits are needed for in your final query? It seems to be only including `HasVotedUp/Down` if the user previously did the opposite?
      singulars
      1. PODetermine most recent 'vote type' for a record, returning 2 fields for HasVotedUp/Down?
      1. USMartin Smith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload