reddit-comment-re-sql-queries.md

I don't think this answer is necessarily going to convince you in any way to use Ash 😅. But it felt worth explaining how it works.

You don't have to leverage the AshPostgres data layer necessarily. Resources & actions etc. can be leveraged just for their interface tooling, and you can make queries with Ecto manually. Most people, even people who are very picky about their SQL, often end up not doing that because AshPostgres makes smart choices for you that are hard to replicate manually. What this is talking about when I refer to it as an insane thing to support isn't that we generate pathological queries, its the complexity in query building that we take on to do something that when you actually see it just "feels like it should work".

The queries we build are tailored to application dev, not necessarily things you should use for analytics & reporting. Ash isn't trying to replace Ecto for all use cases. So in this example, the idea is that if you're loading this information you very likely have a "low-ish" cardinality of in some way pre-filtered data. Building SQL queries changes a lot depending on the context the query will be run in, and what is optimal for running against your entire database for big data work is different than what is optimal for feature dev etc. Given a set up like so:

  # on MyApp.Orgs.Location
  # has_many employees
  aggregates do
    list :employee_identifiers, :employees, :identifier
  end
  
  # on MyApp.Orgs.Employee
  # belongs_to :company
  calculations do
    calculate :identifier, :string, expr("#{company.name} - #{name}")
  end

If you were then to run

  # on MyApp.Orgs.Location
  # has_many employees
  aggregates do
    list :employee_identifiers, :employees, :identifier
  end
  
  # on MyApp.Orgs.Employee
  # belongs_to :company
  calculations do
    calculate :identifier, :string, expr("#{company.name} - #{name}")
  end

You would get the following query

SELECT 
    l0."id", 
    l0."name", 
    l0."company_id", 
    coalesce(s1."employee_identifiers"::text[], $1::text[])::text[]
FROM 
    "locations" AS l0
LEFT OUTER JOIN LATERAL (
    SELECT 
        se0."location_id" AS "location_id", 
        coalesce(
            array_agg(
                (sc1."name"::text || ($2::text || se0."name"::text))::text
            ), 
            $3::text[]
        )::text[] AS "employee_identifiers"
    FROM 
        "public"."employees" AS se0
    LEFT OUTER JOIN 
        "public"."companies" AS sc1 
    ON 
        se0."company_id" = sc1."id"
    WHERE 
        l0."id" = se0."location_id"
    GROUP BY 
        se0."location_id"
) AS s1 ON TRUE;

zachdaniel/file.md