Outerfaces Ecto API Query System

A comprehensive, declarative query building and serialization system for Phoenix/Ecto applications that supports complex filtering, sorting, pagination, and deep association preloading through JSON API requests.

Architecture Overview

The system consists of several interconnected modules that work together to provide a clean, declarative API for building complex database queries:

Frontend JSON Request
    ↓
QueryEngine (orchestrator)
    ↓
QueryBuilder (parses & builds query)
    ↓
QueryJoiner (manages associations)
    ↓
QueryFilter/QueryExpressor (applies filters)
    ↓
QueryAssociator (preloads & serializes)
    ↓
QuerySerializer (transforms to JSON)
    ↓
Clean JSON Response

Core Modules

QueryEngine

The main entry point that orchestrates the entire query pipeline. Provides two main functions:

• all/7 - For index endpoints with pagination
• get/5 - For show endpoints

QueryBuilder

Parses JSON query parameters and builds Ecto queries using declarative filter and sort specifications. Handles a sophisticated two-phase filtering process:

1. Explicit Filters: Applies filters explicitly provided in the JSON request
2. Default Filters: Applies default/fallback filters for any missing filter specifications

QueryFilter

Applies filters to queries with support for:

• Simple field filtering
• Deep association filtering (up to 21 levels deep)
• Conditional operators based on values
• Default fallback filters with multiple fallback strategies

QueryExpressor

Generates dynamic Ecto query expressions for different binding arities. Handles the complex task of building type-safe dynamic queries with proper variable binding.

QueryAssociator

Manages preloading of associations and delegates serialization to individual schemas.

QuerySerializer

Provides a macro for schemas to define their serialization behavior with hooks for custom field processing.

Usage

1. Define Filter Specifications

# In your controller or filter module
@index_filters [
  BaseFilters.is_active(),  # Reusable base filter with default
  BaseFilters.organization_id(),
  {
    :custom_field,
    {
      QueryFilter,
      :by_field,
      :field_name,
      :==,
      _allow_nil = false,
      _default_or_fallback_filter = nil
    }
  },
  {
    :association_filter,
    {
      QueryFilter,
      :by_association_field,
      [:user, :profile],  # Association path
      :name,
      :==,
      _allow_nil = false,
      _default_or_fallback_filter = nil
    }
  }
]

2. Define Sort Specifications

@sort_specs [
  BaseSorts.created_at_desc_default(),  # Default sort
  BaseSorts.id_asc_default(),
  {
    :custom_sort,
    {
      QuerySort,
      :by_association_field,
      [:user],
      :name,
      :asc,
      _default = false
    }
  }
]

3. Define Preload Specifications

@index_preloads [
  :simple_association,
  {:nested_association, [:deep_nested]},
  {
    :complex_association,
    [
      nested_field: [:deep_field],
      another_nested: [
        :sound_generation_profile,
        :audio_control_mode
      ]
    ]
  }
]

4. Use in Controllers

def index(conn, params) do
  QueryEngine.all(
    Repo,
    MySchema,
    @index_preloads,
    @index_filters,
    @sort_specs,
    params
  )
  |> then(fn {:ok, response} -> json(conn, response) end)
end

def show(conn, %{"id" => id}) do
  QueryEngine.get(Repo, MySchema, id, @show_preloads)
  |> then(fn {_status, response} -> json(conn, response) end)
end

5. Add Serialization to Schemas

defmodule MyApp.MySchema do
  use Ecto.Schema
  use ToadAudioOrchestratorWeb.Api.QuerySerializer

  # Override serialization behavior
  def serializer_skip_fields(_opts), do: [:internal_field, :password_hash]

  def do_serialize_field(:created_at, value, _opts) do
    # Custom date formatting
    DateTime.to_iso8601(value)
  end
  
  def do_serialize_field(_field, value, _opts), do: value
end

Frontend Usage

JSON Query Format

