カテゴリー:
エラー
閲覧数:204 配信日:2020-03-15 09:44
修正前
public function fetchStatusRelationLastResEntryAll()//レスがない場合、最新一覧日は空欄(RIGHTで逆)
{
// SELECT DISTINCT et.* FROM
// エントリーテーブル et
// LEFT OUTER JOIN エントリー所属カテゴリーテーブル ec
// ON (et.エントリーID = ec.エントリーID)
// WHERE ec.カテゴリーID = 1
$sql = "
SELECT
a.id AS id,
a.category_id AS category_id,
a.project_id AS project_id,
a.domain_id AS domain_id,
a.state_id AS state_id,
a.official_url AS official_url,
a.body AS body,
a.counter AS counter,
a.created_at AS created_at,
a.modified AS modified,
a.topic_name,
z.*,
s.situation,
s.color AS statecolor,
r.modified AS rmodified,
u.display_name,
u.site_id,
c.category,
p.project,
d.domain,
relationentrynum,
GROUP_CONCAT(t.tag) AS tag,
GROUP_CONCAT(t.color) AS tag_color,
GROUP_CONCAT(st.tag_id) AS tag_id,
c.color AS category_color,
p.color AS project_color,
d.color AS domain_color
FROM
status AS a
LEFT JOIN user u ON a.user_id = u.id
LEFT JOIN category c ON a.category_id = c.category_id
LEFT JOIN project p ON a.project_id = p.project_id
LEFT JOIN domain d ON a.domain_id = d.domain_id
LEFT JOIN status_tag st ON (a.id = st.status_id)
LEFT JOIN state s ON a.state_id = s.state_id
RIGHT JOIN tag t ON (t.tag_id = st.tag_id)
LEFT OUTER JOIN(
SELECT
topic_id,MAX(modified) AS modified, COUNT(topic_id) AS `relationentrynum`
FROM
res
GROUP BY topic_id
) AS r ON a.id = r.topic_id
LEFT JOIN(
SELECT
a.category_id, a.category, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.category
else b.category
end AS parent_category,
CASE WHEN a.parent_id = 0
then a.category_id
else a.parent_id
end AS parent_id2
FROM
category a
LEFT JOIN
category b on a.parent_id = b.category_id
) AS z ON a.category_id = z.category_id
LEFT JOIN(
SELECT
a.project_id, a.project, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.project
else b.project
end AS parent_project,
CASE WHEN a.parent_id = 0
then a.project_id
else a.parent_id
end AS parent_id2
FROM
project a
LEFT JOIN
project b on a.parent_id = b.project_id
) AS x ON a.project_id = x.project_id
LEFT JOIN(
SELECT
a.domain_id, a.domain, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.domain
else b.domain
end AS parent_domain,
CASE WHEN a.parent_id = 0
then a.domain_id
else a.parent_id
end AS parent_id2
FROM
domain a
LEFT JOIN
domain b on a.parent_id = b.domain_id
) AS y ON a.domain_id = y.domain_id
WHERE a.public = 1
GROUP BY a.id
ORDER BY GREATEST(a.`modified`,IFNULL(`rmodified`, a.`modified`))DESC, a.`created_at` DESC
";
return $this->fetchAll($sql);
}
JOIN追加したらエラー発生
LEFT JOIN `chronology` chr ON (a.id = chr.status_id)
`chr`.`modified` AS chrmodified,
public function fetchStatusRelationLastResEntryAll()//レスがない場合、最新一覧日は空欄(RIGHTで逆)
{
// SELECT DISTINCT et.* FROM
// エントリーテーブル et
// LEFT OUTER JOIN エントリー所属カテゴリーテーブル ec
// ON (et.エントリーID = ec.エントリーID)
// WHERE ec.カテゴリーID = 1
$sql = "
SELECT
a.id AS id,
a.category_id AS category_id,
a.project_id AS project_id,
a.domain_id AS domain_id,
a.state_id AS state_id,
a.official_url AS official_url,
a.body AS body,
a.counter AS counter,
a.created_at AS created_at,
a.modified AS modified,
a.topic_name,
z.*,
s.situation,
s.color AS statecolor,
r.modified AS rmodified,
u.display_name,
u.site_id,
c.category,
p.project,
d.domain,
`chr`.`modified` AS chrmodified,
relationentrynum,
GROUP_CONCAT(t.tag) AS tag,
GROUP_CONCAT(t.color) AS tag_color,
GROUP_CONCAT(st.tag_id) AS tag_id,
c.color AS category_color,
p.color AS project_color,
d.color AS domain_color
FROM
status AS a
LEFT JOIN user u ON a.user_id = u.id
LEFT JOIN category c ON a.category_id = c.category_id
LEFT JOIN project p ON a.project_id = p.project_id
LEFT JOIN domain d ON a.domain_id = d.domain_id
LEFT JOIN status_tag st ON (a.id = st.status_id)
LEFT JOIN state s ON a.state_id = s.state_id
LEFT JOIN `chronology` chr ON (a.id = chr.status_id)
RIGHT JOIN tag t ON (t.tag_id = st.tag_id)
LEFT OUTER JOIN(
SELECT
topic_id,MAX(modified) AS modified, COUNT(topic_id) AS `relationentrynum`
FROM
res
GROUP BY topic_id
) AS r ON a.id = r.topic_id
LEFT JOIN(
SELECT
a.category_id, a.category, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.category
else b.category
end AS parent_category,
CASE WHEN a.parent_id = 0
then a.category_id
else a.parent_id
end AS parent_id2
FROM
category a
LEFT JOIN
category b on a.parent_id = b.category_id
) AS z ON a.category_id = z.category_id
LEFT JOIN(
SELECT
a.project_id, a.project, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.project
else b.project
end AS parent_project,
CASE WHEN a.parent_id = 0
then a.project_id
else a.parent_id
end AS parent_id2
FROM
project a
LEFT JOIN
project b on a.parent_id = b.project_id
) AS x ON a.project_id = x.project_id
LEFT JOIN(
SELECT
a.domain_id, a.domain, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.domain
else b.domain
end AS parent_domain,
CASE WHEN a.parent_id = 0
then a.domain_id
else a.parent_id
end AS parent_id2
FROM
domain a
LEFT JOIN
domain b on a.parent_id = b.domain_id
) AS y ON a.domain_id = y.domain_id
WHERE a.public = 1
GROUP BY a.id
ORDER BY GREATEST(a.`modified`,IFNULL(`rmodified`, a.`modified`))DESC, a.`created_at` DESC
";
return $this->fetchAll($sql);
}
修正対応
SQLを一意に特定できるよう、GroupBy句に取得するカラムを指定
GROUP BY に chr.modified を追加
GROUP BY a.id,chr.modified
public function fetchStatusRelationLastResEntryAll()//レスがない場合、最新一覧日は空欄(RIGHTで逆)
{
// SELECT DISTINCT et.* FROM
// エントリーテーブル et
// LEFT OUTER JOIN エントリー所属カテゴリーテーブル ec
// ON (et.エントリーID = ec.エントリーID)
// WHERE ec.カテゴリーID = 1
$sql = "
SELECT
a.id AS id,
a.category_id AS category_id,
a.project_id AS project_id,
a.domain_id AS domain_id,
a.state_id AS state_id,
a.official_url AS official_url,
a.body AS body,
a.counter AS counter,
a.created_at AS created_at,
a.modified AS modified,
a.topic_name,
z.*,
s.situation,
s.color AS statecolor,
r.modified AS rmodified,
u.display_name,
u.site_id,
c.category,
p.project,
d.domain,
`chr`.`modified` AS chrmodified,
relationentrynum,
GROUP_CONCAT(t.tag) AS tag,
GROUP_CONCAT(t.color) AS tag_color,
GROUP_CONCAT(st.tag_id) AS tag_id,
c.color AS category_color,
p.color AS project_color,
d.color AS domain_color
FROM
status AS a
LEFT JOIN user u ON a.user_id = u.id
LEFT JOIN category c ON a.category_id = c.category_id
LEFT JOIN project p ON a.project_id = p.project_id
LEFT JOIN domain d ON a.domain_id = d.domain_id
LEFT JOIN status_tag st ON (a.id = st.status_id)
LEFT JOIN state s ON a.state_id = s.state_id
LEFT JOIN `chronology` chr ON (a.id = chr.status_id)
RIGHT JOIN tag t ON (t.tag_id = st.tag_id)
LEFT OUTER JOIN(
SELECT
topic_id,MAX(modified) AS modified, COUNT(topic_id) AS `relationentrynum`
FROM
res
GROUP BY topic_id
) AS r ON a.id = r.topic_id
LEFT JOIN(
SELECT
a.category_id, a.category, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.category
else b.category
end AS parent_category,
CASE WHEN a.parent_id = 0
then a.category_id
else a.parent_id
end AS parent_id2
FROM
category a
LEFT JOIN
category b on a.parent_id = b.category_id
) AS z ON a.category_id = z.category_id
LEFT JOIN(
SELECT
a.project_id, a.project, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.project
else b.project
end AS parent_project,
CASE WHEN a.parent_id = 0
then a.project_id
else a.parent_id
end AS parent_id2
FROM
project a
LEFT JOIN
project b on a.parent_id = b.project_id
) AS x ON a.project_id = x.project_id
LEFT JOIN(
SELECT
a.domain_id, a.domain, a.parent_id,
CASE WHEN a.parent_id = 0
then a.fonticon
else b.fonticon
end AS fonticon,
CASE WHEN a.parent_id = 0
then a.color
else b.color
end AS color,
CASE WHEN a.parent_id = 0
then a.domain
else b.domain
end AS parent_domain,
CASE WHEN a.parent_id = 0
then a.domain_id
else a.parent_id
end AS parent_id2
FROM
domain a
LEFT JOIN
domain b on a.parent_id = b.domain_id
) AS y ON a.domain_id = y.domain_id
WHERE a.public = 1
GROUP BY a.id,chr.modified
ORDER BY GREATEST(a.`modified`,IFNULL(`rmodified`, a.`modified`))DESC, a.`created_at` DESC
";
return $this->fetchAll($sql);
}
→ MySQLでSELECT文を作成するための基本的な考え方。どの構文を採用するか、までの思考履歴