Как составить sql запрос для выборки статей и комментариев из связанных таблиц?

Question

[Sunsh1ne]

Здравствуйте.
Есть такая структура БД MySQL:
Таблица статей(story)
- id
- text
- author_id
...
Таблица комментариев(story_comments)
- id
- s_id (id статьи)
- text
...
Таблицы связаны
Мне необходимо выбрать статьи и попутно комментарии к ним.

У меня вышло как-то так:

SELECT 
story.id s_id, 
story.author_id s_author_id, 
story.text s_text,
 ...
story_comments.id comment_id, 
story_comments.text comment_text
 ... 
FROM (SELECT * FROM story WHERE story.id IN ($ids)) story 
LEFT JOIN story_comments ON story.id = story_comments.s_id

Первый раз я написал так:

SELECT 
story.id s_id,
story.author_id s_author_id, 
story.text s_text,
 ...
story_comments.id comment_id, 
story_comments.text comment_text
 ... 
FROM story 
LEFT JOIN story_comments ON story.id = story_comments.s_id 
WHERE story.id IN ($ids)

Вопроса два:
1. Какой из верхних запросов быстрее и меньше нагружает БД
2. Все так делают или я неправильно выбираю комментарии? Мне возвращается массив результатов типа: [данные первой статьи, первого комментария],[данные первой статьи, второго комментария], ...
Можно ли как-то сделать, чтобы возвращалось:[данные первой статьи,[первый комментарий, второй, ...]],[вторая статья,[...]]

Answer 1

[nozzy]

Должно все быть в одну строку,
в комментариях разделитель '||'.
Не проверял.

select 
s.id,
s.text,
ifnull(c.comments, "нет комментариев")
from story s
left join 
(
	select 
	s_id,
	group_concat(text SEPARATOR '||') as comments
	from story_comments
	group by s_id
) c on c.s_id = s.id

Comments

[Sunsh1ne]

Спасибо, почти правильно)

ifnull(c.comments, "нет комментариев") AS comments
from story s

Answer 2

[Rsa97]

В данном случае правильнее выбирать отдельным запросом саму статью, отдельным - комментарии к ней. Иначе статья (а это обычно несколько килобайт текста) будет передаваться вместе с каждым комментарием, резко увеличивая трафик.

Comments

[Sunsh1ne]

Можно ли как-то сделать, чтобы возвращалось:[данные первой статьи,[первый комментарий, второй, ...]],[вторая статья,[...]] ?

[Rsa97]

Sunsh1ne: Можно через CONCAT и GROUP_CONCAT, но потом могут быть сложности с обратным разделением, в статье могут быть и запятые, и скобки и кавычки...

Answer 3

[igruschkafox]

ответ на 1 вопрос:
По теории быстрее второй вариант (тот который у Вас помечен как - "Первый раз я написал так"
потому что:
- SELECT * FROM
так писать не стоит - Звездочка всегда работает медленне чем перечисление колонок, не намного :) но все же
- просто Left join будет работать быстрее, чем подзапрос - особенно если есть необходимые индексы

ответ на 2 вопрос:
"чтобы возвращалось:[данные первой статьи,[первый комментарий, второй, ...]]"(с)
поддержка такого формата ---> "JSON" реализована в MS SQL 2016

Ну смотри :)
Сам напросился :)

-- Делаем таблички
CREATE TABLE story( id int ,[text] text,author_id int )
CREATE TABLE story_comments( id int,s_id INT,[text] text)

-- Заполнем данными

TRUNCATE TABLE [story]
TRUNCATE TABLE [story_comments]

INSERT INTO [dbo].[story]([id],[text],[author_id])VALUES(1,'Статья 1',1)
INSERT INTO [dbo].[story]([id],[text],[author_id])VALUES(2,'Статья 2',2)

INSERT INTO [dbo].[story_comments]([id],[s_id],[text])VALUES(1,1,'Первый комент Первой статьи')
INSERT INTO [dbo].[story_comments]([id],[s_id],[text])VALUES(2,1,'Второй комент Первой статьи')
INSERT INTO [dbo].[story_comments]([id],[s_id],[text])VALUES(3,1,'Третий комент Первой статьи')

-- Сам запрос

;WITH cte 
AS
( 
SELECT [text2]=
(
SELECT 
--s.id     AS 'Story_ID',
       s.[text]  AS 'Story_Text'
	   ,
     (
SELECT ISNULL(
REPLACE( 
REPLACE(
REPLACE(
(SELECT [text]=
(SELECT sc.[text]AS 'comment_Text'
FROM [story_comments] sc
WHERE  s.id=sc.[s_id]
FOR XML PATH (''))),'</comment_Text><comment_Text>',',')
,'<comment_Text>','['),'</comment_Text>',']')
,'[NULL]')
)  
FROM   [story] s
FOR XML PATH('')
))

SELECT '['+
REPLACE(
replace (
REPLACE (text2,'<Story_Text>','[')
,'</Story_Text>[',',[')
,'][','],[')+']'
 FROM cte

 Ответ:
 [[Статья 1,[Первый комент Первой статьи,Второй комент Первой статьи,Третий комент Первой статьи],[Статья 2,[NULL]]

Answer 4

[Sunsh1ne]

Хотелось бы узнать, кто как делает, может структура неверна?
Но хотелось бы, что бы возвращалось так:
[
[данные первой статьи, [первый комментарий, второй, ...]],
[вторая статья, [...]],
...
]

И еще, если комментариев нет к статье, возвращается ответ, но в полях про комментарий везде null

Comments

[igruschkafox]

"но в полях про комментарий везде null"
:)))
Если коментариев нет тогда таких полей вообще не будет - как Вы определите сколько комментариев должно быть у статьи ? (что бы проставить Null

[Sunsh1ne]

i.imgur.com/838tM5H.png

вот такой ответ приходит, если у статьи нет комментариев

[igruschkafox]

Sunsh1ne: Написал в своем комментарии :)
надеюсь поможет :)