You are here

Grouped Filter in Drupal Views Cardinality Violation

I was working on a view that listed faculty members on a Drupal site. The request was pretty straightforward. We have profiles on the site and each profile has one or more categories such as "Core Faculty", "Affiliate Faculty", "Administration", "Staff", "Graduate Student", "PhD Student", etc. This is done with a taxonomy term on the profile content type.

Now that we know how the content type is set up, we want to have a view that displays just faculty. By default this view should show all profiles who have the term "Core Faculty" and/or "Affiliate Faculty". This is simple to do. The hard part is they want an exposed filter that so that they can refine the list to just one of the two categories.

After messing around I eventually thought about using a exposed grouped filter on category. I set it up so the default is both catgories and it's not optional so it forces the filter on the inital load.

Grouped Filters screenshot

However, this came with an unfriendly error:

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

I can see what it's trying to do in the query and why it's invalid.

WHERE (( (users.status <> '0') AND (users.uid > '1') AND (field_data_field_category.field_category_tid = '1', '2') ))

If we just change the field_category_tid = '1', '2' to field_category_tid in('1', '2') we'd be golden. I did some Googleing around for this error message and I found an open issue and a workaround. Aparently all you need to do is select "allow multiple selections", save, and then uncheck "allow amultiple selections" and save again. Clears it up nicely.

Grouped filter 'Allow multiple selections' screenshot

This workaround has been around for 3 years, and it still hasn't been fixed. I am by no means an expert at views and how views constructs queries, but if you are or have the time to investigate it, feel free to work on the issue. It looks like there is also an issue for it in Drupal 8.