Automatic Where

FraiseQL automatically generates filter input types for your queries, providing powerful filtering capabilities without manual implementation.

Overview

When you enable auto_params, FraiseQL generates:

• Filter input types for each field
• Logical operators (_and, _or, _not)
• Type-specific comparison operators

Enabling Auto-Params

@fraiseql.query
def users(where: UserWhereInput | None = None) -> list[User]:
    """Query users with automatic filtering."""
    return fraiseql.config(sql_source="v_user", auto_params={"where": True})

Generated Filter Types

For this type:

from fraiseql.scalars import ID, DateTime, Email, CountryCode, Json

@fraiseql.type
class User:
    id: ID
    name: str
    email: Email                 # Rich type - gets specialized filter
    country: CountryCode         # Rich type - gets specialized filter
    age: int
    is_active: bool
    created_at: DateTime
    tags: list[str]
    metadata: Json

FraiseQL generates:

input UserWhereInput {
    id: IDFilter
    name: StringFilter
    email: EmailFilter           # Rich type filter
    country: CountryCodeFilter   # Rich type filter
    age: IntFilter
    is_active: BooleanFilter
    created_at: DateTimeFilter
    tags: StringArrayFilter
    metadata: JsonFilter

    _and: [UserWhereInput!]
    _or: [UserWhereInput!]
    _not: UserWhereInput
}

input IDFilter {
    _eq: ID
    _neq: ID
    _in: [ID!]
    _nin: [ID!]
    _is_null: Boolean
}

input StringFilter {
    _eq: String
    _neq: String
    _in: [String!]
    _nin: [String!]
    _like: String
    _ilike: String
    _regex: String
    _is_null: Boolean
}

input EmailFilter {
    _eq: Email
    _neq: Email
    _in: [Email!]
    _nin: [Email!]
    _domain_eq: String           # e.g., "company.com"
    _domain_endswith: String     # e.g., ".edu"
    _is_null: Boolean
}

# ... additional filter types

Using Filters

Basic Equality

query {
    users(where: { email: { _eq: "john@example.com" } }) {
        id
        name
    }
}

Rich Type Operators

Use domain-specific operators for rich types:

query {
    users(where: {
        email: { _domain_endswith: ".company.com" }  # Email domain suffix filter
        country: { _continent_eq: "EU" }              # Country continent filter
    }) {
        id
        name
        email
        country
    }
}

Multiple Conditions

Conditions at the same level are ANDed:

query {
    users(where: {
        is_active: { _eq: true }
        age: { _gte: 18 }
    }) {
        id
        name
    }
}

Try It Yourself

Execute queries below to see automatic where filtering in action:

Automatic Where Example

Filter users by active status and age range. Modify the query to try different filters!

Loading Apollo Sandbox...

This sandbox uses Apollo Sandbox (the same GraphQL IDE as fraiseql serve). Your queries execute against the endpoint below. No data is sent to Apollo. Learn more about privacy →

OR Conditions

query {
    users(where: {
        _or: [
            { email: { _ilike: "%@company.com" } }
            { is_active: { _eq: true } }
        ]
    }) {
        id
        name
    }
}

NOT Conditions

query {
    users(where: {
        _not: { status: { _eq: "banned" } }
    }) {
        id
        name
    }
}

Complex Queries

query {
    users(where: {
        _and: [
            { is_active: { _eq: true } }
            {
                _or: [
                    { role: { _eq: "admin" } }
                    {
                        _and: [
                            { role: { _eq: "user" } }
                            { verified: { _eq: true } }
                        ]
                    }
                ]
            }
        ]
    }) {
        id
        name
        role
    }
}

SQL Mapping

FraiseQL translates filters to SQL against your views:

# Input
query {
    users(where: {
        is_active: { _eq: true }
        email: { _ilike: "%@example.com" }
        age: { _gte: 21, _lt: 65 }
    }) { id }
}

PostgreSQL

-- Resulting query
SELECT data FROM v_user
WHERE (data->>'is_active')::boolean = true
  AND data->>'email' ILIKE '%@example.com'
  AND (data->>'age')::int >= 21
  AND (data->>'age')::int < 65

MySQL

