Post-Query Data Filtering & Sorting

[Entropy-rgb]

Problem Statement

Query results are displayed as static tables with no client-side filtering, sorting, or searching. Users must modify their SQL to change result ordering or filter rows, which is inefficient for data exploration.

Proposed Solution

Add interactive filtering, sorting, and search controls on query result tables, allowing users to explore data without re-querying the database.

Acceptance Criteria

• Column header click to sort ascending/descending
• Filter dropdown per column (text contains, exact match, range for numbers)
• Search box to filter across all columns
• Pagination controls (prev/next, page size selector)
• Filter/sort state preserved when switching between table/chart view
• Export filtered/sorted results as CSV
• Clear all filters button
• Active filter indicators (chips)

Technical Approach

Frontend Changes

1. Filter controls (frontend/src/lib/components/ResultFilters.svelte):

<script>
    export let columns = [];
    export let rows = [];
    export let filteredRows = [];
    
    let sortColumn = null;
    let sortDirection = 'asc';
    let filters = {};
    let searchQuery = '';
    let page = 0;
    let pageSize = 25;
    
    $: {
        let result = [...rows];
        
        // Apply search
        if (searchQuery) {
            result = result.filter(row => 
                row.some(cell => 
                    String(cell).toLowerCase().includes(searchQuery.toLowerCase())
                )
            );
        }
        
        // Apply column filters
        for (const [col, filter] of Object.entries(filters)) {
            const colIndex = columns.indexOf(col);
            if (filter.type === 'contains') {
                result = result.filter(row => 
                    String(row[colIndex]).toLowerCase().includes(filter.value.toLowerCase())
                );
            } else if (filter.type === 'exact') {
                result = result.filter(row => row[colIndex] === filter.value);
            } else if (filter.type === 'range') {
                result = result.filter(row => 
                    row[colIndex] >= filter.min && row[colIndex] <= filter.max
                );
            }
        }
        
        // Apply sort
        if (sortColumn !== null) {
            const colIndex = columns.indexOf(sortColumn);
            result.sort((a, b) => {
                const aVal = a[colIndex];
                const bVal = b[colIndex];
                const compare = typeof aVal === 'number' ? aVal - bVal : 
                    String(aVal).localeCompare(String(bVal));
                return sortDirection === 'asc' ? compare : -compare;
            });
        }
        
        // Apply pagination
        filteredRows = result.slice(page * pageSize, (page + 1) * pageSize);
    }
    
    function toggleSort(column) {
        if (sortColumn === column) {
            sortDirection = sortDirection === 'asc' ? 'desc' : 'asc';
        } else {
            sortColumn = column;
            sortDirection = 'asc';
        }
    }
</script>

<div class="result-filters">
    <div class="search-bar">
        <input 
            type="text" 
            bind:value={searchQuery} 
            placeholder="Search across all columns..."
        />
    </div>
    
    <div class="active-filters">
        {#each Object.entries(filters) as [col, filter]}
            <span class="filter-chip">
                {col}: {filter.type} {filter.value || `${filter.min}-${filter.max}`}
                <button on:click={() => delete filters[col]}>×</button>
            </span>
        {/each}
        {#if Object.keys(filters).length > 0}
            <button on:click={() => filters = {}}>Clear all</button>
        {/if}
    </div>
    
    <div class="pagination">
        <button on:click={() => page--} disabled={page === 0}>Previous</button>
        <span>Page {page + 1} of {Math.ceil(rows.length / pageSize)}</span>
        <button on:click={() => page++} disabled={filteredRows.length < pageSize}>Next</button>
        <select bind:value={pageSize}>
            <option value={10}>10 per page</option>
            <option value={25}>25 per page</option>
            <option value={50}>50 per page</option>
            <option value={100}>100 per page</option>
        </select>
    </div>
</div>

2. Column filter dropdown (frontend/src/lib/components/ColumnFilter.svelte):

<script>
    export let column;
    export let type; // 'text', 'number', 'date'
    export let onApply;
    
    let filterType = 'contains';
    let value = '';
    let min = '';
    let max = '';
</script>

<div class="column-filter">
    {#if type === 'text'}
        <select bind:value={filterType}>
            <option value="contains">Contains</option>
            <option value="exact">Exact match</option>
            <option value="starts_with">Starts with</option>
        </select>
        <input bind:value={value} placeholder="Filter value" />
    {:else if type === 'number'}
        <select bind:value={filterType}>
            <option value="range">Range</option>
            <option value="gt">Greater than</option>
            <option value="lt">Less than</option>
        </select>
        <input type="number" bind:value={min} placeholder="Min" />
        <input type="number" bind:value={max} placeholder="Max" />
    {/if}
    
    <button on:click={() => onApply({type: filterType, value, min, max})}>
        Apply
    </button>
</div>

3. Update ResultTable (frontend/src/lib/components/ui/ResultTable.svelte):

• Add sortable column headers
• Add filter dropdown per column
• Integrate with ResultFilters

4. Export functionality:

• Export filtered/sorted results as CSV
• Include filter state in export

Key Files

• frontend/src/lib/components/ResultFilters.svelte - Filter controls (new)
• frontend/src/lib/components/ColumnFilter.svelte - Column filter (new)
• frontend/src/lib/components/ui/ResultTable.svelte - Table updates
• frontend/src/lib/components/AnswerCard.svelte - Integration

Related Issues

• Part of Feature Roadmap (Feature Roadmap: Next Iteration of BoloDB #71)
• Related to Post-Query Data Filtering & Sorting for client-side filtering
• Related to Dashboard Builder (Dashboard Builder (Pin Results) #234) for widget filtering