php - MySQL选择匹配的id，输出到json

Question

我正在使用 d3js 来直观地显示用户生成的内容。正在显示的数据是通过标准的三个表从数据库中提取的:条目、条目标签、条目标签查找表。我的 d3js 代码需要 JSON 输出才能正常工作。我已经正确编码了 PHP 到 JSON 的输出，但我很难弄清楚需要编写什么查询来提取正确的 ID。

条目表(create_thought)

id | email | thought_title

标签表(thoughts_tags)

tag_id | tag_name

查找表(thoughts_tagged)

tagged_id | tag_id | thought_id

PHP 到 JSON 代码(运行)

$titlequery = "
SELECT  `thought_title` FROM  `create_thought`
";
//THE BELOW QUERY IS INCORRECT
$tagquery = "
SELECT  `tag_id` AS `source`, `thought_id` AS `target` FROM  `thoughts_tagged`
";
$query1 = mysql_query($titlequery);

if ( ! $query1 ) {
echo mysql_error();
die;
}
$query2 = mysql_query($tagquery);

if ( ! $query2 ) {
echo mysql_error();
die;
}

$datatitle = array();

for ($x = 0; $x < mysql_num_rows($query1); $x++) {
$datatitle[] = mysql_fetch_assoc($query1);
}

$datatag = array();

for ($y = 0; $y < mysql_num_rows($query2); $y++) {
$datatag[] = mysql_fetch_assoc($query2);
}

echo '{
"nodes":' . json_encode($datatitle) . ',"links":' . json_encode($datatag) . '}';     

这输出如下:

{ "nodes":[{"thought_title":"Title1"},{"thought_title":"Title2"}],"links":[{"source":"1","target":"8"},{"source":"2","target":"8"},{"source":"2","target":"17"}]}

我的问题是链接(源/目标)都不正确。我需要源是thought_id，目标是所有具有匹配tag_ids的thought_id。我尝试摆弄 How to filter SQL results in a has-many-through relation 中的一些解决方案，但它们不是我需要的。

下面是我得到的最接近的，但我不确定如何使用源和目标格式化它；它只是用一个匹配的标签(55)来提取thought_id。

SELECT id
FROM   create_thought
WHERE  EXISTS (SELECT * FROM thoughts_tagged
WHERE  thought_id = id AND tag_id = 55)

编辑:想出了这个，看起来更接近，但格式不正确。我需要基于匹配标签列出的thought_ids，但没有其他。

SELECT DISTINCT t1.*
FROM thoughts_tagged t1
INNER JOIN thoughts_tagged t2
ON t1.tag_id = t2.tag_id AND t1.thought_id <> t2.thought_id

感谢任何帮助!

Answer 1

这是我认识的一位开发人员能够想出的答案。它工作完美。 :)

SELECT DISTINCT
t1.thought_id as source,
t2.thought_id as target
FROM thoughts_tagged t1
INNER JOIN
thoughts_tagged t2
ON t1.tag_id = t2.tag_id AND t1.thought_id <> t2.thought_id