1

Given Channel entries, is there a way to get a list of unique entries in a text field and how many times that entry exist? For example, say the Channel contains the field first_name (FT is text) and there are 100 entries. I need to know how that "John" exist 20 times, "Jill" exist 50 times, and "Jane" exist 30 times.

One possible solution is to nest looping through the channel entries and enabling PHP in the template in order to build the result I need. This seems inefficient. Another option is to probably use the Query module, but that requires knowing which fields the data exist in -- which becomes a maintenance issue when fields are deleted and re-added.

Suggestions?

Improve this question
5
  • I'm not certain I understand. You want to find DISTINCT items and COUNT them, but you don't want to specify the field that should be DISTINCT?
    – nonprofit_tech
    Jan 3, 2013 at 8:08
  • If I go with the Query module solution, then yes I would specify which fields should be DISTINCT. The problem is that if the Channel field is deleted and re-added, then the SQL will be broken because field__id_x would have changed. Seems like a maintenance nightmare.
    – ExpressionEngineNewbie
    Jan 3, 2013 at 8:12
  • Generally speaking, channel fields aren't deleted much after development is complete and a field in use, shouldn't be deleted. Only a site super_admin or a trained admin should have the right to delete fields. Deleting fields randomly, wouldn't just effect custom queries, but all templates that use the field as well. So it would be a nightmare all around, not just for this. But that would be a user generated nightmare, not by the query process itself.
    – nonprofit_tech
    Jan 3, 2013 at 8:31
  • Good point about development/production. In this case, I guess I don't expect Channel fields to change at all once finalized. Do you think Query module is the way to go? That means SQL in the templates -- and possibly enabling PHP in order to dynamically generate the SQL to execute. Is this a good thing to do?
    – ExpressionEngineNewbie
    Jan 3, 2013 at 11:29
  • For a few clients, we've created a 'Reports' area, which basically just stores custom reports we've developed at their request. They all use the Query Module, only one that I can think of has PHP enabled-- when we needed to group items, by State, I believe. Generally speaking, this is fine. EE gives you the power to do both custom queries and use PHP in the templates. It also warns you of potential dangers (primarily from your code). If NSM Reports doesn't look like it will work or you'd rather not use it, I'd go with custom queries.
    – nonprofit_tech
    Jan 3, 2013 at 12:17

1 Answer 1

Reset to default
2

While custom queries using the queries module will provide the most flexibility in designing a report like this, you may also want to take a look at NSM Reports. The goal of NSM Reports is to allow you to create custom reports faster, but store/save reports, and send reports to clients or automatically run a monthly report or so.

I've tended towards custom queries myself, so haven't used it before. But it's probably worth the look, if your going to allow fields to be randomly deleted. I believe it will pull a field list from ExpressionEngine for you.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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