1

I have three tables:

An adjacency-list table, territories with fields id, alignment_id, three_left, tree_right, depth and deleted_at (omitted from table for brevity, all values null).

id alignent_id tree_left tree_right depth deleted_at
1 1 1 132 0
2 1 2 3 1
3 1 4 31 1
4 1 25 26 2

A table profile_groups with id and reference_name fields.

id reference_name
16 front.field
18 front.manager
19 front.director

A pivot table territory_to_profile_group that relates territories with profile_groups

id territory_id profile_group_id
1 1 19
2 2 18
3 3 18
4 4 16

I am trying to find the deepest ancestor that matches additional criteria:

select  territories.id,
                territory_group_pivot.id as manager_territory_id,
                aggregated_depth
from        territories
/* there could be no matching ancestors */
left join  (
            select  territories.id,
                            min(territories.tree_left) as aggregated_tree_left,
                            min(territories.tree_right) as aggregated_tree_right,
                            min(territories.alignment_id) as aggregated_alignment_id,
                            max(territories.depth) as aggregated_depth
            from        territories 
            join (
                    select  territory_to_profile_group.territory_id
                    from        territory_to_profile_group
                    join        profile_groups on profile_groups.id = territory_to_profile_group.profile_group_id
                    where   profile_groups.reference_name in ('front.manager', 'front.director')
            ) 
            as inner_territory_group_pivot on inner_territory_group_pivot.territory_id = territories.id
            where territories.deleted_at is null
            group by territories.id
) as territory_group_pivot 
/* must match alignment */
on territory_group_pivot.aggregated_alignment_id = territories.alignment_id
/* should be properly outside the current tree */
and territory_group_pivot.aggregated_tree_left <= territories.tree_left 
and territory_group_pivot.aggregated_tree_right >= territories.tree_right

where territories.deleted_at is null


The desired result is this:

id manager_territory_id aggregated_depth
1 1 0
2 2 1
3 3 1
4 3 1
2
  • Is this what you are looking for? ORDER BY ... LIMIT 1
    – Rick James
    Oct 13, 2023 at 18:54
  • If I place the order and limit into left join (select ... order by .. limit 1) i will not get any results at all, as the limit 1 will happen before the join.
    – ioni
    Oct 14, 2023 at 20:35

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.