mariaDB json_object/json_arrayagg
Есть запрос, в котором вытаскиваю данные о игроке, у игрока есть 2 вещи и 1 противник в отдельных таблицах
pool.query(`SELECT json_object(
'game_id', G.id,
'game_mode', G.mode,
'game_submode', G.submode,
'game_maxplayers', G.maxplayers,
'game_createdAt', G.createdAt,
'game_game_round', G.game_round,
'player_nickname', C.nickname,
'player_avatar', C.avatar,
'player_vip', C.vip,
'player_domain', C.domain,
'player_id', A.player_id,
'player_race', A.race,
'player_class', A.class,
'player_level', A.level,
'player_cash', A.cash,
'player_points', A.points,
'player_battles', A.battles,
'player_battles_bosses', A.battles_bosses,
'player_status', A.player_status,
'player_position', B.player_position,
'player_action', B.player_action,
'player_made_action', B.made_action,
'player_action_name', B.action,
'player_dices', B.dices,
'player_time_action', B.time_action,
'items', json_arrayagg(
json_object(
'item_type', E.type,
'item_subtype', E.subtype,
'item_name', E.name,
'item_description', E.description,
'item_image', E.image,
'item_level', E.level,
'item_price', E.price
)
),
'enemies', json_arrayagg(
json_object(
'enemy_type', H.type,
'enemy_name', H.name
)
)
) players
FROM games G
LEFT JOIN game_players A ON A.game_id = G.id
LEFT JOIN game_events B ON B.player_id = A.player_id
LEFT JOIN users C ON C.id = A.player_id
LEFT JOIN game_items D ON D.player_id = A.player_id
LEFT JOIN items E ON E.id = D.item_id
LEFT JOIN game_enemies F ON F.player_id = A.player_id
LEFT JOIN enemies H ON H.id = F.enemy_id
WHERE G.gs_token = ? GROUP BY A.player_id
Получаю такой ответ
RowDataPacket {
players: '{
"game_id": 22,
"game_mode": "0",
"game_submode": "1",
"game_maxplayers": "3",
"game_createdAt": "2022-09-06 19:18:11",
"game_game_round": 396,
"player_nickname": "Moonbeam",
"player_avatar": "/img/CuqTPZzRPIE4qKEu",
"player_vip": 0,
"player_domain": null,
"player_id": 1,
"player_race": "elf",
"player_class": "warrior",
"player_level": 11,
"player_cash": 0,
"player_points": 0,
"player_battles": 0,
"player_battles_bosses": 0,
"player_status": 1,
"player_position": 39,
"player_action": 1,
"player_made_action": 0,
"player_action_name": "battle",
"player_dices": "[6,4]",
"player_time_action": "2022-09-26 18:44:40",
"items": [
{"item_type": "armor",
"item_subtype": "head",
"item_name": "Мотоциклетный шлем",
"item_description": "Описание",
"item_image": "/img/items/armor/head/helmet.svg",
"item_level": 3,
"item_price": 800
},
{"item_type": "armor",
"item_subtype": "body",
"item_name": "Жилет",
"item_description": "Описание",
"item_image": "",
"item_level": 6,
"item_price": 1800}],
"enemies": [
{"enemy_type": "normal",
"enemy_name": "Npc1"
},
{"enemy_type": "normal",
"enemy_name": "Npc1"
}]
}'
}
В запросе есть 2 json_arrayagg - items и enemies Если привязано 2 и более вещей, то дублируются enemies (Противники) приходит 2 раза Npc1 и тоже самое с вещами, если 2 противника и к примеру 1 вещь, то эта вещь дублируется, как их можно сделать "независимыми" друг от друга, к примеру если 1 вещь и 2 противника то вывести
"items": [
{"item_type": "armor",
"item_subtype": "head",
"item_name": "Мотоциклетный шлем",
"item_description": "Описание",
"item_image": "/img/items/armor/head/helmet.svg",
"item_level": 3,
"item_price": 800
}],
"enemies": [
{"enemy_type": "normal",
"enemy_name": "Npc1"
},
{"enemy_type": "normal",
"enemy_name": "Npc2"
}]
Ответы (1 шт):
Автор решения: Wallo
→ Ссылка
Спасибо Akina за наводку), необходимые данные правильно приходят, правильно ли я реализовал и не скажется ли это на производительности из за таких вложенностей?
pool.query(`SELECT json_object(
'game_id', G.id,
'game_mode', G.mode,
'game_submode', G.submode,
'game_maxplayers', G.maxplayers,
'game_createdAt', G.createdAt,
'game_game_round', G.game_round,
'player_nickname', C.nickname,
'player_avatar', C.avatar,
'player_vip', C.vip,
'player_domain', C.domain,
'player_id', A.player_id,
'player_race', A.race,
'player_class', A.class,
'player_level', A.level,
'player_cash', A.cash,
'player_points', A.points,
'player_battles', A.battles,
'player_battles_bosses', A.battles_bosses,
'player_status', A.player_status,
'player_position', B.player_position,
'player_action', B.player_action,
'player_made_action', B.made_action,
'player_action_name', B.action,
'player_dices', B.dices,
'player_time_action', B.time_action
) player,
(SELECT json_object(
'items_all', json_arrayagg(
json_object(
'item_type', items.type,
'item_subtype', items.subtype,
'item_name', items.name,
'item_description', items.description,
'item_image', items.image,
'item_level', items.level,
'item_price', items.price,
'item_active', game_items.item_active,
'item_slot', game_items.item_slot
)
)
)
FROM game_items LEFT JOIN items ON items.id = game_items.item_id WHERE game_items.player_id = A.player_id) items,
(SELECT json_object(
'enemies_all', json_arrayagg(
json_object(
'enemy_type', enemies.type,
'enemy_name', enemies.name,
'enemy_description', enemies.description,
'enemy_image', enemies.image,
'enemy_level', enemies.level,
'enemy_undead', enemies.undead,
'enemy_damage', enemies.damage,
'enemy_buffs', enemies.buffs,
'enemy_rewards', enemies.rewards
)
)
)
FROM game_enemies LEFT JOIN enemies ON enemies.id = game_enemies.enemy_id WHERE game_enemies.player_id = A.player_id) enemies
FROM games G
LEFT JOIN game_players A ON A.game_id = G.id
LEFT JOIN game_events B ON B.player_id = A.player_id
LEFT JOIN users C ON C.id = A.player_id
WHERE G.gs_token = ? GROUP BY A.player_id`,[gs_token], (error,result) => {