5

I'm trying to morph this wonderful query to get all of one's posts so that it retrieves all the posts I have favorited. Full code here but here is the key piece I have modified, but does not work.

I added the inner join using the vote table. These are based the response to my comment yesterday from the author of the query (@rene) who said

"that is stored in the votes table, votetypeid = 5 and userid = [your userid on the site], join votes.postid with posts.id ".

Unsurprisingly, something isn't right with my attempt! Also, I feel like I need to remove the inner join on users, as I am not the author of posts I favorited.

I appreciate any guidance!

begin
    set @sql = N'use '+ QUOTENAME(@db_c_name) +';
               declare @url nvarchar(250)
               exec #siteurl ''' + @db_c_name  + ''', @url output
               insert into #all_posts 
               select @url
               , q.id
               , q.title
               , p.id
               , p.creationdate
               , p.score
               , p.body
               from posts p 
               inner join votes v on v.PostId
               where v.VoteTypeId = 5 and v.UserId = cast(@accountid as nvarchar)
               inner join posts q on q.id = coalesce(p.parentid, p.id)
               inner join users pu on pu.id = p.owneruserid 
               where pu.accountid = '+ cast(@accountid as nvarchar) + ';'
     exec (@sql)
   fetch next from db_c into @db_c_name
end;

UPDATE with this change:

               from posts p 
               inner join votes v on v.PostId = p.id 
                 and v.VoteTypeId = 5 and v.UserId = pu.id
               inner join posts q on q.id = coalesce(p.parentid, p.id)
               inner join users pu on pu.id = p.owneruserid 
               where pu.accountid = '+ cast(@accountid as nvarchar) + ';'

I get The multi-part identifier "pu.id" could not be bound.

6

You need to join the Votes row to your per site User.id

The from and where clause needs to become this:

from posts p 
inner join votes v on v.PostId = p.id 
       and v.VoteTypeId = 5 
inner join posts q on q.id = coalesce(p.parentid, p.id)
inner join users pu on pu.id = v.userid  -- your favorites
where pu.accountid = '+ cast(@accountid as nvarchar) + ';'

The forked query is here

The Votes and Users table are joined by Votes.UserId and Users.Id. That would normally be enough if you have a query for a single site. Grab your Userid from your user profile page and feed it into the query.

With a network wide / multi-database query this becomes somewhat more tricky as we can no longer use the Users.Id key value as your userid will be different on each site (Unless you are the Community user, or Jeff Atwood or Shog9 for some sites). That is where AccountId comes in. AccountId is an attribuut of the Users table. It does have the id of your network profile (316869 in your case). And that AccountId is stable for all sites. So in a network-wide / multi-database query we rely on this fact that the AccountId is the same for the same Users across all sites. For that reason we have to join with the Users table and use one where clause to restrict the Users set to that row where the AccountId matches our networkprofile id.

Give a big shout-out to Monica Cellio for the awesome SEDE Tutorial she wrote.
Say "Hi" in SEDE chat.

  • Thanks @rene for this, but apparently I'm not fully understanding (sorry). If I make the one line change you suggest, it still fails with -- An expression of non-boolean type specified in a context where a condition is expected, near 'where'. – Bryan Hanson Jan 18 at 15:57
  • @BryanHanson sorry, I missed you had put the where in the join statement. That was not where it should have been. I made a small change, see if that works better for you. – rene Jan 18 at 16:06
  • I made the change you suggested -- see update in original question. Gives a different complaint which seems like pu.id is not in scope. – Bryan Hanson Jan 18 at 16:26
  • @BryanHanson let's try if if works with p.owneruserid – rene Jan 18 at 16:53
  • It executes using p.owneruserid, but is returning my questions that others have favorited -- I'm trying to get questions by others that I have favorited (sorry if that wasn't completely clear). Since it works I was able to save it here: data.stackexchange.com/stackoverflow/query/1182670/… – Bryan Hanson Jan 18 at 17:03
  • @BryanHanson here you go :data.stackexchange.com/stackoverflow/query/1182671/… – rene Jan 18 at 17:26
  • Thank you for your patience, which is legion! The query returns 4 posts, which sadly, is the small number of my questions that have been favorited by others. The goal is to retrieve the 118 questions written by others which I favorited (to say this site has been helpful is an understatement!). – Bryan Hanson Jan 18 at 18:30
  • @BryanHanson yeah, that got messed up badly. I rearranged the whole from and where part. Now it looks much better. – rene Jan 18 at 20:08
  • Ha! Small miracle -- I made a change and got it to work! I had to change inner join users pu on pu.id = p.owneruserid where pu.accountid = '+ cast(@accountid as nvarchar) + ' to inner join users pu on pu.accountid = '+ cast(@accountid as nvarchar) + ' (so I eliminated the p.owneruserid bit which was limiting it to things I favorited). It now returns the correct info. Thank you so much @rene, for sticking with me, and also Monica and everyone who built this system. – Bryan Hanson Jan 18 at 21:25
  • For the record, the final version is data.stackexchange.com/stackoverflow/query/1182670/… – Bryan Hanson Jan 18 at 23:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .