Fossil SCM

Attempt N at filtering /forum on the status tag. This still requires a separate query to get the status label for each entry and it's not yet proven to be performant enough for real use, but it essentially works. Bug: when browsing to page 2+ of a filtered list then changing the filter via the dropdown selection list, the x= param (starting offset) is retained but we need it to be removed.

stephan 2026-05-28 09:14 UTC forum-statuses
Commit 45a8de423b9af62470f24e9cf035347dd736071a41c6bf6415040f38c1581db2
Parent d01961c346476c5…
1 file changed +68 -10
~ src/forum.c
M src/forum.c
+68 -10
--- src/forum.c
+++ src/forum.c
@@ -2470,32 +2470,90 @@
24702470
 if( zStatusFilter &&
24712471
 (!*zStatusFilter || 0==fossil_strcmp("*",zStatusFilter)) ){
24722472
 zStatusFilter = 0;
24732473
 }
24742474
 if( db_table_exists("repository","forumpost") ){
2475
- Stmt qStat;
2475
+ const ForumStatusList * pFstat = forum_statuses();
2476
+ const int iStatusTagId = bHasStatus
2477
+ ? db_int(0, "SELECT tagid FROM TAG WHERE tagname='status'")
2478
+ : 0;
2479
+ Stmt qStat = empty_Stmt;
2480
+ db_multi_exec(
2481
+ /* List of forumpost.fpid values which match current
2482
+ filters. */
2483
+ "CREATE TEMP TABLE trootid(id INTEGER PRIMARY KEY);"
2484
+ );
24762485
 if( bHasStatus ){
2477
- db_prepare(&qStat,
2486
+ db_prepare(
2487
+ /* FIXME/TODO: it would be nice to be able to get this info
2488
+ ** directly into the thread query below, rather than
2489
+ ** requiring a separate statement and lookup inside the
2490
+ ** q loop. */
2491
+ &qStat,
24782492
 "SELECT tagxref.value, forumstatus.label\n"
24792493
 " FROM forumstatus, tagxref\n"
24802494
 " WHERE tagid=%d AND tagtype>=1\n"
24812495
 " AND forumstatus.value=tagxref.value\n"
24822496
 " AND rid=:rid\n"
24832497
 " AND if(%d=0,1,tagxref.value=%Q)\n"
24842498
 " ORDER BY mtime DESC",
2485
- db_int(0,"SELECT tagid FROM tag WHERE tagname='status'"),
2486
- !!zStatusFilter, zStatusFilter
2499
+ iStatusTagId, !!zStatusFilter, zStatusFilter
2500
+ );
2501
+ if( zStatusFilter ){
2502
+ const int bIsDflt =
2503
+ 0==fossil_strcmp(pFstat->aStatus[0].zValue, zStatusFilter);
2504
+ db_multi_exec(
2505
+ "INSERT INTO trootid\n"
2506
+ /* Rules:
2507
+
2508
+ (1) Filter on status=$zStatusFilter
2509
+ (2) If $zStatusFilter==default status then also include
2510
+ any posts with no status tag.
2511
+ (3) If no matching tag is found, assume a tag with the value
2512
+ of the first (default) status.
2513
+
2514
+ We need to ensure that we filter only the most recent
2515
+ value of each tag and count tagtype=0 (cancel) tags
2516
+ properly.
2517
+ */
2518
+ " SELECT fpid FROM forumpost, event\n"
2519
+ " LEFT JOIN tagxref x ON objid=rid\n"
2520
+ " WHERE froot=fpid AND firt IS NULL\n"
2521
+ " AND (x.tagid IS NULL OR x.tagid=%d)\n"
2522
+ " AND fpid=objid AND type='f'\n"
2523
+ " AND CASE\n"
2524
+ " WHEN %d THEN (x.value IS NULL OR x.value=%Q)\n" /* (2,3) */
2525
+ " ELSE x.value=%Q\n" /* (1) */
2526
+ " END",
2527
+ iStatusTagId, bIsDflt, zStatusFilter, zStatusFilter
2528
+ );
2529
+ (void)bIsDflt;
2530
+ }else{
2531
+ db_multi_exec(
2532
+ "INSERT INTO trootid\n"
2533
+ " SELECT fpid FROM forumpost, event\n"
2534
+ " WHERE froot=fpid AND firt IS NULL\n"
2535
+ " AND fpid=objid AND type='f'"
2536
+ );
2537
+ }
2538
+ }else{
2539
+ db_multi_exec(
2540
+ "INSERT INTO trootid\n"
2541
+ " SELECT fpid FROM forumpost, event\n"
2542
+ " WHERE froot=fpid AND firt IS NULL\n"
2543
+ " AND fpid=objid AND type='f'"
24872544
 );
24882545
 }
24892546
 db_prepare(&q,
24902547
 "WITH thread(root,endtime,lastrid) AS (\n"
24912548
 " SELECT\n"
24922549
 " froot,\n"
24932550
 " max(fmtime),\n"
24942551
 " fpid\n"
2495
- " FROM forumpost\n"
2552
+ " FROM forumpost, trootid\n"
24962553
 " WHERE %s/*ModForum*/\n"
2554
+ " AND froot=trootid.id\n"
24972555
 " GROUP BY froot\n"
24982556
 " ORDER BY 2 DESC\n"
24992557
 " LIMIT %d OFFSET %d\n"
25002558
 ")\n"
25012559
 "SELECT\n"
@@ -2523,19 +2581,19 @@
25232581
 const char *zStatus;
25242582
 const char *zStatusLbl;
25252583
 const int bShowStatus = bHasStatus && !zStatusFilter;
25262584
 const int nCols = bShowStatus ? 4 : 3
25272585
 /* When filtering on status, elide the status column */;
2528
- if( bHasStatus ){
2586
+ if( qStat.pStmt ){
25292587
 db_reset(&qStat);
25302588
 db_bind_int(&qStat, ":rid", db_column_int(&q,6));
25312589
 if( db_step(&qStat)==SQLITE_ROW ){
25322590
 zStatus = db_column_text(&qStat, 0);
25332591
 zStatusLbl = db_column_text(&qStat, 1);
25342592
 }else{
2535
- zStatus = forum_statuses()->aStatus[0].zValue;
2536
- zStatusLbl = forum_statuses()->aStatus[0].zLabel;
2593
+ zStatus = pFstat->aStatus[0].zValue;
2594
+ zStatusLbl = pFstat->aStatus[0].zLabel;
25372595
 }
25382596
 if( zStatusFilter && 0!=fossil_strcmp(zStatusFilter,zStatus) ){
25392597
 continue;
25402598
 }
25412599
 }else{
@@ -2605,21 +2663,21 @@
26052663
 }
26062664
 @ </td>\
26072665
 if( bShowStatus ){
26082666
 @ <td class='status'>%h(zStatusLbl)</td>\
26092667
 }
2610
- if( bHasStatus ){
2668
+ if( qStat.pStmt ){
26112669
 db_reset(&qStat);
26122670
 }
26132671
 @</tr>
26142672
 fossil_free(zAge);
26152673
 }
26162674
 db_finalize(&q);
2617
- if( bHasStatus ) db_finalize(&qStat);
2675
+ if( qStat.pStmt ) db_finalize(&qStat);
26182676
 }
26192677
 if( iCnt>0 ){
26202678
 @ </table></div>
26212679
 }else{
26222680
 @ <h1>No forum posts found</h1>
26232681
 }
26242682
 style_finish_page();
26252683
 }
26262684
--- src/forum.c
+++ src/forum.c
@@ -2470,32 +2470,90 @@
2470 if( zStatusFilter &&
2471 (!*zStatusFilter || 0==fossil_strcmp("*",zStatusFilter)) ){
2472 zStatusFilter = 0;
2473 }
2474 if( db_table_exists("repository","forumpost") ){
2475 Stmt qStat;
 
 
 
 
 
 
 
 
 
2476 if( bHasStatus ){
2477 db_prepare(&qStat,
 
 
 
 
 
2478 "SELECT tagxref.value, forumstatus.label\n"
2479 " FROM forumstatus, tagxref\n"
2480 " WHERE tagid=%d AND tagtype>=1\n"
2481 " AND forumstatus.value=tagxref.value\n"
2482 " AND rid=:rid\n"
2483 " AND if(%d=0,1,tagxref.value=%Q)\n"
2484 " ORDER BY mtime DESC",
2485 db_int(0,"SELECT tagid FROM tag WHERE tagname='status'"),
2486 !!zStatusFilter, zStatusFilter
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2487 );
2488 }
2489 db_prepare(&q,
2490 "WITH thread(root,endtime,lastrid) AS (\n"
2491 " SELECT\n"
2492 " froot,\n"
2493 " max(fmtime),\n"
2494 " fpid\n"
2495 " FROM forumpost\n"
2496 " WHERE %s/*ModForum*/\n"
 
2497 " GROUP BY froot\n"
2498 " ORDER BY 2 DESC\n"
2499 " LIMIT %d OFFSET %d\n"
2500 ")\n"
2501 "SELECT\n"
@@ -2523,19 +2581,19 @@
2523 const char *zStatus;
2524 const char *zStatusLbl;
2525 const int bShowStatus = bHasStatus && !zStatusFilter;
2526 const int nCols = bShowStatus ? 4 : 3
2527 /* When filtering on status, elide the status column */;
2528 if( bHasStatus ){
2529 db_reset(&qStat);
2530 db_bind_int(&qStat, ":rid", db_column_int(&q,6));
2531 if( db_step(&qStat)==SQLITE_ROW ){
2532 zStatus = db_column_text(&qStat, 0);
2533 zStatusLbl = db_column_text(&qStat, 1);
2534 }else{
2535 zStatus = forum_statuses()->aStatus[0].zValue;
2536 zStatusLbl = forum_statuses()->aStatus[0].zLabel;
2537 }
2538 if( zStatusFilter && 0!=fossil_strcmp(zStatusFilter,zStatus) ){
2539 continue;
2540 }
2541 }else{
@@ -2605,21 +2663,21 @@
2605 }
2606 @ </td>\
2607 if( bShowStatus ){
2608 @ <td class='status'>%h(zStatusLbl)</td>\
2609 }
2610 if( bHasStatus ){
2611 db_reset(&qStat);
2612 }
2613 @</tr>
2614 fossil_free(zAge);
2615 }
2616 db_finalize(&q);
2617 if( bHasStatus ) db_finalize(&qStat);
2618 }
2619 if( iCnt>0 ){
2620 @ </table></div>
2621 }else{
2622 @ <h1>No forum posts found</h1>
2623 }
2624 style_finish_page();
2625 }
2626
--- src/forum.c
+++ src/forum.c
@@ -2470,32 +2470,90 @@
2470 if( zStatusFilter &&
2471 (!*zStatusFilter || 0==fossil_strcmp("*",zStatusFilter)) ){
2472 zStatusFilter = 0;
2473 }
2474 if( db_table_exists("repository","forumpost") ){
2475 const ForumStatusList * pFstat = forum_statuses();
2476 const int iStatusTagId = bHasStatus
2477 ? db_int(0, "SELECT tagid FROM TAG WHERE tagname='status'")
2478 : 0;
2479 Stmt qStat = empty_Stmt;
2480 db_multi_exec(
2481 /* List of forumpost.fpid values which match current
2482 filters. */
2483 "CREATE TEMP TABLE trootid(id INTEGER PRIMARY KEY);"
2484 );
2485 if( bHasStatus ){
2486 db_prepare(
2487 /* FIXME/TODO: it would be nice to be able to get this info
2488 ** directly into the thread query below, rather than
2489 ** requiring a separate statement and lookup inside the
2490 ** q loop. */
2491 &qStat,
2492 "SELECT tagxref.value, forumstatus.label\n"
2493 " FROM forumstatus, tagxref\n"
2494 " WHERE tagid=%d AND tagtype>=1\n"
2495 " AND forumstatus.value=tagxref.value\n"
2496 " AND rid=:rid\n"
2497 " AND if(%d=0,1,tagxref.value=%Q)\n"
2498 " ORDER BY mtime DESC",
2499 iStatusTagId, !!zStatusFilter, zStatusFilter
2500 );
2501 if( zStatusFilter ){
2502 const int bIsDflt =
2503 0==fossil_strcmp(pFstat->aStatus[0].zValue, zStatusFilter);
2504 db_multi_exec(
2505 "INSERT INTO trootid\n"
2506 /* Rules:
2507
2508 (1) Filter on status=$zStatusFilter
2509 (2) If $zStatusFilter==default status then also include
2510 any posts with no status tag.
2511 (3) If no matching tag is found, assume a tag with the value
2512 of the first (default) status.
2513
2514 We need to ensure that we filter only the most recent
2515 value of each tag and count tagtype=0 (cancel) tags
2516 properly.
2517 */
2518 " SELECT fpid FROM forumpost, event\n"
2519 " LEFT JOIN tagxref x ON objid=rid\n"
2520 " WHERE froot=fpid AND firt IS NULL\n"
2521 " AND (x.tagid IS NULL OR x.tagid=%d)\n"
2522 " AND fpid=objid AND type='f'\n"
2523 " AND CASE\n"
2524 " WHEN %d THEN (x.value IS NULL OR x.value=%Q)\n" /* (2,3) */
2525 " ELSE x.value=%Q\n" /* (1) */
2526 " END",
2527 iStatusTagId, bIsDflt, zStatusFilter, zStatusFilter
2528 );
2529 (void)bIsDflt;
2530 }else{
2531 db_multi_exec(
2532 "INSERT INTO trootid\n"
2533 " SELECT fpid FROM forumpost, event\n"
2534 " WHERE froot=fpid AND firt IS NULL\n"
2535 " AND fpid=objid AND type='f'"
2536 );
2537 }
2538 }else{
2539 db_multi_exec(
2540 "INSERT INTO trootid\n"
2541 " SELECT fpid FROM forumpost, event\n"
2542 " WHERE froot=fpid AND firt IS NULL\n"
2543 " AND fpid=objid AND type='f'"
2544 );
2545 }
2546 db_prepare(&q,
2547 "WITH thread(root,endtime,lastrid) AS (\n"
2548 " SELECT\n"
2549 " froot,\n"
2550 " max(fmtime),\n"
2551 " fpid\n"
2552 " FROM forumpost, trootid\n"
2553 " WHERE %s/*ModForum*/\n"
2554 " AND froot=trootid.id\n"
2555 " GROUP BY froot\n"
2556 " ORDER BY 2 DESC\n"
2557 " LIMIT %d OFFSET %d\n"
2558 ")\n"
2559 "SELECT\n"
@@ -2523,19 +2581,19 @@
2581 const char *zStatus;
2582 const char *zStatusLbl;
2583 const int bShowStatus = bHasStatus && !zStatusFilter;
2584 const int nCols = bShowStatus ? 4 : 3
2585 /* When filtering on status, elide the status column */;
2586 if( qStat.pStmt ){
2587 db_reset(&qStat);
2588 db_bind_int(&qStat, ":rid", db_column_int(&q,6));
2589 if( db_step(&qStat)==SQLITE_ROW ){
2590 zStatus = db_column_text(&qStat, 0);
2591 zStatusLbl = db_column_text(&qStat, 1);
2592 }else{
2593 zStatus = pFstat->aStatus[0].zValue;
2594 zStatusLbl = pFstat->aStatus[0].zLabel;
2595 }
2596 if( zStatusFilter && 0!=fossil_strcmp(zStatusFilter,zStatus) ){
2597 continue;
2598 }
2599 }else{
@@ -2605,21 +2663,21 @@
2663 }
2664 @ </td>\
2665 if( bShowStatus ){
2666 @ <td class='status'>%h(zStatusLbl)</td>\
2667 }
2668 if( qStat.pStmt ){
2669 db_reset(&qStat);
2670 }
2671 @</tr>
2672 fossil_free(zAge);
2673 }
2674 db_finalize(&q);
2675 if( qStat.pStmt ) db_finalize(&qStat);
2676 }
2677 if( iCnt>0 ){
2678 @ </table></div>
2679 }else{
2680 @ <h1>No forum posts found</h1>
2681 }
2682 style_finish_page();
2683 }
2684

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button