I have data stored in two tables called objects and object_relationships. It's a simple self referential many to many.

Objects table

words between tables to fix SE formatting

Object_relationships table

Using an initial query like

select * from objects where description like '%birds%' or description like '%lex%' or description like '%john%' 

Returns id's [1, 2, 3, 5]

I then want every edge between these "nodes" specifically:

• 1 - to - 3
• 3 - from - 1
• 2 - from - 5
• 5 - friends - 3

The code I have for the getting the edges using joins but it's wrong because it pulls in new nodes and I can't figure out how to exclude nodes outside the initial query.

I think my approach is wrong because the query does not even consider the parents of the objects. The json build object is to quickly plot the output in any cytoscape compatible viewer

json_build_object(
    'source', base.object_id,
    'target', base.child_id,
    'type', base.child_type
) as edge1,
json_build_object(
    'source', base.child_id,
    'target', base.child2_id,
    'type', base.child2_type
) as edge2 from
(
with parent as (
select
    distinct unnest(array[base.object_id, base.child_id, base.child2_id]) as id
from
    (
    select 
                o.id as object_id,
                o.type,
                or1.child_object_id as child_id,
                or1."type" as child_type
                or2.child_object_id as child2_id,
                or2."type" as child2_type
    from
        objects o
    join object_relationships or1 on
        or1.object_id = o.id
    join objects o1 on
        o1.id = or1.child_object_id
    join object_relationships or2 on or2.object_id = o1.id
    join objects o2 on o2.id = or2.child_object_id
    where
        o.description like '%birds%' or o.description like '%lex%' or o.description like '%john%'
    limit 1) base
limit 100)
select
    o.id as object_id,
    or1.child_object_id as child_id,
    or1."type" as child_type,
    or2.child_object_id as child2_id,
    or2."type" as child2_type
from
    parent p
join objects o on
    o.id = p.id
join object_relationships or1 on
    or1.object_id = o.id
join objects o1 on
    o1.id = or1.child_object_id
join object_relationships or2 on
    or2.object_id = o1.id
join objects o2 on
    o2.id = or2.child_object_id
limit 100) base;

CodePudding user response：

I suggest using arrays which are very convenient and efficient for such tasks.

select object_id, type, child_id
from (
    select ids[i] as object_id, ids[j] as child_id
    from (
        select array_agg(id) as ids
        from objects 
        where description like any('{%birds%, %lex%, %john%}')
        ) s
    cross join generate_subscripts(ids, 1) as i
    cross join generate_subscripts(ids, 1) as j
    where i <> j
    ) s
join object_relationships using(object_id, child_id)
order by object_id, child_id;

See this db<>fiddle for a step-by-step solution that can help to understand the query.