Which table holds the post URL?

dev
1

I’m using the data explorer to build a query which shows a user’s posts - which table can I use to return the URL of the post? I assumed it would be on posts but I don’t see it.

This is my query so far

SELECT u.email,c.name as post_category
 FROM posts p
left join user_emails u on p.user_id = u.user_id
left join topics t on p.topic_id=t.id
left join categories c on t.category_id = c.id
where p.created_at < '2021-03-01T00:00:00.000Z'
    GROUP BY
       u.email,u.user_id,c.name

thanks.

1 Like
2

In SQL it is

'/t/-' || topics.id || '/' || posts.post_number

Which on your example would be

SELECT u.email,c.name as post_category, '/t/-' || t.id || '/' || p.post_number as post_url
 FROM posts p
left join user_emails u on p.user_id = u.user_id
left join topics t on p.topic_id=t.id
left join categories c on t.category_id = c.id
where p.created_at < '2021-03-01T00:00:00.000Z'
    GROUP BY
       u.email,u.user_id,c.name
5 Likes
3

Thanks, that’s great.

FWIW with the hyphen it didn’t work, so instead of this:

'/t-/' || topics.id || '/' || posts.post_number

I used

'/t/' || topics.id || '/' || posts.post_number

All sorted :+1:

5 Likes