How To Access Column In Subquery?
I'm having issues with the following query. For the subquery returning a
sum of total results, I'm getting the error Unknown column 'search.term'
in 'where clause', I'm not sure how I'd be able to do this query properly?
Any help is appreciated.
SELECT *,
(SELECT SUM(totalResults)
FROM (SELECT COUNT(*) as totalResults
FROM media
LEFT JOIN users ON users.id = media.userId
WHERE media.title LIKE CONCAT('%', search.term, '%')
OR media.description LIKE CONCAT('%', search.term, '%')
OR media.tags LIKE CONCAT('%', search.term, '%')
OR users.username LIKE search.term
UNION ALL
SELECT COUNT(*)
FROM reposts
LEFT JOIN media ON media.id = reposts.mediaId
LEFT JOIN users ON users.id = reposts.userId
WHERE media.title LIKE CONCAT('%', search.term, '%')
OR media.description LIKE CONCAT('%', search.term, '%')
OR media.tags LIKE CONCAT('%', search.term, '%')
OR users.username LIKE search.term
)
t) AS totalResults,
MAX(search.timestamp) AS searchTimestamp
FROM search
WHERE userId = '1'
GROUP BY search.term
ORDER BY searchTimestamp DESC
No comments:
Post a Comment