[feat]: filter boolean

[john093e]

Feature description

Could you add a boolean filter ?

Many thanks in advance :)

Additional Context

Actually we can filter by search text, by selectable options, there is no simple boolean filter.

Before submitting

• I've made research efforts and searched the documentation
• I've searched for existing issues and PRs

[noxify]

@john093e - how would you do that? What would you expect ( Checkbox / Switch / Dropdown with Yes/No )? Any idea how it could be shown at the UI?

[sadmann7]

as @noxify mentioned, sharing a UI demo or screenshot would be really helpful

[john093e]

Hi there,
Thank you for your quick reply.

Honestly, I don't believe the UI requires significant changes from the faceted filter—just a few tweaks here and there should suffice.

For basic filtering, a dropdown option, similar to the faceted filter, seems suitable to me (without requiring text input).

For the advanced filters, instead of having a selection for the operator, I believe the selection should apply to the "true" or "false" values.

As I write this, I'm also considering the handling of unknown/unset/undefined values. However, this consideration isn't as crucial as implementing a simple boolean filter, to be honest.

I've quickly worked on the basic filters (I haven't touched the advanced filters yet, as I first need to understand all the intricacies of this part of the code).

To highlight the changes from the original code, I've added comments with "// <-- Added this line" next to the modifications.

inside tanstack-tables.ts:

export interface DataTableFilterField<TData> {
  label: string
  value: keyof TData
  placeholder?: string
  options?: Option[]
  boolean?: boolean // <-- Added this line
}

export interface DataTableFilterOption<TData> {
  id: string
  label: string
  value: keyof TData
  options: Option[]
  boolean: boolean // <-- Added this line
  filterValues?: string[]
  filterOperator?: string
  isMulti?: boolean
}

use-data-table.ts :

"use client"

import * as React from "react"
import { usePathname, useRouter, useSearchParams } from "next/navigation"
import type { DataTableFilterField } from "@/types"
import {
  getCoreRowModel,
  getFacetedRowModel,
  getFacetedUniqueValues,
  getFilteredRowModel,
  getPaginationRowModel,
  getSortedRowModel,
  useReactTable,
  type ColumnDef,
  type ColumnFiltersState,
  type PaginationState,
  type SortingState,
  type VisibilityState,
} from "@tanstack/react-table"
import { z } from "zod"

import { useDebounce } from "@/hooks/use-debounce"

interface UseDataTableProps<TData, TValue> {
  /**
   * The data for the table.
   * @default []
   * @type TData[]
   */
  data: TData[]

  /**
   * The columns of the table.
   * @default []
   * @type ColumnDef<TData, TValue>[]
   */
  columns: ColumnDef<TData, TValue>[]

  /**
   * The number of pages in the table.
   * @type number
   */
  pageCount: number

  /**
   * Defines filter fields for the table. Supports both dynamic faceted filters and search filters.
   * - Faceted filters are rendered when `options` are provided for a filter field.
   * - Boolean filters are rendered when `boolean` is set to `true`.
   * - Otherwise, search filters are rendered.
   *
   * The indie filter field `value` represents the corresponding column name in the database table.
   * @default []
   * @type { label: string, value: keyof TData, placeholder?: string, options?: { label: string, value: string, icon?: React.ComponentType<{ className?: string }> }[], boolean? : true }[]
   * @example
   * ```ts
   * // Render a search filter
   * const filterFields = [
   *   { label: "Title", value: "title", placeholder: "Search titles" }
   * ];
   * // Render a faceted filter
   * const filterFields = [
   *   {
   *     label: "Status",
   *     value: "status",
   *     options: [
   *       { label: "Todo", value: "todo" },
   *       { label: "In Progress", value: "in-progress" },
   *       { label: "Done", value: "done" },
   *       { label: "Canceled", value: "canceled" }
   *     ]
   *   }
   * ];
   * // Render a boolean filter   // <-- Added this line 
   * const filterFields = [
   *   {
   *     label: "Status",
   *     value: "status",
   *     boolean: true
   *   }
   * ];
   * ```
   */
  filterFields?: DataTableFilterField<TData>[]

