Как сделать запрос в postgres?

Question

[Jazper]

Есть 2 таблицы

users:
 id SERIAL PRIMARY KEY,
 username VARCHAR(40) NOT NULL,
 rating INTEGER

и

games:
 id SERIAL PRIMARY KEY,
 player1id INTEGER NOT NULL,
 player2id INTEGER NOT NULL,
 score1 INTEGER NOT NULL,
 score2 INTEGER NOT NULL

Как правильно организовать связь таблиц? Как сделать запрос возвращающий примерно такую структуру -

[{ gameid,
 score1,
 score2,
 player1: [{ id, username, rating }],
 player2: [{ id, username, rating }]
}]

Заранее большое спасибо за помощь!

UPD: решающий задачу запрос (спасибо @chlp) -

SELECT games.*, row_to_json(player1) AS player1, row_to_json(player2) AS player2 FROM games
INNER JOIN users player1 ON (games.player1id = player1.id)
INNER JOIN users player2 ON (games.player2id = player2.id)

Answer 1

[Алексей Рытиков]

Примерно так

SELECT games.id AS gameid, player1.id AS player1_id, player2.id AS player2_id, games.score1, games.score2
FROM games
INNER JOIN users player1 ON (games.player1id = users.id)
INNER JOIN users player2 ON (games.player2id = users.id)

Comments

[Jazper]

Большое спасибо за ответ, не совсем так как хотел, но очень сильно помог. :)
Если изменить до такого

SELECT games.*, player1, player2
FROM games
INNER JOIN users player1 ON (games.player1id = player1.id)
INNER JOIN users player2 ON (games.player2id = player2.id)

в итоге приходит вот такое -

[ { id: 1,
    player1id: 1,
    player2id: 3,
    score1: 0,
    score2: 0,
    player1: '(1,player1name,1000)',
    player2: '(2,player2name,1000)' } ]

То есть данные о игроках в виде строки со значениями через запятую. Есть ли возможность организовать это в виде подобъекта с парами ключ-значение? Или все-таки придется вручную указывать player1.username AS player1_name, player1.rating AS player1_rating для всех полей?

[Алексей Рытиков]

Jazper: я не применял, но попробуйте использовать row_to_json www.postgresql.org/docs/9.3/static/functions-json.html

[Jazper]

Алексей Рытиков: Огромнейшее спасибо, все работает как надо.