# Recreate the Discourse user’s directory

**URL:** https://meta.discourse.org/t/recreate-the-discourse-user-s-directory/275011
**Category:** Data & reporting
**Tags:** user-directory, sql-query
**Created:** [August 14, 2017, 2:00am UTC](https://meta.discourse.org/t/recreate-the-discourse-user-s-directory/275011 "2017-08-14T02:00:19Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![jomaxro](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jomaxro/32/126216_2.png) [@jomaxro](https://meta.discourse.org/u/jomaxro)
#### Post date: [August 14, 2017, 2:00am UTC](https://meta.discourse.org/t/recreate-the-discourse-user-s-directory/275011/1 "2017-08-14T02:00:19Z")

</div>

### Recreate the Discourse user’s directory

OK, here is the query to recreate the user’s directory.

```sql
-- [params]
-- null int :period

SELECT users.username AS "Username",
directory_items.likes_received AS "Likes Received",
directory_items.likes_given AS "Likes Given",
directory_items.topic_count AS "Topics Created",
directory_items.post_count AS "Replied",
directory_items.days_visited AS "Vists",
directory_items.topics_entered AS "Viewed",
directory_items.posts_read AS "Read"
FROM users
JOIN directory_items ON users.id = directory_items.user_id
WHERE directory_items.period_type = :period
ORDER BY directory_items.likes_received DESC

```

Once the query is saved you can enter the period into the field below the query to determine what data you get. The periods are as follows:

`1`: all  
`2`: yearly  
`3`: monthly  
`4`: weekly  
`5`: daily  
`6`: quarterly

You can also change the sort by adjusting `directory_items.likes_received` on the last line.

---

<div class="post-metadata">

### Author: ![jord8on](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jord8on/32/166809_2.png) [@jord8on](https://meta.discourse.org/u/jord8on)
#### Post date: [March 13, 2020, 8:57pm UTC](https://meta.discourse.org/t/recreate-the-discourse-user-s-directory/275011/2 "2020-03-13T20:57:35Z")

</div>

@riking or @jomaxro - is there a way to add the “name” to this query instead of just the “username”?

This is for the [User Directory](https://github.com/SidVal/discourse-data-explorer/blob/queries/queries/user-directory.sql) Query:

```
-- https://meta.discourse.org/t/43516/48?u=sidv

-- [params]
-- null int :period

SELECT users.username AS "Username",
directory_items.likes_received AS "Likes Received",
directory_items.likes_given AS "Likes Given",
directory_items.topic_count AS "Topics Created",
directory_items.post_count AS "Replied",
directory_items.days_visited AS "Vists",
directory_items.topics_entered AS "Viewed",
directory_items.posts_read AS "Read"
FROM users
JOIN directory_items ON users.id = directory_items.user_id
WHERE directory_items.period_type = :period
ORDER BY directory_items.likes_received DESC

```

---

<div class="post-metadata">

### Author: ![SidV](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sidv/32/119460_2.png) [@SidV](https://meta.discourse.org/u/SidV)
#### Post date: [March 13, 2020, 10:21pm UTC](https://meta.discourse.org/t/recreate-the-discourse-user-s-directory/275011/3 "2020-03-13T22:21:37Z")

</div>

> [@jord8on](#):
>
> is there a way to add the “name” to this query instead of just the “username”?

Add:  
`users.name AS "Name",` after `SELECT users.username AS "Username",`

---

<div class="post-metadata">

### Author: ![jord8on](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jord8on/32/166809_2.png) [@jord8on](https://meta.discourse.org/u/jord8on)
#### Post date: [March 13, 2020, 11:30pm UTC](https://meta.discourse.org/t/recreate-the-discourse-user-s-directory/275011/4 "2020-03-13T23:30:49Z")

</div>

Many thanks @SidV!

I also wanted to add the users id (to be able to sort by when the user joined the community ツ

Here’s what my query looks like now, and it’s EXACLTY what I wanted! (note, I added the different “period” params to remind me what they are without having to come back to @jomaxro’s post.

```
-- https://meta.discourse.org/t/43516/48?u=sidv

-- [params]
-- null int :period

-- 1: all
-- 2: yearly
-- 3: monthly
-- 4: weekly
-- 5: daily
-- 6: quarterly

SELECT users.id AS "User ID",
users.username AS "Username",
users.name AS "Name",
directory_items.likes_received AS "Likes Received",
directory_items.likes_given AS "Likes Given",
directory_items.topic_count AS "Topics Created",
directory_items.post_count AS "Replied",
directory_items.days_visited AS "Vists",
directory_items.topics_entered AS "Viewed",
directory_items.posts_read AS "Read"
FROM users
JOIN directory_items ON users.id = directory_items.user_id
WHERE directory_items.period_type = :period
ORDER BY directory_items.likes_received DESC

```

Thanks again, y’all!!!