Retrieving like counts on entries from SQL.

Question

Hey all,

I've been adding a like feature to an entries database... here's the structure of the DBs:

**Users**
user_id
user_name
etc.

**Entries**
entry_id
entry_content
etc.

**Likes**
user_id
entry_id

(It's a little more complicated than that, there are groups/categories, but that should explain it fine...) Here's the SQL query I'm working with at the moment:

SELECT 
entries.*,
DATE_FORMAT(entry_date, "%M %D, %Y") as entry_date,
groups.group_short_name,
users.user_name, users.user_id,
FROM entries 
INNER JOIN groups ON groups.group_id = entries.group_id 
INNER JOIN users ON users.user_id = entries.user_id 
ORDER BY entry_date DESC

I'm trying to also retrieve likes per entry with this query and wondering if it is possible. I've been trying:

COUNT(DISTINCT likes.like_id) as likes

with

LEFT JOIN likes ON likes.entry_id = entries.entry_id

But I don't think that is anywhere near right. Am I way off? Is this possible? Hope it all made sense.

Thanks for the help in advance.

Answer 1

Add:

GROUP BY entries.entry_id

See if that works.

Answer 2

Give your tables aliases, for one..

FROM entries e

Then add a column query:

select e.*, (select count(*) from Likes where entry_id = e.entry_id) as entry_likes