-- Resulting query
SELECT data FROM v_user
WHERE JSON_UNQUOTE(JSON_EXTRACT(data, '$.is_active')) = true
  AND JSON_UNQUOTE(JSON_EXTRACT(data, '$.email')) LIKE '%@example.com'
  AND CAST(JSON_UNQUOTE(JSON_EXTRACT(data, '$.age')) AS SIGNED) >= 21
  AND CAST(JSON_UNQUOTE(JSON_EXTRACT(data, '$.age')) AS SIGNED) < 65

SQLite

-- Resulting query
SELECT data FROM v_user
WHERE json_extract(data, '$.is_active') = true
  AND json_extract(data, '$.email') LIKE '%@example.com'
  AND CAST(json_extract(data, '$.age') AS INTEGER) >= 21
  AND CAST(json_extract(data, '$.age') AS INTEGER) < 65

SQL Server

-- Resulting query
SELECT data FROM dbo.v_user
WHERE CAST(JSON_VALUE(data, '$.is_active') AS BIT) = 1
  AND JSON_VALUE(data, '$.email') LIKE '%@example.com'
  AND CAST(JSON_VALUE(data, '$.age') AS INT) >= 21
  AND CAST(JSON_VALUE(data, '$.age') AS INT) < 65

Field-Specific Operators

String Fields

Operator	SQL	Example
_eq	=	Exact match
_neq	<>	Not equal
_in	IN (...)	In list
_nin	NOT IN (...)	Not in list
_like	LIKE	Pattern (case-sensitive)
_ilike	ILIKE	Pattern (case-insensitive)
_nlike	NOT LIKE	Not pattern
_regex	~	Regex match
_iregex	~*	Regex (case-insensitive)
_nilike	NOT ILIKE	Not pattern (case-insensitive, PostgreSQL only)
_nregex	!~	Not regex (PostgreSQL only)
_niregex	!~*	Not regex, case-insensitive (PostgreSQL only)
_contains	LIKE '%…%'	Contains substring
_icontains	ILIKE '%…%'	Contains substring (case-insensitive, PostgreSQL only)
_startswith	LIKE '…%'	Starts with
_istartswith	ILIKE '…%'	Starts with (case-insensitive, PostgreSQL only)
_endswith	LIKE '%…'	Ends with
_iendswith	ILIKE '%…'	Ends with (case-insensitive, PostgreSQL only)
_is_null	IS NULL	Null check

Numeric Fields

Operator	SQL	Example
_eq	=	Equal
_neq	<>	Not equal
_gt	>	Greater than
_gte	>=	Greater or equal
_lt	<	Less than
_lte	<=	Less or equal
_in	IN (...)	In list
_nin	NOT IN (...)	Not in list
_is_null	IS NULL	Null check

Array Fields

Operator	SQL	Example
_contains	@> ARRAY[...]	Contains element
_contained_in	<@ ARRAY[...]	Contained in
_has_any	&& ARRAY[...]	Has any element
_has_all	@> ARRAY[...]	Has all elements
_is_empty	= '{}'	Is empty array

JSON Fields

Operator	SQL	Example
_contains	@>	Contains JSON
_contained_in	<@	Contained in JSON
_has_key	?	Has key
_has_keys_any	?|	Has any key
_has_keys_all	?&	Has all keys

Rich Type Filters

FraiseQL’s rich scalar types automatically generate specialized filter types with domain-appropriate operators. These go far beyond basic string matching—each type gets operators tailored to its semantics.

Using Rich Types

from fraiseql.scalars import (
    Email, PhoneNumber, URL, CountryCode, Coordinates,
    VIN, IBAN, CurrencyCode, Money, SemanticVersion,
    IPAddress, AirportCode, TrackingNumber, MimeType
)

@fraiseql.type
class Customer:
    id: ID
    email: Email                  # EmailFilter
    phone: PhoneNumber            # PhoneNumberFilter
    country: CountryCode          # CountryCodeFilter
    location: Coordinates         # CoordinatesFilter
    vehicle_vin: VIN              # VINFilter
    bank_account: IBAN            # IBANFilter
    preferred_currency: CurrencyCode  # CurrencyCodeFilter

Generated Rich Type Filters

input CustomerWhereInput {
    id: IDFilter
    email: EmailFilter              # Domain, local part
    phone: PhoneNumberFilter        # Country code, type
    country: CountryCodeFilter      # Continent, EU, Schengen
    location: CoordinatesFilter     # Geospatial radius/bounds
    vehicle_vin: VINFilter          # WMI, model year
    bank_account: IBANFilter        # Country, validity
    preferred_currency: CurrencyCodeFilter  # Region, decimal places

    _and: [CustomerWhereInput!]
    _or: [CustomerWhereInput!]
    _not: CustomerWhereInput
}

