I have schools, teachers and students channels with each of the students and teachers channels with a field that contains the entry_id
of the school they're related to (not actually using native EE relationships fields in this case).
I need to output a list of all the schools and their total number of teachers and students but also show which of those entries are open and pending (entries only become open after being activated via link in an invite email).
What I have so far outputs the school and the count of all students but I need to know how to change it so that I can output different variables for all students and pending ones.
{exp:channel:entries
channel="schools"
disable="categories|member_data|pagination|trackbacks"
dynamic="no"
orderby="title"
sort="asc"
}
{exp:query sql="SELECT COUNT(cd.entry_id) as student_count
FROM exp_channel_titles ct
JOIN exp_channel_data cd ON cd.entry_id = ct.entry_id
WHERE cd.channel_id=5
AND cd.field_id_45={entry_id}
}
{if count==1}<p><strong>Total schools:</strong> {total_results}</p>
<ul>{/if}
<li>{title} – {student_count}student{if student_count!=1}s{/if}</li>
{if count==total_results}</ul>{/if}
{/exp:query}
{/exp:channel:entries}
I'd also like to know the most optimal way to query the records from both students and teachers channels. I could double up on queries and just change the channel_id
and field_id_XX
but would like to know if there's a cleaner method.