IDs in Data Explorer

[ganncamp (G Ann Campbell)]

I’ve noticed that if I select an FK ID in a table, I get a beautifully presented, linked name. Whereas if I select a PK ID in a table, I just get a number.

For example, when I select the (PK) id field from Posts:

Versus when I select the (FK) topic_id field from Posts:

I’ve been wondering if there’s some sleight-of-hand I can do to get this lovely linking behavior from PKs. Or do I always need to throw in an extra table & select from it when I want linking?

[JammyDodger]

You can alias that particular id to post_id and it will work its magic. Eg.

SELECT id AS post_id
FROM posts 
ORDER BY created_at DESC

[tshenry (Taylor)]

There are actually quite a few other little tricks to beautifying your results in the Discourse UI.

This old file shows some examples:

discourse/discourse-data-explorer/blob/9956b9833e223a0b459acc89056cc7546368241d/test/test_query.sql

I’ll integrate this into the main Data Explorer topic at some point when I have a spare moment.

[JammyDodger]

I also like the admin url one for making interactive user lists:

SELECT
  '/admin/users/' || users.id || '/' || users.username_lower
    AS admin_page_url
FROM users