Playa related entries count

Question

We are running reports and need a query - we're using playa and I want to get the total number of child_entries where channel_id = 16 and where the 'parent_field_id' = 68.

My current query is:

SELECT exp_channel_titles.entry_id, exp_channel_titles.title, count(exp_playa_relationships.child_entry_id) AS entry_count 

FROM exp_channel_titles LEFT JOIN exp_playa_relationships ON exp_channel_titles.entry_id = exp_playa_relationships.child_entry_id 

WHERE exp_playa_relationships.parent_field_id = 68 AND exp_channel_titles.channel_id=16

What I expected to see was:

• Event Title - Entry ID 1218 - total articles: 788
• Event Title - Entry ID 2866 - total articles: 605

What I am getting is:

• Event Title - Entry ID - total articles: 1393

Any thoughs how this would be achieve and what I missed?

Answer 1

You're getting the total of all related articles because you're not specifying that you need to group the results by entry. Try:

SELECT 
    t.entry_id, 
    t.title, 
    COUNT(r.child_entry_id) AS entry_count 
FROM 
    exp_channel_titles AS t
INNER JOIN 
    exp_playa_relationships AS r 
    ON t.entry_id = r.child_entry_id 
WHERE 
    r.parent_field_id = 10 
    AND t.channel_id = 1
GROUP BY 
    t.entry_id

I also changed the join from LEFT to INNER to avoid potential issues when some data has not been cleaned up properly and missing entries still have relationship records.

Answer 2

For reference the working query was:

SELECT
    t.entry_id,
    t.title,
    count(r.child_entry_id) AS entry_count 

FROM
    exp_channel_titles AS t

INNER JOIN
    exp_playa_relationships AS r
    ON t.entry_id = r.child_entry_id 

WHERE
    r.parent_field_id = 68
    AND t.channel_id = 16

GROUP BY
    r.child_entry_id