1

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?

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.

| improve this answer | |
  • Thanks @ Andris, the following worked: 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 – paulcripps Sep 15 '15 at 8:53
0

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
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.