I would like to display multiple value fields in the same row, for example in this way:
Field1, Field2, Field3

I select MULTIPLE FIELD SETTINGS > Display all values in the same row and "," as separator
Instead of getting one single row I get as many rows as there are fields:
Field1, Field2, Field3
Field1, Field2, Field3
Field1, Field2, Field3

I changed field type, number of values, display type (html list, unformatted list, grid, table). Always as many rows as there are values.

The only way to get rid of this behaviour is to select query settings > distinct . So I get a slow "SELECT DISTINCT" query.

Is it a bug or a normal behaviour? Is there a another way than SELECT DISTINCT?

Thanks in advance for your help.

CommentFileSizeAuthor
#2 views mutiple value duplicate.txt12.08 KBgilbertdelyon

Comments

dawehner’s picture

Title: Multiple value field display » Despite multiple value field display view returns duplicates
Status: Active » Postponed (maintainer needs more info)

Please export your view, so we don't have to guess what you configured.

Changed the title so it's possible to understand the general problem by just reading the title.

gilbertdelyon’s picture

StatusFileSize
new12.08 KB

Sorry, I realise that my description was not clear enough.

In the meantine I found the reason of this issue.
It comes from filter settings:
One of the view filters only allows nodes where the multiple value field is NOT NULL.
Notice that this issue occurs not only with fied display, but also with content display

When this filter is enabled each node is duplicated (in view content display and in view fields display+multi values in line) as many times as there are values in the field.
Without this filter nodes are not duplicated, but nodes where value field is NULL are also displayed.

Problem: I would like to display ONLY one time ONLY the nodes where value field is NOT NULL.
The only way I found was: advanced settings>query settings>distinct

gilbertdelyon’s picture

I found another way with hand coded query instead of VIEWS query:

1/ Basic VIEWS query style: DUPLICATES nodes as many times as there are values in the multi value field -> not the result I want

SELECT node.created AS node_created, node.nid AS nid 
FROM node
LEFT JOIN  field_data_field_multi_val_text ON node.nid = field_data_field_multi_val_text.entity_id AND (field_data_field_multi_val_text.entity_type = 'node' AND field_data_field_multi_val_text.deleted = 0)
WHERE (( (node.status = '1') AND (node.type IN  ('article')) AND (field_data_field_multi_val_text.field_multi_val_text_value IS NOT NULL ) ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

2/ Same VIEWS query with DISTINCT: DOES NOT DUPLICATE nodes.-> result OK

SELECT DISTINCT node.created AS node_created, node.nid AS nid 
FROM node
LEFT JOIN  field_data_field_multi_val_text ON node.nid = field_data_field_multi_val_text.entity_id AND (field_data_field_multi_val_text.entity_type = 'node' AND field_data_field_multi_val_text.deleted = 0)
WHERE (( (node.status = '1') AND (node.type IN  ('article')) AND (field_data_field_multi_val_text.field_multi_val_text_value IS NOT NULL ) ))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

3/ Hand coded query with WHERE EXISTS instead of LEFT JOIN: DOES NOT DUPLICATE -> Result OK, simpler and and faster than DISTINCT.

SELECT  node.created AS node_created, node.nid AS nid 
FROM node

LEFT JOIN field_data_field_multi_val_text ON node.nid = field_data_field_multi_val_text.entity_id AND(field_data_field_multi_val_text.entity_type = 'node' AND field_data_field_multi_val_text.deleted = 0)

WHERE ( (node.status = '1') AND (node.type IN  ('article')) 
AND  EXISTS  (SELECT 1 FROM field_data_field_multi_val_text
WHERE  field_data_field_multi_val_text.entity_id=node.nid))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

So, I would suggest to implement WHERE EXISTS in next views update!

merlinofchaos’s picture

Status: Postponed (maintainer needs more info) » Closed (works as designed)

Sorry, it is not possible to filter on a multiple-value-field without creating duplicates. This is the nature of SQL. The query to do that kind of thing is quite complex and not something that Views is capable of doing. This is true of sorting as well.

The only way you can use a multi-value field without creating duplicates is for displaying fields.