thread.ts (3984B)
1 import { modelBehaviour } from '@/lib/model_helpers' 2 import { OkPacket, RowDataPacket } from 'mysql2' 3 4 export interface Thread { 5 id: number 6 authorId: number 7 authorName: string 8 title: string 9 preview?: string // available on list 10 commentCount?: number // available on get 11 createdAt: Date 12 } 13 14 const SQL_LIST_THREADS = ` 15 select t.id, 16 t.author_id, 17 u.nickname, 18 t.title, 19 (select content 20 from thread_comments 21 where thread_id = t.id 22 order by id 23 limit 1) as preview, 24 t.created_at 25 from threads t 26 left join user_profiles u on u.login_id = t.author_id 27 order by t.created_at desc 28 limit ? offset ? 29 ` 30 31 export const listThreads = modelBehaviour< 32 [limit: number, offset: number], 33 Thread[] 34 >(async conn => { 35 const [rows] = await conn.query<RowDataPacket[]>({ 36 sql: SQL_LIST_THREADS, 37 }) 38 39 return rows.map(row => ({ 40 id: row[0], 41 authorId: row[1], 42 authorName: row[2], 43 title: row[3], 44 preview: row[4], 45 createdAt: row[5], 46 })) 47 }) 48 49 const SQL_CREATE_THREAD = ` 50 insert into threads (author_id, title) 51 values (?, ?) 52 ` 53 54 export const createThread = modelBehaviour< 55 [authorId: number, title: string], 56 number 57 >(async (conn, args) => { 58 const [result] = await conn.query<OkPacket>({ 59 sql: SQL_CREATE_THREAD, 60 }, args) 61 62 return result.insertId 63 }) 64 65 const SQL_GET_THREAD_AND_FIRST_COMMENT = ` 66 select t.id, 67 t.author_id, 68 u.nickname, 69 t.title, 70 t.created_at, 71 (select count(*) 72 from thread_comments 73 where thread_id = t.id) 74 as comment_count, 75 76 c.id, 77 c.thread_id, 78 c.author_id, 79 u.nickname, 80 c.content, 81 c.created_at, 82 c.updated_at 83 from threads t 84 left join user_profiles u on u.login_id = t.author_id 85 left join thread_comments c on c.thread_id = t.id 86 where t.id = ? 87 order by c.id 88 limit 1 89 ` 90 91 export const getThreadAndFirstComment = modelBehaviour< 92 [threadId: number], 93 [thread: Thread, comment: ThreadComment] | null 94 >(async (conn, args) => { 95 const [rows] = await conn.query<RowDataPacket[]>({ 96 sql: SQL_GET_THREAD_AND_FIRST_COMMENT, 97 }, args) 98 99 if (rows.length === 0) { 100 return null 101 } 102 103 const row = rows[0] 104 return [{ 105 id: row[0], 106 authorId: row[1], 107 authorName: row[2], 108 title: row[3], 109 createdAt: row[4], 110 commentCount: row[5], 111 }, { 112 id: row[6], 113 threadId: row[7], 114 authorId: row[8], 115 authorName: row[9], 116 content: row[10], 117 createdAt: row[11], 118 updatedAt: row[12], 119 }] 120 }) 121 122 export interface ThreadComment { 123 id: number 124 threadId: number 125 authorId: number 126 authorName: string 127 content: string 128 createdAt: Date 129 updatedAt?: Date 130 } 131 132 const SQL_LIST_THREAD_COMMENTS = ` 133 select c.id, c.thread_id, c.author_id, u.nickname, c.content, c.created_at, c.updated_at 134 from thread_comments c 135 left join user_profiles u on u.login_id = c.author_id 136 where c.thread_id = ? 137 order by c.created_at desc 138 limit ? offset ? 139 ` 140 141 export const listThreadComments = modelBehaviour< 142 [threadId: number, limit: number, offset: number], 143 ThreadComment[] 144 >(async (conn, args) => { 145 const [rows] = await conn.query<RowDataPacket[]>({ 146 sql: SQL_LIST_THREAD_COMMENTS, 147 }, args) 148 149 return rows.map(row => ({ 150 id: row[0], 151 threadId: row[1], 152 authorId: row[2], 153 authorName: row[3], 154 content: row[4], 155 createdAt: row[5], 156 updatedAt: row[6], 157 })) 158 }) 159 160 const SQL_CREATE_THREAD_COMMENT = ` 161 insert into thread_comments (thread_id, author_id, content) 162 values (?, ?, ?) 163 ` 164 165 export const createThreadComment = modelBehaviour< 166 [threadId: number, authorId: number, content: string], 167 number 168 >(async (conn, args) => { 169 const [result] = await conn.query<OkPacket>({ 170 sql: SQL_CREATE_THREAD_COMMENT, 171 }, args) 172 173 return result.insertId 174 })