Составление таблиц Average и LEFT JOIN
Я пытаюсь объединить среднее значение столбца rating на основе postid с другой таблицей.
Я создал этот код, который вычисляет среднее значение rating, и он работает:
SELECT post_rating.postid,
AVG(post_rating.rating)
FROM post_rating
GROUP BY post_rating.postid"
Однако, когда я пытаюсь присоединиться, это не срабатывает, как показано ниже:
$join = ' LEFT JOIN ('SELECT post_rating.postid,
AVG(post_rating.rating)
FROM post_rating
GROUP BY post_rating.postid') AS post_rating
ON post_rating.postid=theList.id';
Как мне присоединиться к этому коду или есть лучший способ сделать это? Спасибо!
Весь код, который я использую:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=myDB', 'root', 'root');
$select = 'SELECT theList.id, theList.name, post_rating.rating, post_rating.postid';
$from = ' FROM theList';
$where = ' WHERE TRUE';
$join = ' LEFT JOIN ('SELECT post_rating.postid, AVG(post_rating.rating) FROM post_rating GROUP BY post_rating.postid') AS post_rating ON post_rating.postid=theList.id';
$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
if (in_array("pub", $opts)){
$where .= " AND pub = 1";
}
if (in_array("bar", $opts)){
$where .= " AND bar = 1";
}
if (in_array("restaurant", $opts)){
$where .= " AND restaurant = 1";
}
if (in_array("club", $opts)){
$where .= " AND club = 1";
}
$sql = $select . $from . $join . $where;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
?>
Вот упрощенные версии моих таблиц:
таблица postid
id | postid | rating
--------------------
1 | 3 | 5
2 | 3 | 4
3 | 1 | 3
Таблица списка
id | name
----------
1 | name1
2 | name2
3 | name3
По сути, я пытаюсь вывести:
id | name | rating
------------------
1 | name1 | 3
2 | name2 | 0
3 | name3 | 4.5
Ответы 2
Теперь я вижу проблему, о которой вы пытались рассказать мне в своих предыдущих комментариях к вопросу, вы по какой-то причине закончили строку $join, поэтому большая часть запроса не будет включена
Итак, эта строка
$join = ' LEFT JOIN ('SELECT post_rating.postid, AVG(post_rating.rating)
FROM post_rating GROUP BY post_rating.postid') AS post_rating
ON post_rating.postid=theList.id';
(см. цвет кода показывает ошибку)
Так что поправьте
$join = ' LEFT JOIN (SELECT post_rating.postid, AVG(post_rating.rating)
FROM post_rating GROUP BY post_rating.postid) AS post_rating
ON post_rating.postid=theList.id';
Могу ли я предложить вам добавить в PDO обработку EXCEPTION, чтобы в браузере сообщали вам об ошибках. Сделайте это в своем коде подключения
$pdo = new PDO(.......);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
По-прежнему появляется ошибка 500! Что-то еще мне не хватает? В очередной раз благодарим за помощь!
500 ошибок обычно довольно серьезны. Просматривали ли вы журнал ошибок PHP, чтобы узнать, есть ли какие-либо сообщения, которые помогут определить проблему?
«По-прежнему появляется ошибка 500! Что-то еще мне не хватает?» Перейдите к другому хостинг-провайдеру, который не навязывает вам белый экран смерти при выходе из строя парсера PHP. @tbowden .. тогда у вас будет более полезная ошибка. Или сделайте то, что говорит RiggsFolly, просматривает файл ошибок PHP, если у вас есть к нему доступ ... если у вас нет доступа к нему, используйте это в своем коде ini_set('error_log', 'your/path/to/errors.log');
Я предполагаю, что вы изменили что-то еще, кроме этого! Эта ошибка должна была вызвать довольно обычную ошибку, а не 500.
Здесь можно многому научиться! Спасибо ребята. Я выяснил ошибку! Почему-то сказано: «1054 Неизвестный столбец post_rating.rating» в «списке полей».
Столбец рейтинга в таблице post_rating определенно существует!
Вам нужен псевдоним: AVG(post_rating.rating) AS rating.
Кстати, вам не нужно говорить post_rating. перед всеми столбцами в подзапросе, поскольку там только одна таблица.
Я думаю, что вы подходите к этому запросу излишне сложным образом. Просто выполните Left Join между таблицами, рассмотрите theList как крайнюю левую таблицу и вычислите Avg() в группе по на postid:
SELECT tl.id,
tl.name,
AVG(pr.rating) AS rating
FROM post_rating AS pr
LEFT JOIN theList AS tl ON tl.id = pr.postid
GROUP BY tl.id, tl.name
Ваш PHP-код будет выглядеть следующим образом:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=myDB', 'root', 'root');
$select = 'SELECT tl.id,
tl.name,
AVG(pr.rating) AS rating ';
$from = ' FROM post_rating AS pr ';
$where = ' WHERE 1 = 1 ';
$join = ' LEFT JOIN theList AS tl ON tl.id = pr.postid ';
$opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
if (in_array("pub", $opts)){
$where .= " AND pub = 1";
}
if (in_array("bar", $opts)){
$where .= " AND bar = 1";
}
if (in_array("restaurant", $opts)){
$where .= " AND restaurant = 1";
}
if (in_array("club", $opts)){
$where .= " AND club = 1";
}
$group = ' GROUP BY tl.id, tl.name ';
$sql = $select . $from . $join . $where . $group;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);
?>
Действительно? В этом запросе неправильно используется расширенная функция MySQL GROUP BY. Другими словами, используются столбцы в SELECT без использования их в GROUP BY. Это не жалоба ANSI SQL и может привести к несвязанным данным для каждого неагрегированного столбца выбора, который не является в группе по.
@RaymondNijland хотя и исправлен, но tl.id и pr.postid были функционально зависимы через соединение
Особые случаи функциональной зависимости могут работать только тогда, когда столбец, используемый в GROUP BY, имеет (PRIMARY или UNIQUE) ключевую зависимость dev.mysql.com/doc/refman/5.7/en/…, по крайней мере, это то, что я понял при чтении документации.
@RaymondNijland, спасибо за разъяснения. Хотя я чувствовал, что можно с уверенностью предположить, что tl.id является первичным ключом в таблице theList, и поскольку мы присоединяемся, используя tl.id = pr.postid; не имеет значения, группирую я по tl.id или pr.postid
«не имеет значения, группирую я по tl.id или pr.postid». В документе объясняется, что столбец или столбцы, используемые в GROUP BY, используются для функциональной зависимости ... «Таким образом, co.name - функционально зависит от столбцов GROUP BY, и запрос действителен.», так что ясно, что это похоже, это имеет значение ... Кроме того, функциональные зависимости, производные от ключей с несколькими столбцами (JOINING) и от Equalities, следует за этим, если вы присмотритесь.
@MadhurBhaiya Я успешно использовал этот код для получения рейтинга. Благодарю вас! Однако теперь оператор WHERE TRUE не работает и не получает ошибку! Вы знаете, как я могу это решить?
@tbowden зачем тебе Где правда?
У меня есть несколько флажков, которые в основном помогают мне уточнить запрос. Таким образом, код работает, проверяя, установлены ли флажки, а затем возвращает результаты, в зависимости от того, какие флажки установлены! - Вы видите, как это работает наверху? Спасибо!
@tbowden проверьте обновленный ответ. Я также добавил модифицированный PHP-код
Идеально! Огромное спасибо.
«это не работает» - Что такое сообщение об ошибке MySQL?