| 233 | ) { |
| 234 | const db = ky.withTables<WithCache>() |
| 235 | const baseQuery = ( |
| 236 | db1: QueryCreator<DB & WithCache & CardTagRowid & NoteTagRowid> = db, |
| 237 | ) => { |
| 238 | // `cardWithTagCount` and `noteWithTagCount` exist because `join`ing in the tag table (like further below) forces us to use `HAVING` (instead of `WHERE`). |
| 239 | // A separate `HAVING` clause screws up our nonexistent boolean logic, since we use SQLite's query engine/`WHERE` for that. |
| 240 | const card = conversionResult.cardTagCount |
| 241 | ? ('cardWithTagCount as card' as 'card') |
| 242 | : 'card' |
| 243 | const note = conversionResult.noteTagCount |
| 244 | ? ('noteWithTagCount as note' as 'note') |
| 245 | : 'note' |
| 246 | return ( |
| 247 | db1 |
| 248 | .selectFrom(card) |
| 249 | .select('card.rowid as cardRowid') |
| 250 | .innerJoin(note, 'card.noteId', 'note.id') |
| 251 | .innerJoin('template', 'template.id', 'note.templateId') |
| 252 | .$if(sort != null, (db) => db.orderBy(sort!.col, sort!.direction)) |
| 253 | // don't `where` when scrolling - redundant since joining on the cache already filters |
| 254 | .$if(offset === 0 && conversionResult.sql != null, (db) => |
| 255 | db |
| 256 | .$if(conversionResult.joinNoteValueFts.length !== 0, (dbSeed) => { |
| 257 | let dbReturn = dbSeed |
| 258 | conversionResult.joinNoteValueFts.forEach((t) => { |
| 259 | const name = t.name as JoinFts |
| 260 | const dbJoined = dbReturn.leftJoin( |
| 261 | (eb) => |
| 262 | eb |
| 263 | .selectFrom('noteValueFts') |
| 264 | .innerJoin( |
| 265 | 'noteFieldValue', |
| 266 | 'noteFieldValue.rowid', |
| 267 | 'noteValueFts.rowid', |
| 268 | ) |
| 269 | .select(['noteFieldValue.noteId as z', 'rank']) // `z` also goes here 2DB5DD73-603E-4DF7-A366-A53375AF0093 |
| 270 | .where(t.sql) |
| 271 | .as(name), |
| 272 | (join) => join.onRef(`${name}.z`, '=', 'note.id'), |
| 273 | ) |
| 274 | dbReturn = dbJoined |
| 275 | }) |
| 276 | const rankSum = conversionResult.joinNoteValueFts |
| 277 | .map((t) => t.name + '.rank') |
| 278 | .join('+') |
| 279 | return dbReturn |
| 280 | .select(sql.raw(rankSum).as('rank')) |
| 281 | .orderBy('rank') |
| 282 | }) |
| 283 | .$if(conversionResult.joinNoteFieldValue.length !== 0, (dbSeed) => { |
| 284 | let dbReturn = dbSeed |
| 285 | conversionResult.joinNoteFieldValue.forEach((t) => { |
| 286 | const name = t.name as JoinFts |
| 287 | const dbJoined = dbReturn.leftJoin( |
| 288 | (eb) => |
| 289 | eb |
| 290 | .selectFrom('noteFieldValue') |
| 291 | .select('noteFieldValue.noteId as z') // z to make typescript happy; otherwise `noteId` gets consolidated with `card.noteId` and made nullable |
| 292 | .where(t.sql) |