  /**
   * Enable notion like column filters.
   * Advanced filters and column filters cannot be used at the same time.
   * @default false
   * @type boolean
   */
  enableAdvancedFilter?: boolean
}

export function useDataTable<TData, TValue>({
  columns,
  data,
  pageCount,
  filterFields = [],
  enableAdvancedFilter = false,
}: UseDataTableProps<TData, TValue>) {
  const router = useRouter()
  const pathname = usePathname()
  const searchParams = useSearchParams()

  // Search params
  const { page, per_page, sort } = TableSearchParamsSchema.parse(
    Object.fromEntries(searchParams)
  )
  const [column, order] = sort?.split(".") ?? []

  // Memoize computation of searchableColumns and filterableColumns
  const { searchableColumns, filterableColumns, filterableBooleanColumns } =
    useMemo(() => {
      return {
        // Searchable columns are those without options and not marked as boolean
        searchableColumns: filterFields.filter(
          (field) => !field.options && !field.boolean
        ),

        // Filterable columns are those with options (prioritize options over boolean if both are provided)
        filterableColumns: filterFields.filter((field) => field.options),

        // Filterable boolean columns are those marked as boolean and without options
        filterableBooleanColumns: filterFields.filter(
          (field) => field.boolean && !field.options
        ),
      } // <-- Added this line with some tweaks in the others, please check the comments 
    }, [filterFields])

  // Create query string
  const createQueryString = useCallback(
    (params: Record<string, string | number | null>) => {
      const newSearchParams = new URLSearchParams(searchParams?.toString())

      for (const [key, value] of Object.entries(params)) {
        if (value === null) {
          newSearchParams.delete(key)
        } else {
          newSearchParams.set(key, String(value))
        }
      }

      return newSearchParams.toString()
    },
    [searchParams]
  )
  // Initial column filters
  const initialColumnFilters: ColumnFiltersState = useMemo(() => {
    return Array.from(searchParams.entries()).reduce<ColumnFiltersState>(
      (filters, [key, value]) => {
        const filterableColumn = filterableColumns.find(
          (column) => column.value === key
        )
        const searchableColumn = searchableColumns.find(
          (column) => column.value === key
        )
        const filterableBooleanColumn = filterableBooleanColumns.find(
          (column) => column.value === key
        ) // <-- Added this line 

        if (filterableColumn) {
          filters.push({
            id: key,
            value: value.split("."),
          })
        } else if (searchableColumn) {
          filters.push({
            id: key,
            value: [value],
          })
        } else if (filterableBooleanColumn) {
          filters.push({
            id: key,
            value: value,
          })
        } // <-- Added this line 

        return filters
      },
      []
    )
  }, [
    filterableColumns,
    searchableColumns,
    filterableBooleanColumns, // <-- Added this line 
    searchParams,
  ])

  // Table states
  const [rowSelection, setRowSelection] = useState({})
  const [columnVisibility, setColumnVisibility] = useState<VisibilityState>({})
  const [columnFilters, setColumnFilters] =
    useState<ColumnFiltersState>(initialColumnFilters)

  // Handle server-side pagination
  const [{ pageIndex, pageSize }, setPagination] = useState<PaginationState>({
    pageIndex: page - 1,
    pageSize: per_page,
  })

  const pagination = useMemo(
    () => ({
      pageIndex,
      pageSize,
    }),
    [pageIndex, pageSize]
  )

  useEffect(() => {
    router.push(
      `${pathname}?${createQueryString({
        page: pageIndex + 1,
        per_page: pageSize,
      })}`,
      {
        scroll: false,
      }
    )

    // eslint-disable-next-line react-hooks/exhaustive-deps
  }, [pageIndex, pageSize])

  // Handle server-side sorting
  const [sorting, setSorting] = useState<SortingState>([
    {
      id: column ?? "",
      desc: order === "desc",
    },
  ])

  useEffect(() => {
    router.push(
      `${pathname}?${createQueryString({
        page,
        sort: sorting[0]?.id
          ? `${sorting[0]?.id}.${sorting[0]?.desc ? "desc" : "asc"}`
          : null,
      })}`
    )

    // eslint-disable-next-line react-hooks/exhaustive-deps
  }, [sorting])

  // Handle server-side filtering
  const debouncedSearchableColumnFilters = JSON.parse(
    useDebounce(
      JSON.stringify(
        columnFilters.filter((filter) => {
          return searchableColumns.find((column) => column.value === filter.id)
        })
      ),
      500
    )
  ) as ColumnFiltersState

  const filterableColumnFilters = columnFilters.filter((filter) => {
    return filterableColumns.find((column) => column.value === filter.id)
  })

  const filterableBooleanColumnFilters = columnFilters.filter((filter) => {
    return filterableBooleanColumns.find((column) => column.value === filter.id)
  })  // <-- Added this line 

  const [mounted, setMounted] = useState(false)

  useEffect(() => {
    // Opt out when advanced filter is enabled, because it contains additional params
    if (enableAdvancedFilter) return

    // Prevent resetting the page on initial render
    if (!mounted) {
      setMounted(true)
      return
    }

    // Initialize new params
    const newParamsObject = {
      page: 1,
    }

    // Handle debounced searchable column filters
    for (const column of debouncedSearchableColumnFilters) {
      if (typeof column.value === "string") {
        Object.assign(newParamsObject, {
          [column.id]: typeof column.value === "string" ? column.value : null,
        })
      }
    }

    // Handle filterable column filters
    for (const column of filterableColumnFilters) {
      if (typeof column.value === "object" && Array.isArray(column.value)) {
        Object.assign(newParamsObject, { [column.id]: column.value.join(".") })
      }
    }

    // Handle filterable boolean column filters
    for (const column of filterableBooleanColumnFilters) {
      if (typeof column.value === "string") {
        Object.assign(newParamsObject, {
          [column.id]: typeof column.value === "string" ? column.value : null,
        })
      }
    } <-- Added this line 

    // Remove deleted values
    for (const key of searchParams.keys()) {
      if (
        (searchableColumns.find((column) => column.value === key) &&
          !debouncedSearchableColumnFilters.find(
            (column) => column.id === key
          )) ||
        (filterableColumns.find((column) => column.value === key) &&
          !filterableColumnFilters.find((column) => column.id === key)) ||
        (filterableBooleanColumns.find((column) => column.value === key) &&
          !filterableBooleanColumnFilters.find((column) => column.id === key))  // <-- Added this line 
      ) {
        Object.assign(newParamsObject, { [key]: null })
      }
    }

    // After cumulating all the changes, push new params
    router.push(`${pathname}?${createQueryString(newParamsObject)}`)

    // eslint-disable-next-line react-hooks/exhaustive-deps
  }, [
    // eslint-disable-next-line react-hooks/exhaustive-deps
    JSON.stringify(debouncedSearchableColumnFilters),
    // eslint-disable-next-line react-hooks/exhaustive-deps
    JSON.stringify(filterableColumnFilters),
    // eslint-disable-next-line react-hooks/exhaustive-deps
    JSON.stringify(filterableBooleanColumnFilters), // <-- Added this line 
  ])

  const table = useReactTable({
    data,
    columns,
    pageCount: pageCount ?? -1,
    state: {
      pagination,
      sorting,
      columnVisibility,
      rowSelection,
      columnFilters,
    },
    enableRowSelection: true,
    onRowSelectionChange: setRowSelection,
    onPaginationChange: setPagination,
    onSortingChange: setSorting,
    onColumnFiltersChange: setColumnFilters,
    onColumnVisibilityChange: setColumnVisibility,
    getCoreRowModel: getCoreRowModel(),
    getFilteredRowModel: getFilteredRowModel(),
    getPaginationRowModel: getPaginationRowModel(),
    getSortedRowModel: getSortedRowModel(),
    getFacetedRowModel: getFacetedRowModel(),
    getFacetedUniqueValues: getFacetedUniqueValues(),
    manualPagination: true,
    manualSorting: true,
    manualFiltering: true,
  })

  return { table }
}

