Возникли проблемы с SQL Join в ColdFusion
Я пытаюсь сделать что-то очень простое в CF/SQL, но просто не могу понять, что я делаю неправильно.
У меня есть эти таблицы:
movies genres actors moviesGenres moviesActors
------ ------ ------ ------------ ------------
movieId genreId actorId id id
title name fname movie movie
lname genre actor
Я пытаюсь написать запрос, который просто перечисляет все фильмы, с жанром каждого фильма (возможно несколько вариантов выбора) и актерами каждого фильма (опять же, возможно несколько вариантов выбора).
Когда я пишу запрос, чтобы включить только жанры, все работает отлично. Я использую для самого запроса группировку по movieId, а затем отдельно по жанрам.
Но когда я пытаюсь включить актеров, все взрывается, и кажется, что группа распадается.
Вот SQL-запрос для обоих соединений:
SELECT m.movieId, m.title, m.releaseDate, m.description, g.name, a.fname, a.lname
FROM movies m
INNER JOIN genres g ON g.genreId IN (SELECT genre FROM moviesGenres WHERE movie = m.movieId)
INNER JOIN actors a ON a.actorID IN (SELECT actor FROM moviesActors WHERE movie = m.movieId)
ORDER BY m.title
Заранее благодарю за любую помощь!
ОБНОВИТЬ:
Похоже, что запрос, предоставленный Ли и Марком, в целом работает, но я все еще вижу акторов, многократно отображаемых в. Вот мой код:
<tbody>
<cfoutput query="variables.movieList" group="movieId">
<tr>
<td><a href="##">#title#</a></td>
<td><cfoutput group="name">#name# | </cfoutput></td>
<td><cfoutput group="actorId">#actorId# | </cfoutput></td>
</tr>
</cfoutput>
</tbody>
Я также попробовал это, не группируя заключительный тег, но это не сработало. Обратите внимание, что я изменил a.lName и a.fName на a.actorId для простоты тестирования.
Пример строки выглядит так:
The Godfather Action | Drama | 1 | 2 | 1 | 2 |
4 ответа
Вы должны связать все таблицы в соединении. В противном случае вы получите декартово произведение. Так что ПРИСОЕДИНЯЙТЕСЬ к соединительным таблицам, а не используйте их в подзапросе. С правильным ORDER BY вы сможете использовать <cfoutput group=".."> отформатировать вывод по желанию.
Не проверено, но что-то в этом роде.
SELECT m.movieId, m.title, m.releaseDate, m.description, g.name, a.actorID, a.fname, a.lname
FROM movies m
LEFT JOIN moviesGenres mg ON mg.movie = m.movieID
LEFT JOIN genres g ON g.genreID = mg.genre
LEFT JOIN moviesActors ma ON ma.movie = m.movieID
LEFT JOIN actors a ON a.actorId = ma.actor
// since movie names may not be unique, do a secondary sort on movieID
ORDER BY m.title, m.movieID, g.Name, a.fName, a.lName
Редактировать на основе комментариев:
Как упоминал Стив, при использовании cfoutput group результаты должны быть отсортированы и сгруппированы таким же образом, чтобы функция работала правильно. Смотрите его ответ для более подробного объяснения.
Альтернативой подходу является использование структур для генерации уникальных жанров и актеров. Обратите внимание, что SQL-запрос выше был немного изменен, чтобы соответствовать вашему обновленному примеру.
<table border="1">
<!--- group by ID in case multiple movies have the same name --->
<cfoutput query="yourQuery" group="movieID">
<!--- use structures to save unique genres and actors --->
<cfset genres = {}>
<cfset actors = {}>
<cfoutput>
<cfset genres[name] = true>
<cfset actors[actorID] = true>
</cfoutput>
<!--- display results --->
<tr><td>#Title#</td>
<td>#structKeyList(genres, "|")#</td>
<td>#structKeyList(actors, "|") #</td>
</tr>
</cfoutput>
</table>
Ваш вопрос действительно имеет две отдельные проблемы.
1) Как написать запрос SQL, чтобы получить результаты, необходимые для вашей страницы.
У Марка и Ли есть хорошие решения этой проблемы. Лично я предпочитаю Ли.
Я просто добавлю, чтобы вывести ваши результаты по мере того, как вы идете (все здесь, вероятно, знают это, но хорошо, что это было задокументировано для потомков), так как легко предположить, что вы получаете результаты, отличные от тех, которые вы думаете.
Также это особенно актуально для следующего шага.
2) Как правильно отобразить результаты.
Для этого вам нужно понять, как работает атрибут group в cfoutput. Он просто проверяет, изменяется ли значение выбранного столбца по сравнению с предыдущей строкой. Это означает, что вы должны упорядочить по столбцу группы, иначе группировка будет выглядеть неправильно.
Например:
категория, продукт
- Еда, яблоко
- Очистители, Комета
- Еда, Банан
Если вы сделали с результатами выше, это выглядело бы неправильно, потому что сгруппированный вывод будет отображаться каждый раз, когда категория переключается. Следующий набор результатов, с другой стороны, будет работать правильно:
- Очистители, Комета
- Еда, яблоко
- Еда, Банан
Результатом этого является то, что группа cfoutput зависит от порядка, и поэтому вы можете использовать ее только в одном столбце для любого одного уровня (хотя, конечно, вы можете пройти столько уровней, сколько захотите).
Решение, таким образом, состоит в том, чтобы обрабатывать вторую группу столбцов более вручную. Это может быть, например, составление списка где-нибудь и затем его повторение.
Итак, для запроса в вашем примере это будет работать:
<tbody>
<cfoutput query="variables.movieList" group="movieId">
<cfset actors = "">
<cfoutput>
<cfif NOT ListFindNoCase(actors,actorId)>
<cfset actors = ListAppend(actors,actorId)>
</cfif>
</cfoutput>
<tr>
<td><a href="##">#title#</a></td>
<td><cfoutput group="name">#name# | </cfoutput></td>
<td><cfloop list="actors" index="actor">#actor# | </cfloop></td>
</tr>
</cfoutput>
</tbody>
Не уверен, что мне нравится ваш синтаксис. Я предполагаю, что ваши подвыборы и псевдонимы не соответствуют вашим результатам. После чего вы присоединитесь к этим бридж-столам, если я поймаю, что вы делаете. Я бы сделал что-то вроде этого:
SELECT m.movieId, m.title, m.releaseDate, m.description, g.name, a.fname, a.lname
FROM movies m
INNER JOIN movieGenres mg ON m.movieID = mg.movie
INNER JOIN genres g ON g.genreID = mg.genre
INNER JOIN movieactors ma on ma.movie = m.movieID
INNER JOIN actors a on ma.actor = a.actorID
ORDER BY m.title
Это сработало бы, если activists.movie и genres.movie совпадают с movies.movieID - но обычно мне нравится видеть внешние ключи, названные лучше, чем это. Например, если Actor.movie действительно содержит "movieID", назовите его "movieID" - иначе я думаю, что я ищу
Вы не можете сделать это как с актерами, так и с жанрами в одном запросе. Лучше всего выводить только фильмы с <cfoutput query="someQry" group="movieId"> а затем выполните запрос-запрос, чтобы получить актеров и жанры для каждого фильма отдельно.