MySQL: запрос с переменной AS не работает
Я сделал запрос, используя который я создаю переменную accountId, я не могу повторно использовать accountId в моем подзапросе, почему?
Вот мой код:
SELECT userId AS accountId,
(SELECT recommended FROM ads_connections WHERE byUserId = accountId AND throughUserId = accountId AND adId = :recommendedAdId) AS recommended,
--
(SELECT requestStatus FROM ads_recommends_requests WHERE
userId = :currentUserId AND
requestFromUserId = accountId AND
advertisementId = :statusAdId) AS requestStatus,
(SELECT COUNT(id) FROM ads_recommends_requests
WHERE
requestFromUserId = accountId AND
advertisementId = :countAdId AND
requestStatus = '1'
) AS recommendedTimes
--
FROM user_contacts
WHERE userId IN " . $myCircleString . "AND phoneNumber = :phoneNumber
AND (SELECT fictive FROM users WHERE id = userId) = 0
";
Пример вывода:
"data": [
{
"userId": 0,
"accountId": 2,
"fictive": false,
"name": "Mickael",
"phoneNumber": "0584688888",
"review": "0",
"liked": false,
"recommended": 0,
"requestStatus": 0,
"recommendedTimes": 0
}
Спасибо
Решение
Проверь это-
SELECT userId AS accountId,
(SELECT recommended FROM ads_connections WHERE byUserId = userId AND throughUserId = userId AND adId = :recommendedAdId) AS recommended,
--
(SELECT requestStatus FROM ads_recommends_requests WHERE
userId = :currentUserId AND
requestFromUserId = userId AND
advertisementId = :statusAdId) AS requestStatus,
(SELECT COUNT(id) FROM ads_recommends_requests
WHERE
requestFromUserId = userId AND
advertisementId = :countAdId AND
requestStatus = '1'
) AS recommendedTimes
--
FROM user_contacts
WHERE userId IN " . $myCircleString . "AND phoneNumber = :phoneNumber
AND (SELECT fictive FROM users WHERE id = userId) = 0
";
Другие решения
ОТВЕТЬТЕ НА ОТВЕТ
Создайте переменную следующим образом:
SELECT @acctId := userId AS accountId
И использовать это:
(SELECT requestStatus FROM ads_recommends_requests WHERE
userId = :currentUserId AND
requestFromUserId = @acctId AND
advertisementId = :statusAdId) AS requestStatus
detector