Skip to content

Commit e191d96

Browse files
joobisbjoobisb
authored
feat: support created_at filter for the GET /users endpoint (coder#15633)
Closes coder#12747 We support these filters currently: https://coder.com/docs/v2/latest/admin/users#user-filtering, adding `created_at` filter as well.
1 parent f16c809 commit e191d96

File tree

8 files changed

+170
-4
lines changed

8 files changed

+170
-4
lines changed

‎coderd/database/dbmem/dbmem.go

+20
Original file line numberDiff line numberDiff line change
@@ -5800,6 +5800,26 @@ func (q *FakeQuerier) GetUsers(_ context.Context, params database.GetUsersParams
58005800
users = usersFilteredByRole
58015801
}
58025802

5803+
if !params.CreatedBefore.IsZero() {
5804+
usersFilteredByCreatedAt := make([]database.User, 0, len(users))
5805+
for i, user := range users {
5806+
if user.CreatedAt.Before(params.CreatedBefore) {
5807+
usersFilteredByCreatedAt = append(usersFilteredByCreatedAt, users[i])
5808+
}
5809+
}
5810+
users = usersFilteredByCreatedAt
5811+
}
5812+
5813+
if !params.CreatedAfter.IsZero() {
5814+
usersFilteredByCreatedAt := make([]database.User, 0, len(users))
5815+
for i, user := range users {
5816+
if user.CreatedAt.After(params.CreatedAfter) {
5817+
usersFilteredByCreatedAt = append(usersFilteredByCreatedAt, users[i])
5818+
}
5819+
}
5820+
users = usersFilteredByCreatedAt
5821+
}
5822+
58035823
if !params.LastSeenBefore.IsZero() {
58045824
usersFilteredByLastSeen := make([]database.User, 0, len(users))
58055825
for i, user := range users {

‎coderd/database/modelqueries.go

+2
Original file line numberDiff line numberDiff line change
@@ -391,6 +391,8 @@ func (q *sqlQuerier) GetAuthorizedUsers(ctx context.Context, arg GetUsersParams,
391391
pq.Array(arg.RbacRole),
392392
arg.LastSeenBefore,
393393
arg.LastSeenAfter,
394+
arg.CreatedBefore,
395+
arg.CreatedAfter,
394396
arg.OffsetOpt,
395397
arg.LimitOpt,
396398
)

‎coderd/database/queries.sql.go

+17-2
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/users.sql

+11
Original file line numberDiff line numberDiff line change
@@ -199,6 +199,17 @@ WHERE
199199
last_seen_at >= @last_seen_after
200200
ELSE true
201201
END
202+
-- Filter by created_at
203+
AND CASE
204+
WHEN @created_before :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
205+
created_at <= @created_before
206+
ELSE true
207+
END
208+
AND CASE
209+
WHEN @created_after :: timestamp with time zone != '0001-01-01 00:00:00Z' THEN
210+
created_at >= @created_after
211+
ELSE true
212+
END
202213
-- End of filters
203214

204215
-- Authorize Filter clause will be injected below in GetAuthorizedUsers

‎coderd/searchquery/search.go

+2
Original file line numberDiff line numberDiff line change
@@ -70,6 +70,8 @@ func Users(query string) (database.GetUsersParams, []codersdk.ValidationError) {
7070
RbacRole: parser.Strings(values, []string{}, "role"),
7171
LastSeenAfter: parser.Time3339Nano(values, time.Time{}, "last_seen_after"),
7272
LastSeenBefore: parser.Time3339Nano(values, time.Time{}, "last_seen_before"),
73+
CreatedAfter: parser.Time3339Nano(values, time.Time{}, "created_after"),
74+
CreatedBefore: parser.Time3339Nano(values, time.Time{}, "created_before"),
7375
}
7476
parser.ErrorExcessParams(values)
7577
return filter, parser.Errors

‎coderd/users.go

+2
Original file line numberDiff line numberDiff line change
@@ -317,6 +317,8 @@ func (api *API) GetUsers(rw http.ResponseWriter, r *http.Request) ([]database.Us
317317
RbacRole: params.RbacRole,
318318
LastSeenBefore: params.LastSeenBefore,
319319
LastSeenAfter: params.LastSeenAfter,
320+
CreatedAfter: params.CreatedAfter,
321+
CreatedBefore: params.CreatedBefore,
320322
OffsetOpt: int32(paginationParams.Offset),
321323
LimitOpt: int32(paginationParams.Limit),
322324
})

‎coderd/users_test.go

+110
Original file line numberDiff line numberDiff line change
@@ -26,9 +26,11 @@ import (
2626
"github.com/coder/coder/v2/coderd/audit"
2727
"github.com/coder/coder/v2/coderd/coderdtest"
2828
"github.com/coder/coder/v2/coderd/database"
29+
"github.com/coder/coder/v2/coderd/database/db2sdk"
2930
"github.com/coder/coder/v2/coderd/database/dbauthz"
3031
"github.com/coder/coder/v2/coderd/database/dbfake"
3132
"github.com/coder/coder/v2/coderd/database/dbgen"
33+
"github.com/coder/coder/v2/coderd/database/dbtestutil"
3234
"github.com/coder/coder/v2/coderd/database/dbtime"
3335
"github.com/coder/coder/v2/coderd/rbac"
3436
"github.com/coder/coder/v2/coderd/util/ptr"
@@ -1515,6 +1517,73 @@ func TestUsersFilter(t *testing.T) {
15151517
users = append(users, user)
15161518
}
15171519

1520+
// Add users with different creation dates for testing date filters
1521+
for i := 0; i < 3; i++ {
1522+
// nolint:gocritic // Using system context is necessary to seed data in tests
1523+
user1, err := api.Database.InsertUser(dbauthz.AsSystemRestricted(ctx), database.InsertUserParams{
1524+
ID: uuid.New(),
1525+
Email: fmt.Sprintf("before%d@coder.com", i),
1526+
Username: fmt.Sprintf("before%d", i),
1527+
LoginType: database.LoginTypeNone,
1528+
Status: string(codersdk.UserStatusActive),
1529+
RBACRoles: []string{codersdk.RoleMember},
1530+
CreatedAt: dbtime.Time(time.Date(2022, 12, 15+i, 12, 0, 0, 0, time.UTC)),
1531+
})
1532+
require.NoError(t, err)
1533+
1534+
// The expected timestamps must be parsed from strings to compare equal during `ElementsMatch`
1535+
sdkUser1 := db2sdk.User(user1, nil)
1536+
sdkUser1.CreatedAt, err = time.Parse(time.RFC3339, sdkUser1.CreatedAt.Format(time.RFC3339))
1537+
require.NoError(t, err)
1538+
sdkUser1.UpdatedAt, err = time.Parse(time.RFC3339, sdkUser1.UpdatedAt.Format(time.RFC3339))
1539+
require.NoError(t, err)
1540+
sdkUser1.LastSeenAt, err = time.Parse(time.RFC3339, sdkUser1.LastSeenAt.Format(time.RFC3339))
1541+
require.NoError(t, err)
1542+
users = append(users, sdkUser1)
1543+
1544+
// nolint:gocritic //Using system context is necessary to seed data in tests
1545+
user2, err := api.Database.InsertUser(dbauthz.AsSystemRestricted(ctx), database.InsertUserParams{
1546+
ID: uuid.New(),
1547+
Email: fmt.Sprintf("during%d@coder.com", i),
1548+
Username: fmt.Sprintf("during%d", i),
1549+
LoginType: database.LoginTypeNone,
1550+
Status: string(codersdk.UserStatusActive),
1551+
RBACRoles: []string{codersdk.RoleOwner},
1552+
CreatedAt: dbtime.Time(time.Date(2023, 1, 15+i, 12, 0, 0, 0, time.UTC)),
1553+
})
1554+
require.NoError(t, err)
1555+
1556+
sdkUser2 := db2sdk.User(user2, nil)
1557+
sdkUser2.CreatedAt, err = time.Parse(time.RFC3339, sdkUser2.CreatedAt.Format(time.RFC3339))
1558+
require.NoError(t, err)
1559+
sdkUser2.UpdatedAt, err = time.Parse(time.RFC3339, sdkUser2.UpdatedAt.Format(time.RFC3339))
1560+
require.NoError(t, err)
1561+
sdkUser2.LastSeenAt, err = time.Parse(time.RFC3339, sdkUser2.LastSeenAt.Format(time.RFC3339))
1562+
require.NoError(t, err)
1563+
users = append(users, sdkUser2)
1564+
1565+
// nolint:gocritic // Using system context is necessary to seed data in tests
1566+
user3, err := api.Database.InsertUser(dbauthz.AsSystemRestricted(ctx), database.InsertUserParams{
1567+
ID: uuid.New(),
1568+
Email: fmt.Sprintf("after%d@coder.com", i),
1569+
Username: fmt.Sprintf("after%d", i),
1570+
LoginType: database.LoginTypeNone,
1571+
Status: string(codersdk.UserStatusActive),
1572+
RBACRoles: []string{codersdk.RoleOwner},
1573+
CreatedAt: dbtime.Time(time.Date(2023, 2, 15+i, 12, 0, 0, 0, time.UTC)),
1574+
})
1575+
require.NoError(t, err)
1576+
1577+
sdkUser3 := db2sdk.User(user3, nil)
1578+
sdkUser3.CreatedAt, err = time.Parse(time.RFC3339, sdkUser3.CreatedAt.Format(time.RFC3339))
1579+
require.NoError(t, err)
1580+
sdkUser3.UpdatedAt, err = time.Parse(time.RFC3339, sdkUser3.UpdatedAt.Format(time.RFC3339))
1581+
require.NoError(t, err)
1582+
sdkUser3.LastSeenAt, err = time.Parse(time.RFC3339, sdkUser3.LastSeenAt.Format(time.RFC3339))
1583+
require.NoError(t, err)
1584+
users = append(users, sdkUser3)
1585+
}
1586+
15181587
// --- Setup done ---
15191588
testCases := []struct {
15201589
Name string
@@ -1657,6 +1726,37 @@ func TestUsersFilter(t *testing.T) {
16571726
return u.LastSeenAt.Before(end) && u.LastSeenAt.After(start)
16581727
},
16591728
},
1729+
{
1730+
Name: "CreatedAtBefore",
1731+
Filter: codersdk.UsersRequest{
1732+
SearchQuery: `created_before:"2023-01-31T23:59:59Z"`,
1733+
},
1734+
FilterF: func(_ codersdk.UsersRequest, u codersdk.User) bool {
1735+
end := time.Date(2023, 1, 31, 23, 59, 59, 0, time.UTC)
1736+
return u.CreatedAt.Before(end)
1737+
},
1738+
},
1739+
{
1740+
Name: "CreatedAtAfter",
1741+
Filter: codersdk.UsersRequest{
1742+
SearchQuery: `created_after:"2023-01-01T00:00:00Z"`,
1743+
},
1744+
FilterF: func(_ codersdk.UsersRequest, u codersdk.User) bool {
1745+
start := time.Date(2023, 1, 1, 0, 0, 0, 0, time.UTC)
1746+
return u.CreatedAt.After(start)
1747+
},
1748+
},
1749+
{
1750+
Name: "CreatedAtRange",
1751+
Filter: codersdk.UsersRequest{
1752+
SearchQuery: `created_after:"2023-01-01T00:00:00Z" created_before:"2023-01-31T23:59:59Z"`,
1753+
},
1754+
FilterF: func(_ codersdk.UsersRequest, u codersdk.User) bool {
1755+
start := time.Date(2023, 1, 1, 0, 0, 0, 0, time.UTC)
1756+
end := time.Date(2023, 1, 31, 23, 59, 59, 0, time.UTC)
1757+
return u.CreatedAt.After(start) && u.CreatedAt.Before(end)
1758+
},
1759+
},
16601760
}
16611761

16621762
for _, c := range testCases {
@@ -1677,6 +1777,16 @@ func TestUsersFilter(t *testing.T) {
16771777
exp = append(exp, made)
16781778
}
16791779
}
1780+
1781+
// TODO: This can be removed with dbmem
1782+
if !dbtestutil.WillUsePostgres() {
1783+
for i := range matched.Users {
1784+
if len(matched.Users[i].OrganizationIDs) == 0 {
1785+
matched.Users[i].OrganizationIDs = nil
1786+
}
1787+
}
1788+
}
1789+
16801790
require.ElementsMatch(t, exp, matched.Users, "expected users returned")
16811791
})
16821792
}

