7

The PostLinks table in SEDE has a column called LinkTypeId, but there's nothing in the schema that describes what various values of LinkTypeId mean. For I think all of the other "Type" columns, there are corresponding description tables like CloseReasonTypes and PostHistoryTypes and ReviewTaskTypes.

So, it would be nice if we had a new table called PostLinkTypes. I guess it would just have two columns (Id tinyint and Name nvarchar), and probably only two rows, too, seeing as the only values of LinkTypeId I've found are 1 (regular links) and 3 (links to duplicates).

Motivation: I looked at the PostLinks table for the first time in the process of writing this query and had to spelunk through the table to figure out what the LinkTypeId column meant.

  • I also think this would be worth adding. It's easy enough to find documentation on it, but the lack of i info button is a bit inconsistent and mildly inconvenient, as is the lack of an automatic look up of descriptive names for ids for generating reports (still an easy workaround, of course). I'm a big fan of self-documentation. – Jason C Apr 2 '17 at 20:50
2

The two values you found are the only ones:

PostLinks

  • Id primary key
  • CreationDate when the link was created
  • PostId id of source post
  • RelatedPostId id of target/related post
  • LinkTypeId type of link
    1. Linked
    3. Duplicate

Until the feature request gets implemented you could setup the table your self (but a simple case when construction is less trouble)

/* setup the missing PostLinkTypes table 
   as a temporary table */
create table #PostLinkTypes(
id  integer,
name nvarchar(200))

/* fill table with lookup values */
insert into #PostLinkTypes values (1,'Linked');
insert into #PostLinkTypes values (3,'Duplicate');

/* end setup */

select top 100
       PostId as [Post Link]
      , RelatedPostId as [Post Link] 
      , plt.Name
      , case 
        when pl.linktypeid = 1 then 'Linked'
        when pl.linktypeid = 3 then 'Duplicate'  
        end as [alternative]
from PostLinks pl
inner join #PostLinkTypes plt on plt.id = pl.linktypeid
where plt.id = pl.linktypeid
order by creationdate desc

You can always check the Database schema documentation for the public data dump and SEDE for explanation of tables, fields and content.

  • 1
    Yeah, I mean, there are a ton of workarounds; the main reason I see for having the table in SEDE itself is so that you have the little "i" button for the table in the right pane of the editor so that you can easily view the list of link types. – senshin Jan 18 '15 at 2:28

You must log in to answer this question.

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