const queryParams = {
  query: JSON.stringify({
    filters: {
      is_active: true,
      organization_id: 123,
      "user.name": "John"  // Association filtering
    },
    sort: ["created_at:desc", "name:asc"]
  }),
  page: 1,
  per_page: 25
}

fetch(`/api/resources?${new URLSearchParams(queryParams)}`)

Response Format

{
  status: 200,
  results: {
    data: [
      {
        id: 1,
        name: "Resource 1",
        schema: "MyApp.MySchema",
        user: {
          id: 1,
          name: "John",
          schema: "MyApp.User"
        }
      }
    ],
    page_info: {
      current_page: 1,
      total_pages: 5,
      total_count: 123,
      per_page: 25
    },
    schema: "MyApp.MySchema"
  }
}

Filter Specification Format

{
  :filter_key,  # Key that appears in JSON filters
  {
    Module,           # Module containing filter function
    :function_name,   # Function to call
    :field_or_binding_list,  # Field name or association path
    :operator,        # :==, :!=, :>, :<, etc. or {:truthy_op, :falsy_op}
    allow_nil,        # boolean - whether to apply filter with nil values
    default_value     # Default value or {Module, :func, args} for default filter
  }
}

Sort Specification Format

{
  :sort_key,  # Key that appears in JSON sort array
  {
    Module,           # Module containing sort function  
    :function_name,   # Function to call
    binding_list,     # Association path ([] for direct field)
    :field_name,      # Field to sort on
    :direction,       # :asc or :desc
    is_default        # boolean - whether this is a default sort
  }
}

Advanced Features

Default Filter Mechanism

The system automatically applies fallback filters when they're not explicitly provided in requests. This enables tenant scoping, soft deletes, security defaults, and better user experience.

Conditional Operators (Most Common)

The most powerful feature allows operators to change based on the filter value's truthiness:

def is_active() do
  {
    :is_active,
    {
      QueryFilter,
      :by_field,
      :archived_at,
      {:is_nil, :not_nil},    # Conditional operators
      false,                   # Don't filter on explicit nil values  
      true                     # Default to active (true)
    }
  }
end

Behavior:

• is_active: true → WHERE archived_at IS NULL (active records)
• is_active: false → WHERE archived_at IS NOT NULL (archived records)
• is_active: null → Filter skipped (allow_nil = false)
• Request without is_active → WHERE archived_at IS NULL (defaults to active)

Default Filter Processing

1. Explicit Filters First: Processes filters provided in the JSON request
2. Default Filters Second: Applies defaults for missing filters only

Override Prevention: Defaults only apply when the filter key is completely missing:

# {"filters": {"is_active": null}} → Filter skipped (explicit null)
# {"filters": {}} → Default applied (key missing)

Default Value Types

Literal Values (most common):

{:tenant_scoped, {QueryFilter, :by_field, :tenant_id, :==, false, 123}}
# When not provided, filters by tenant_id = 123

Function Calls:

{:tenant_id, {QueryFilter, :by_field, :tenant_id, :==, false, {MyModule, :current_tenant_id, []}}}
# Calls MyModule.current_tenant_id() to get default value

Nil with allow_nil flag:

{:optional, {QueryFilter, :by_field, :field, :==, true, nil}}
# If allow_nil = true: applies filter with nil value
# If allow_nil = false: skips filter entirely

Conditional Operators

# Operator changes based on filter value
{:is_active, {QueryFilter, :by_field, :archived_at, {:is_nil, :not_nil}, false, true}}
# When is_active: true -> uses :is_nil
# When is_active: false -> uses :not_nil
# When not provided -> defaults to true, uses :is_nil

Default Filters

# Applied when filter not explicitly provided
{:tenant_scoped, {QueryFilter, :by_field, :tenant_id, :==, false, current_tenant_id}}

Deep Association Filtering

# Filter by nested association fields
{:user_profile_type, {QueryFilter, :by_association_field, [:user, :profile], :type, :==, false, nil}}