| 1 | -- ─── follows ─────────────────────────────────────────────────────── |
| 2 | |
| 3 | -- name: FollowUser :one |
| 4 | WITH inserted AS ( |
| 5 | INSERT INTO follows (follower_user_id, followee_user_id) |
| 6 | VALUES ($1, $2) |
| 7 | ON CONFLICT (follower_user_id, followee_user_id) |
| 8 | WHERE followee_user_id IS NOT NULL |
| 9 | DO NOTHING |
| 10 | RETURNING 1 |
| 11 | ) |
| 12 | SELECT EXISTS (SELECT 1 FROM inserted) AS inserted; |
| 13 | |
| 14 | -- name: UnfollowUser :execrows |
| 15 | DELETE FROM follows |
| 16 | WHERE follower_user_id = $1 |
| 17 | AND followee_user_id = $2; |
| 18 | |
| 19 | -- name: FollowOrg :one |
| 20 | WITH inserted AS ( |
| 21 | INSERT INTO follows (follower_user_id, followee_org_id) |
| 22 | VALUES ($1, $2) |
| 23 | ON CONFLICT (follower_user_id, followee_org_id) |
| 24 | WHERE followee_org_id IS NOT NULL |
| 25 | DO NOTHING |
| 26 | RETURNING 1 |
| 27 | ) |
| 28 | SELECT EXISTS (SELECT 1 FROM inserted) AS inserted; |
| 29 | |
| 30 | -- name: UnfollowOrg :execrows |
| 31 | DELETE FROM follows |
| 32 | WHERE follower_user_id = $1 |
| 33 | AND followee_org_id = $2; |
| 34 | |
| 35 | -- name: IsFollowingUser :one |
| 36 | SELECT EXISTS ( |
| 37 | SELECT 1 FROM follows |
| 38 | WHERE follower_user_id = $1 |
| 39 | AND followee_user_id = $2 |
| 40 | ) AS following; |
| 41 | |
| 42 | -- name: IsFollowingOrg :one |
| 43 | SELECT EXISTS ( |
| 44 | SELECT 1 FROM follows |
| 45 | WHERE follower_user_id = $1 |
| 46 | AND followee_org_id = $2 |
| 47 | ) AS following; |
| 48 | |
| 49 | -- name: CountFollowersForUser :one |
| 50 | SELECT COUNT(*) FROM follows f |
| 51 | JOIN users u ON u.id = f.follower_user_id |
| 52 | WHERE f.followee_user_id = $1 |
| 53 | AND u.suspended_at IS NULL |
| 54 | AND u.deleted_at IS NULL; |
| 55 | |
| 56 | -- name: CountFollowingForUser :one |
| 57 | SELECT COUNT(*) FROM follows f |
| 58 | LEFT JOIN users u ON u.id = f.followee_user_id |
| 59 | LEFT JOIN orgs o ON o.id = f.followee_org_id |
| 60 | WHERE f.follower_user_id = $1 |
| 61 | AND (f.followee_user_id IS NULL OR (u.suspended_at IS NULL AND u.deleted_at IS NULL)) |
| 62 | AND (f.followee_org_id IS NULL OR (o.suspended_at IS NULL AND o.deleted_at IS NULL)); |
| 63 | |
| 64 | -- name: CountFollowersForOrg :one |
| 65 | SELECT COUNT(*) FROM follows f |
| 66 | JOIN users u ON u.id = f.follower_user_id |
| 67 | WHERE f.followee_org_id = $1 |
| 68 | AND u.suspended_at IS NULL |
| 69 | AND u.deleted_at IS NULL; |
| 70 | |
| 71 | -- name: ListFollowersForUser :many |
| 72 | SELECT f.follower_user_id AS user_id, f.followed_at, u.username, u.display_name |
| 73 | FROM follows f |
| 74 | JOIN users u ON u.id = f.follower_user_id |
| 75 | WHERE f.followee_user_id = $1 |
| 76 | AND u.suspended_at IS NULL |
| 77 | AND u.deleted_at IS NULL |
| 78 | ORDER BY f.followed_at DESC, f.id DESC |
| 79 | LIMIT $2 OFFSET $3; |
| 80 | |
| 81 | -- name: ListFollowingUsersForUser :many |
| 82 | SELECT f.followee_user_id AS user_id, f.followed_at, u.username, u.display_name |
| 83 | FROM follows f |
| 84 | JOIN users u ON u.id = f.followee_user_id |
| 85 | WHERE f.follower_user_id = $1 |
| 86 | AND u.suspended_at IS NULL |
| 87 | AND u.deleted_at IS NULL |
| 88 | ORDER BY f.followed_at DESC, f.id DESC |
| 89 | LIMIT $2 OFFSET $3; |
| 90 | |
| 91 | -- name: ListFollowingOrgsForUser :many |
| 92 | SELECT f.followee_org_id AS org_id, f.followed_at, o.slug, o.display_name |
| 93 | FROM follows f |
| 94 | JOIN orgs o ON o.id = f.followee_org_id |
| 95 | WHERE f.follower_user_id = $1 |
| 96 | AND o.suspended_at IS NULL |
| 97 | AND o.deleted_at IS NULL |
| 98 | ORDER BY f.followed_at DESC, f.id DESC |
| 99 | LIMIT $2 OFFSET $3; |
| 100 | |
| 101 | -- name: ListFollowersForOrg :many |
| 102 | SELECT f.follower_user_id AS user_id, f.followed_at, u.username, u.display_name |
| 103 | FROM follows f |
| 104 | JOIN users u ON u.id = f.follower_user_id |
| 105 | WHERE f.followee_org_id = $1 |
| 106 | AND u.suspended_at IS NULL |
| 107 | AND u.deleted_at IS NULL |
| 108 | ORDER BY f.followed_at DESC, f.id DESC |
| 109 | LIMIT $2 OFFSET $3; |
| 110 |