I am looking to show the total number of channel entries which do not have and empty matrix field.

For example I have entries which have a matrix field with customer information. I would like, if possible, to show all the channel entries which have no customer information added.

Any advice appreciated.

Thanks Ben

  • Updated my answer to include another option. – Alex Kendrick Apr 16 '13 at 21:13

One way is to use the search:field_name parameter on your Channel Entries tag. You can use it like...

search:your_matrix_field="not IS_EMPTY"

...and it will only return entries that actually have content in your Matrix field.

Alternatively, Adrian Macneil just released a nifty plugin called Channel Query that is perfect for this. It works just like the Channel Entries tag but allows you to specify a custom query to get just the entry_ids you need. So in this case you'd select entries where your Matrix field is not empty.

This will loop over all the entries that have Matrix data:

{exp:channel_query sql="SELECT entry_id FROM exp_channel_data WHERE field_id_22 != '' AND channel_id = '12'"}

Substitute the field_id for your Matrix field's id and channel_id for your particular Channel.

If instead you want only the total results you can just use the Query module:

{exp:query sql=
    "SELECT COUNT(exp_channel_data.entry_id) AS total
    FROM exp_channel_data
    JOIN exp_channel_titles
    ON exp_channel_titles.entry_id = exp_channel_data.entry_id
    WHERE field_id_22 != ''
    AND status = 'open'
    AND exp_channel_data.channel_id = '12'"

For either of these queries you can change to showing/counting only entries that do not have any Matrix data by altering the field_id_X != '' to field_id_X = ''

  • That pretty much covers every angle. :) – Ian Apr 16 '13 at 21:47
  • I need to limit this from one channel only which has a channel_id is 10.I tried adding WHERE channel_id ='10' but I get SQL errors. – Ben Apr 17 '13 at 22:35
  • Since there is already WHERE clause you need to add any additional conditions immediately after that by using AND. So for example WHERE field_id_22 != '' AND channel_id = '10' – Alex Kendrick Apr 18 '13 at 0:45
  • Thanks Alex, when I add the AND channel_id = '10' after the WHERE clause I get the following: "Column 'channel_id' in where clause is ambiguous" – Ben Apr 18 '13 at 10:21
  • Ah, OK, in that case it's because both exp_channel_title and exp_channel_data both have a channel_id field, so just specify a table for channel_id so it isn't ambiguous. Like this: WHERE field_id_22 = ''AND exp_channel_data.channel_id = '12'. I will update my answer. – Alex Kendrick Apr 18 '13 at 14:14

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.