‎docs/admin/users/index.md

+6-2
Original file line numberDiff line numberDiff line change
@@ -185,8 +185,10 @@ to use the Coder's filter query:
185185

186186
- To find active users, use the filter `status:active`.
187187
- To find admin users, use the filter `role:admin`.
188-
- To find users have not been active since July 2023:
188+
- To find users who have not been active since July 2023:
189189
`status:active last_seen_before:"2023-07-01T00:00:00Z"`
190+
- To find users who were created between January 1 and January 18, 2023:
191+
`created_before:"2023-01-18T00:00:00Z" created_after:"2023-01-01T23:59:59Z"`
190192

191193
The following filters are supported:
192194

@@ -195,6 +197,8 @@ The following filters are supported:
195197
- `role` - Represents the role of the user. You can refer to the
196198
[TemplateRole documentation](https://pkg.go.dev/github.com/coder/coder/v2/codersdk#TemplateRole)
197199
for a list of supported user roles.
198-
- `last_seen_before` and `last_seen_after` - The last time a used has used the
200+
- `last_seen_before` and `last_seen_after` - The last time a user has used the
199201
platform (e.g. logging in, any API requests, connecting to workspaces). Uses
200202
the RFC3339Nano format.
203+
- `created_before` and `created_after` - The time a user was created. Uses the
204+
RFC3339Nano format.

0 commit comments

Comments
 (0)