Ash Elixir•2y ago

Fetching descendants in calculation

I've created a listings resource/table which is nestable via a parent_listing_id attribute. In a calculation I want to find all descendants for a listing, what's the best way to do this?

4 Replies

zimt28OP•2y ago

I thought about just translating a query similar to the following to ecto and then passing it the list of record ids in the calculation, but maybe there's a better way?

WITH RECURSIVE parents AS (
    SELECT
        id,
        type,
        0 AS number_of_ancestors,
        ARRAY[id] AS ancestry,
        NULL::uuid AS parent,
        id AS start_of_ancestry
    FROM
        listings
    WHERE
        id in ('11fa5e30-2fff-46ef-ae14-5ea81ecd43c1')
    UNION
    SELECT
        child.id,
        child.type,
        p.number_of_ancestors + 1 AS ancestry_size,
        array_append(p.ancestry, child.id) AS ancestry,
        child.parent_listing_id AS parent,
        coalesce(p.start_of_ancestry, child.parent_listing_id) AS start_of_ancestry
    FROM
        listings child
        INNER JOIN parents p ON p.id = child.parent_listing_id
)
SELECT
    id,
    type,
    number_of_ancestors,
    ancestry,
    parent,
    start_of_ancestry
FROM
    parents;

WITH RECURSIVE parents AS (
    SELECT
        id,
        type,
        0 AS number_of_ancestors,
        ARRAY[id] AS ancestry,
        NULL::uuid AS parent,
        id AS start_of_ancestry
    FROM
        listings
    WHERE
        id in ('11fa5e30-2fff-46ef-ae14-5ea81ecd43c1')
    UNION
    SELECT
        child.id,
        child.type,
        p.number_of_ancestors + 1 AS ancestry_size,
        array_append(p.ancestry, child.id) AS ancestry,
        child.parent_listing_id AS parent,
        coalesce(p.start_of_ancestry, child.parent_listing_id) AS start_of_ancestry
    FROM
        listings child
        INNER JOIN parents p ON p.id = child.parent_listing_id
)
SELECT
    id,
    type,
    number_of_ancestors,
    ancestry,
    parent,
    start_of_ancestry
FROM
    parents;

barnabasj•2y ago

i have this in one of my resources

calculate :destinations,
              {:array, :string},
              expr(
                fragment(
                  """
                  SELECT * FROM (
                  WITH RECURSIVE parent_destinations AS (
                      SELECT dc.destination1_id AS destination1_id,
                              dc.destination2_id AS destination2_id
                      FROM destination_connection dc
                      WHERE dc.destination1_id = ?
                      UNION
                      SELECT dc.destination1_id AS destination1_id,
                              dc.destination2_id AS destination2_id
                      FROM destination_connection dc
                                JOIN parent_destinations ds ON dc.destination1_id = ds.destination2_id)
                  SELECT DISTINCT ARRAY_CAT(ARRAY [?],
                                            ARRAY_AGG(distinct pd.destination2_id))
                  FROM parent_destinations pd) d
                  """,
                  closest_destination_id,
                  closest_destination_id
                )
              ) do
    end

calculate :destinations,
              {:array, :string},
              expr(
                fragment(
                  """
                  SELECT * FROM (
                  WITH RECURSIVE parent_destinations AS (
                      SELECT dc.destination1_id AS destination1_id,
                              dc.destination2_id AS destination2_id
                      FROM destination_connection dc
                      WHERE dc.destination1_id = ?
                      UNION
                      SELECT dc.destination1_id AS destination1_id,
                              dc.destination2_id AS destination2_id
                      FROM destination_connection dc
                                JOIN parent_destinations ds ON dc.destination1_id = ds.destination2_id)
                  SELECT DISTINCT ARRAY_CAT(ARRAY [?],
                                            ARRAY_AGG(distinct pd.destination2_id))
                  FROM parent_destinations pd) d
                  """,
                  closest_destination_id,
                  closest_destination_id
                )
              ) do
    end

Not sure if it is better though 😉

frankdugan3•2y ago

I think it's better at least in that you can look at the resource and know the current state of that SQL statement instead of digging through migrations to figure it out.

ZachDaniel•2y ago

I might suggest using a manual relationship it would look similar, but if you use the ash postgres features for manual relationships then you'll be able to filter/join on it/use it in other calculations

Gaming

Programming

Fetching descendants in calculation

Did you find this page helpful?