current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 0 down vote favorite

can anyone help me with querying many to many relation tables in postgres?

i have tables:

> 1.exercise(id,name)
> 2.tag(id,label)
> 3.tag_in_exercise(id,exercise_id,tag_id)

let say, that we have one exercise bonded with two tags via tag_in_exercise

when using query :

select e.id,t.label from exercise e 
left join tag_in_exercise te on e.id=te.exercise_id  
left join tag t on te.tag_id=t.id

i will receive json 

[ { id: 1,
    label: 'basic1' },
  { id: 1,
    label: 'basic2' }]

but i want to receive it as nested json

[ { id: 1,
    tags:[ {'basic1'},{'basic2'} ]
}]

is it possible to get that by using standart postgresql queries or i need to use some ORM?

or if exists another solution please let me know,

thanks

share|improve this question
    
Nothing in your query tells PostgreSQL to return JSON. Your node driver must be converting the result to JSON. – Mark Stosberg Nov 9 '14 at 12:18
up vote 0 down vote accepted

PostgreSQL does not return the JavaScript object you have posted. Your node driver is converting an array of arrays returned by PostgreSQL, which the driver is converting to JavaScript objects.

However, you can have PostgreSQL return a structure which I suspect will be converted how you wish by using array_agg.

Try this:

SELECT e.id,array_agg(t.label) AS label FROM exercise e LEFT JOIN tag_in_exercise te on e.id=te.exercise_id LEFT JOIN tag t on te.tag_id=t.id GROUP BY e.id;

You will get a raw PostgreSQL result in the structure you want, which hopefully the driver will translate as you intend:

id | label
----+----------------- 1 | {basic1,basic2} 2 | {NULL}

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.