Rich Type Filter Examples

Email Filtering:

query {
    # All .edu domain emails
    students(where: {
        email: { _domain_endswith: ".edu" }
    }) { id email }

    # Specific company domain
    employees(where: {
        email: { _domain_eq: "acme.com" }
    }) { id email department }

    # Emails from multiple approved domains
    partners(where: {
        email: { _domain_in: ["acme.com", "globex.com"] }
    }) { id email }
}

Country/Region Filtering:

query {
    # Asian market expansion
    leads(where: {
        country: { _continent_eq: "AS" }
    }) { id company country }

    # EU member states
    customers(where: {
        country: { _in_eu: true }
    }) { id name country }

    # Schengen area
    travelers(where: {
        country: { _in_schengen: true }
    }) { id name country }
}

Geospatial Filtering:

query {
    # Stores within 50km of NYC
    stores(where: {
        location: {
            _distance_within: { lat: 40.7128, lng: -74.006, radius_km: 50 }
        }
    }) { id name location }

    # Locations within a bounding box
    venues(where: {
        location: {
            _within_bounding_box: { north: 41.0, south: 40.5, east: -73.7, west: -74.3 }
        }
    }) { id name location }
}

VIN Filtering:

query {
    # Toyota WMI codes
    vehicles(where: {
        vin: { _wmi_eq: "JT2" }
    }) { id vin }

    # Vehicles by WMI code list
    vehicles(where: {
        vin: { _wmi_in: ["1HG", "1FA", "WBA"] }
    }) { id vin }

    # Specific model year
    vehicles(where: {
        vin: { _model_year_eq: 2022 }
    }) { id vin model_year }
}

Financial Filtering:

query {
    # German IBANs
    accounts(where: {
        iban: { _country_eq: "DE" }
    }) { id iban holder_name }

    # IBANs from multiple countries
    accounts(where: {
        iban: { _country_in: ["DE", "FR", "NL"] }
    }) { id iban country }

    # USD transactions
    transactions(where: {
        money: { _currency_eq: "USD" }
    }) { id amount }
}

Network Filtering:

query {
    # Private IP addresses
    connections(where: {
        ip: { _is_private: true }
    }) { id ip path }

    # IPv4 vs IPv6
    requests(where: {
        ip: { _version_eq: 4 }
    }) { id ip user }
}

Version Filtering:

query {
    # Specific major version
    packages(where: {
        version: { _major_eq: 2 }
    }) { id name version }

    # Packages with a prerelease tag
    releases(where: {
        version: { _has_prerelease: false }
    }) { id version published_at }
}

Transportation Filtering:

query {
    # FedEx shipments
    shipments(where: {
        tracking: { _carrier_eq: "FedEx" }
    }) { id tracking status }

    # Flights operated by a specific airline
    flights(where: {
        flight_number: { _airline_eq: "AA" }
    }) { id flight_number departure }
}

Available Rich Type Filters

Category	Type	Key Operators
Contact	Email	_domain_eq, _domain_in, _domain_endswith, _local_part_startswith
	PhoneNumber	_country_code_eq, _country_code_in, _type_eq
	URL	_protocol_eq, _host_eq, _path_startswith
Geography	CountryCode	_continent_eq, _region_eq, _in_eu, _in_schengen
	Coordinates	_distance_within, _within_bounding_box, _within_polygon
	PostalCode	_country_eq
Financial	CurrencyCode	_region_eq, _decimal_places_eq
	Money	_currency_eq
	IBAN	_country_eq, _country_in
Identifiers	VIN	_wmi_eq, _wmi_in, _country_eq, _model_year_eq
	SemanticVersion	_major_eq, _minor_eq, _patch_eq, _has_prerelease
	TrackingNumber	_carrier_eq
Transport	AirportCode	_country_eq, _is_major
	FlightNumber	_airline_eq
Content	MimeType	_type_eq, _subtype_eq, _charset_eq
	Color	_hex_eq
Network	IPAddress	_version_eq, _is_private
	Port	_service_eq, _is_well_known, _is_registered
Database	LTree	_ancestor_eq, _descendant_eq, _depth_eq
	DateRange	_duration_gte, _starts_after, _ends_before