inside data-table-toolbar.tsx:

import { DataTableBooleanFilter } from "@/components/data-table/data-table-boolean-filter" // <-- Added this line
import { DataTableFacetedFilter } from "@/components/data-table/data-table-faceted-filter"
import { DataTableViewOptions } from "@/components/data-table/data-table-view-options"

...

// Memoize computation of searchableColumns and filterableColumns
  const { searchableColumns, filterableColumns, filterableBooleanColumns } =
    useMemo(() => {
      return {
        searchableColumns: filterFields.filter(
          (field) => !field.options && !field.boolean
        ),
        filterableColumns: filterFields.filter((field) => field.options),
        filterableBooleanColumns: filterFields.filter(
          (field) => field.boolean && !field.options
        ), // <-- Added this line
      }
    }, [filterFields])

...

{filterableColumns.length > 0 &&
          filterableColumns.map(
            (column) =>
              table.getColumn(column.value ? String(column.value) : "") && (
                <DataTableFacetedFilter
                  key={String(column.value)}
                  column={table.getColumn(
                    column.value ? String(column.value) : ""
                  )}
                  title={column.label}
                  options={column.options ?? []}
                />
              )
          )}
        {filterableBooleanColumns.length > 0 &&
          filterableBooleanColumns.map(
            (column) =>
              table.getColumn(column.value ? String(column.value) : "") && (
                <DataTableBooleanFilter
                  key={String(column.value)}
                  column={table.getColumn(
                    column.value ? String(column.value) : ""
                  )}
                  title={column.label}
                />
              )
          )} // <-- Added this line 
        {isFiltered && (
        
        ....

Created a new data-table component "data-table-boolean-filter.tsx":

import { CheckIcon, PlusCircledIcon } from "@radix-ui/react-icons"
import type { Column } from "@tanstack/react-table"

import { cn } from "@/lib/utils"
import { Badge } from "@/components/ui/badge"
import { Button } from "@/components/ui/button"
import {
  Command,
  CommandGroup,
  CommandItem,
  CommandList,
  CommandSeparator,
} from "@/components/ui/command"
import {
  Popover,
  PopoverContent,
  PopoverTrigger,
} from "@/components/ui/popover"
import { Separator } from "@/components/ui/separator"

interface DataTableBooleanFilterProps<TData, TValue> {
  column?: Column<TData, TValue>
  title?: string
}

export function DataTableBooleanFilter<TData, TValue>({
  column,
  title,
}: DataTableBooleanFilterProps<TData, TValue>) {
  const selectedValue = column?.getFilterValue() as string | undefined


  const booleanOptions = [
    { label: "True", value: "true" },
    { label: "False", value: "false" },
  ]

  const handleSelect = (value: string) => {
    // Toggle value if the same value is selected, else set the new value
    column?.setFilterValue(selectedValue === value ? undefined : value)
  }

  const clearFilter = () => {
    column?.setFilterValue(undefined) // Clear filter
  }

  return (
    <Popover>
      <PopoverTrigger asChild>
        <Button variant="outline" size="sm" className="h-8 border-dashed">
          <PlusCircledIcon className="mr-2 size-4" />
          {title}
          {selectedValue && (
            <>
              <Separator orientation="vertical" className="mx-2 h-4" />
              <Badge
                variant="secondary"
                className="rounded-sm px-1 font-normal"
              >
              {selectedValue === "true" ? "True" : "False"}
              </Badge>
            </>
          )}
        </Button>
      </PopoverTrigger>
      <PopoverContent className="w-[12.5rem] p-0" align="start">
        <Command>
          <CommandList>
            <CommandGroup>
              {booleanOptions.map((option) => (
                <CommandItem
                  key={option.value}
                  onSelect={() => handleSelect(option.value)}
                >
                  <div
                    className={cn(
                      "mr-2 flex size-4 items-center justify-center rounded-sm border border-primary",
                      selectedValue === option.value
                        ? "bg-primary text-primary-foreground"
                        : "opacity-50 [&_svg]:invisible"
                    )}
                  >
                    {selectedValue === option.value && (
                      <CheckIcon className="size-4" aria-hidden="true" />
                    )}
                  </div>
                  <span>{option.label}</span>
                </CommandItem>
              ))}
            </CommandGroup>
            {selectedValue && (
              <>
                <CommandSeparator />
                <CommandGroup>
                  <CommandItem
                    onSelect={() => clearFilter()}
                    className="justify-center text-center"
                  >
                    Clear filter
                  </CommandItem>
                </CommandGroup>
              </>
            )}
          </CommandList>
        </Command>
      </PopoverContent>
    </Popover>
  )
}

tweaking the filter-coumns.ts.ts :

// TODO: find solution to not duplicate it in packages/api and in app/main

import type { Column, ColumnBaseConfig, ColumnDataType } from "drizzle-orm"
import {
  eq,
  ilike,
  inArray,
  isNotNull,
  isNull,
  not,
  notLike,
} from "drizzle-orm"

import type { DataTableConfig } from "./data-table"

export function filterColumn({
  column,
  value,
  isSelectable,
  isBoolean,
}: {
  column: Column<ColumnBaseConfig<ColumnDataType, string>, object, object>
  value: string
  isSelectable?: boolean
  isBoolean?: boolean
}) {
  const [filterValue, filterOperator] = (value?.split("~").filter(Boolean) ??
    []) as [
    string,
    DataTableConfig["comparisonOperators"][number]["value"] | undefined,
  ]

  if (!filterValue) return

  // Check if isBoolean is true, then directly parse filterValue as a boolean
  let booleanValue = true
  if (isBoolean) {
    booleanValue = filterValue.toLowerCase() === "true"
  }

  if (isSelectable) {
    switch (filterOperator) {
      case "eq":
        return inArray(column, filterValue?.split(".").filter(Boolean) ?? [])
      case "notEq":
        return not(
          inArray(column, filterValue?.split(".").filter(Boolean) ?? [])
        )
      case "isNull":
        return isNull(column)
      case "isNotNull":
        return isNotNull(column)
      default:
        return inArray(column, filterValue?.split(".") ?? [])
    }
  }

  switch (filterOperator) {
    case "ilike":
      return ilike(column, `%${filterValue}%`)
    case "notIlike":
      return notLike(column, `%${filterValue}%`)
    case "startsWith":
      return ilike(column, `${filterValue}%`)
    case "endsWith":
      return ilike(column, `%${filterValue}`)
    case "eq":
      return eq(column, isBoolean ? booleanValue : filterValue) // use booleanValue when isBoolean is true
    case "notEq":
      return not(eq(column, isBoolean ? booleanValue : filterValue)) // use booleanValue when isBoolean is true
    case "isNull":
      return isNull(column)
    case "isNotNull":
      return isNotNull(column)
    default:
      // For boolean columns, defaulting to a contains search doesn't make sense,
      // so we default to an equality check if the operator is not explicitly handled.
      return isBoolean
        ? eq(column, booleanValue)
        : ilike(column, `%${filterValue}%`)
  }
}

inside the queries.ts, to add the filter column for boolean :

                  !!activated
                    ? filterColumn({
                        column: schema.permissionSets.editable,
                        value: input.editable,
                        isBoolean: true, // <-- Remove the isSelectable and use isBoolean 
                      })
                    : undefined,

Lastly to add the filter inside the column definition we can simply add {label : string, value: string, boolean: boolean} to the filterFields like so :

task-table-column.tsx :

...

export const filterFields: DataTableFilterField<Task>[] = [
  {
    label: "Title",
    value: "title",
    placeholder: "Filter titles...",
  },
  {
    label: "Status",
    value: "status",
    options: tasks.status.enumValues.map((status) => ({
      label: status[0]?.toUpperCase() + status.slice(1),
      value: status,
    })),
  },
  {
    label: "Priority",
    value: "priority",
    options: tasks.priority.enumValues.map((priority) => ({
      label: priority[0]?.toUpperCase() + priority.slice(1),
      value: priority,
    })),
  },
    {
    label: "Activated",
    value: "activated",
    boolean: true,
  },
]

...

As for the advanced filter mentioned at the beginning of this comment, I still haven't taken the time to fully understand how everything works, so I can't share any code (for now).

Here is a showcase of the boolean filter I've implemented. (Note: I'm not using Shadcn; the CMKD UI doesn't align with the rest of my UI, and my computer is struggling :p)

teaser.mp4

[noxify]

Awesome! Thanks for the detailed reply. For me it looks good - not sure what the current state of the advanced filter is.

Maybe we could start with your approach which should handle most of the use cases ( or all?)

Just an idea for a later iteration: instead of having a flag which defines the search field, we could use an enumeration like type to define the field type. Would be a more generic/modular solution which allows us to add more field types in the future without having multiple flags.

Possible types could be:

• text
• select
• multiselect
• boolean

@sadmann7 what do you think about this? Does this makes sense or do you have any other ideas/plans for the filter?

[sadmann7]

looks cool @john093e. @noxify that might be the way to go. more dynamic and modular

[dBianchii]

+1 here

[Stefisrb]

@john093e @noxify
I needed something like this too.
I think this can help for boolean filter.

import { Column } from "@tanstack/react-table";
import { Switch } from "@/components/ui/switch";
import { Cross2Icon } from "@radix-ui/react-icons";
import { Badge } from "@/components/ui/badge";

interface ColumnFilterSwitchProps<TData, TValue> {
  column?: Column<TData, TValue>;
  title?: string;
}

const ColumnFilterSwitch = <TData, TValue>({
  column,
  title,
}: ColumnFilterSwitchProps<TData, TValue>) => {
  return (
    <div className="tw-flex tw-items-center tw-space-x-2 tw-border tw-border-dashed  tw-p-1 tw-rounded-md tw-h-9">
      <span className="tw-text-xs tw-pl-1">{title}</span>
      <Switch
        checked={
          column?.getFilterValue() === true && column?.getIsFiltered() === true
        }
        onCheckedChange={(value) => {
          column?.setFilterValue(value);
        }}
      />
      {column?.getIsFiltered() && (
        <Badge
          onClick={() => {
            column?.setFilterValue(null);
          }}
          variant="secondary"
          className="tw-rounded-sm tw-px-1 tw-font-sm"
        >
          <Cross2Icon className="tw-h-3 tw-w-3" />
        </Badge>
      )}
    </div>
  );
};

export default ColumnFilterSwitch;

and usage is simple

        {table.getColumn("active") && (
          <ColumnFilterSwitch
            column={table.getColumn("active")}
            title="Active"
          />
        )}

        {table.getColumn("automaticClose") && (
          <ColumnFilterSwitch
            column={table.getColumn("automaticClose")}
            title="Close"
          />
        )}

        {table.getColumn("privateContestAvailable") && (
          <ColumnFilterSwitch
            column={table.getColumn("privateContestAvailable")}
            title="Private contest"
          />
        )}