0
I need a collection of objects having as reference the items of an array field in postgres, duplicating the person in the relationships she participates in.
I am using Rails 6 and created the field Relations thus:
t.integer :relations, array: true, null: false, default: []
...
add_index :people, :relations, using: :gin
Generating this table:
id | name | relations |
------------------------|
1 | João | {nil,1,2,3} |
2 | Maria| {nil,1} |
I need to return according to the item in the Relations field array, the nil doesn’t matter.
Sort of like this
[
{id: 1, name: 'João', relation: 1},
{id: 1, name: 'João', relation: 2},
{id: 1, name: 'João', relation: 3},
{id: 2, name: 'Maria', relation: 1}
]
The coarse manner would be the return of that select:
SELECT id, name, '1' as relation FROM people WHERE 1 = ANY (relations)
union
SELECT id, name, '2' as relation FROM people WHERE 2 = ANY (relations)
union
SELECT id, name, '3' as relation FROM people WHERE 3 = ANY (relations);
I thank anyone who can help.