EE Tip: Counting the results of a nested query

If you’ve built anything remotely challenging in ExpressionEngine, you’ve no doubt discovered things that are easier done in native PHP than in EE tags. A lot of it has to do with how ExpressionEngine parses templates and what gets parsed first.

One recent bugbear I ran into was trying to use the {count} magic” variable from a call to {exp:query} that resided inside a loop. I needed the {entry_id} from the entry in the SQL statement, but {count} (despite being used inside {exp:query}) was evaluating as the count and not the {exp:query} count. To solve the issue, I came up with the following:

{exp:weblog:entries weblog="services"
{exp:query sql="SELECT @row := 0"}{/exp:query}
{exp:query sql="SELECT @row := @row + 1 AS `query_count`
`project`.`title` AS `project_name`
FROM `exp_weblog_titles` AS `project`
INNER JOIN `exp_relationships` AS `service_projects` ON `project`.`entry_id` = `service_projects`.`rel_parent_id`
WHERE `project`.`weblog_id` = 13
AND `service_projects`.`rel_child_id` = {entry_id}
LIMIT 10"}
{if query_count=="1"}
{if query_count!="10"}
{if query_count=="9"}
<li>&#8230;and many more</li>
view raw gistfile1.phtml hosted with ❤ by GitHub

You’ll notice I’m using {exp:query} twice. The first time is to establish a variable in the SQL connection. Then I am free to use the variable in the second query and the count (returned as {query_count}) will be a count of the inner loop instead of the outer one.

It is important to note, however, that MySQL will evaluate the variable’s incrementation before paying attention to any ORDER BY clauses, so your mileage may vary. Regardless, it’s a handy technique.

Like it? Share it

Share on LinkedIn

Share on Google Plus