what is the error in current query? (it's working before update mysql)
receive:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE ON LIKE.feed_item_id = feed_item.id AND LIKE.user_id= LEFT JOIN category ON' at line 15
query:
SELECT feed_item.*,
user.id AS user_id,
user.first_name,
user.last_name,
user.avatar_url,
category.name AS category_name,
feed_item.*,
user.id AS user_id,
user.first_name,
user.last_name,
user.avatar_url,
like.user_id AS is_liked
FROM (feed_item)
LEFT JOIN USER ON USER.id=feed_item.user_id
LEFT JOIN LIKE ON LIKE.feed_item_id = feed_item.id
AND LIKE.user_id=
LEFT JOIN category ON category.id=feed_item.category_id
WHERE feed_item.visibility IN ('public')
AND feed_item.user_id != 0
ORDER BY time_stamp DESC LIMIT 11
how to fix this? thanks,
LIKE is a reserved keyword, you need to wrap it in backticks, also here LIKE.user_id= you haven't specified a parameter for the equality condition (eg LIKE.user_id = 30)
SELECT feed_item.*,
user.id AS user_id,
user.first_name,
user.last_name,
user.avatar_url,
category.name AS category_name,
feed_item.*,
user.id AS user_id,
user.first_name,
user.last_name,
user.avatar_url,
`like`.user_id AS is_liked
FROM feed_item
LEFT JOIN USER ON USER.id=feed_item.user_id
LEFT JOIN `LIKE` ON `LIKE`.`feed_item_id` = feed_item.id
AND `LIKE`.`user_id`= feed_item.user_id // <- here
LEFT JOIN category ON category.id=feed_item.category_id
WHERE feed_item.visibility IN ('public')
AND feed_item.user_id != 0
ORDER BY time_stamp DESC LIMIT 11
'LIKE' is a reserved keyword in SQL, so I would strongly suggest not naming a table that. I imagine that is what is causing your error!
If you can rename your table, do it. 'Like' is a keyword for SQL, so use it like that generate an error.
Rename your table name; LIKE and USER are reserved words. Try to include a prefix like tbl_like or make it plural ("likes").
SELECT users.id AS user_id,
users.first_name,
users.last_name,
users.avatar_url,
category.name AS category_name,
users.id AS user_id,
users.first_name,
users.last_name,
users.avatar_url,
likes.user_id AS is_liked
FROM feed_item
LEFT JOIN USERs ON USERs.id=feed_item.user_id
LEFT JOIN LIKEs ON LIKEs.feed_item_id = feed_item.id
LEFT JOIN category ON category.id=feed_item.category_id
WHERE feed_item.visibility IN ('public')
AND feed_item.user_id != 0
ORDER BY time_stamp DESC LIMIT 11