For complete filter definitions, see Query Operators.

Customizing Filters

Exclude Fields

@fraiseql.type
class User:
    id: ID
    name: str
    password_hash: Annotated[str, fraiseql.field(filterable=False)]

Custom Filter Types

@fraiseql.filter_type
class EmailFilter(StringFilter):
    """Custom filter for email fields."""
    _domain: str  # Filter by domain

@fraiseql.type
class User:
    email: Annotated[str, fraiseql.field(filter_type=EmailFilter)]

Limit Operators

@fraiseql.type
class User:
    # Only allow equality checks
    email: Annotated[str, fraiseql.field(
        filter_operators=["_eq", "_neq", "_in"]
    )]

Nested Object Filtering

Filter on related entities:

@fraiseql.type
class Post:
    id: ID
    title: str
    author: User

@fraiseql.query
def posts(where: PostWhereInput | None = None) -> list[Post]:
    return fraiseql.config(sql_source="v_post", auto_params={"where": True})

query {
    posts(where: {
        author: {
            is_active: { _eq: true }
            email: { _ilike: "%@company.com" }
        }
    }) {
        id
        title
    }
}

Performance Considerations

Indexing

Create indexes for commonly filtered fields:

-- JSONB field indexes
CREATE INDEX idx_tv_user_email ON tv_user ((data->>'email'));
CREATE INDEX idx_tv_user_active ON tv_user ((data->>'is_active'));

-- Composite for common filter combinations
CREATE INDEX idx_tv_user_active_email ON tv_user (
    (data->>'is_active'),
    (data->>'email')
);

Avoid Non-Indexed Filters

# Good: Uses indexed field
{ email: { _eq: "..." } }

# Slower: Full table scan
{ bio: { _ilike: "%keyword%" } }

Limit Complex Conditions

# Complex nested conditions can be slow
{
    _or: [
        { _and: [...] }
        { _or: [...] }
    ]
}

Combining with Other Parameters

@fraiseql.query
def posts(
    where: PostWhereInput | None = None,
    limit: int = 20,
    offset: int = 0,
    order_by: PostOrderByInput | None = None
) -> list[Post]:
    return fraiseql.config(
        sql_source="v_post",
        auto_params={
            "where": True,
            "limit": True,
            "offset": True,
            "order_by": True
        }
    )

query {
    posts(
        where: { is_published: { _eq: true } }
        order_by: { created_at: DESC }
        limit: 10
        offset: 0
    ) {
        id
        title
    }
}

Type Safety

Filter types are validated at compile time:

# Error: age expects IntFilter, not StringFilter
query {
    users(where: { age: { _ilike: "twenty" } }) { id }
}
# → Error: Field 'age' does not accept operator '_ilike'

Best Practices

Index Filtered Fields

CREATE INDEX idx_posts_published ON tv_post ((data->>'is_published'))
WHERE data->>'is_published' = 'true';

Use Specific Operators

# Better: Specific operator
{ email: { _eq: "user@example.com" } }

# Slower: Pattern matching
{ email: { _like: "user@example.com" } }

Combine with Pagination

Always paginate filtered results:

query {
    users(
        where: { is_active: { _eq: true } }
        limit: 20  # Always limit
    ) { id }
}

Project-Level Defaults

You can control which auto-parameters are enabled on a per-query basis via the auto_params dict on each decorator. Pass True to enable all parameters at once, or specify individual keys:

# Enable all auto-params (where, limit, offset, order_by)
@fraiseql.query
def posts(
    where: PostWhereInput | None = None,
    limit: int = 20,
    offset: int = 0,
    order_by: PostOrderByInput | None = None
) -> list[Post]:
    return fraiseql.config(sql_source="v_post", auto_params=True)

# Enable only where filtering for this query
@fraiseql.query
def users(where: UserWhereInput | None = None) -> list[User]:
    return fraiseql.config(sql_source="v_user", auto_params={"where": True})

REST Query Parameters

Auto-generated WHERE filters are also available via REST query parameters. For example, if a field name has a contains filter, you can use GET /rest/v1/users?name_contains=alice (the default path is /rest/v1; configure with [rest] path) — equivalent to users(where: { name: { contains: "alice" } }) in GraphQL.

Next Steps

• Rich Filters — Advanced filtering with semantic types
• Operators — Complete operator reference
• Pagination — Limit and offset
• Performance — Query optimization