I have a view pulling in nodes that have multiple paragraphs referenced to them.
This works well and each node displays once in a table layout with the multiple paragraph fields displaying in each cell.
I currently have an exposed filter for searching some of the nodes fields.
The problem:
I would like to add an exposed filter to search the taxonomy in the paragraphs on the nodes.
In order to do that I need to add a relationship to the paragraph.
As soon as I add the relationship, the nodes repeat in the view for every paragraph associated with them.
If the node has 2 paragraphs referenced then the node shows 2 times. If I associate 3 paragraphs then the node shows 3 times.
Is this a glitch? How can I expose the paragraph filter and keep the nodes singular? I don't even need the paragraphs to be in the view. I just need to search for what paragraph data is in a node.
Am I missing something? I tried using distinct but can't get the distinct to work on the paragraph id nor the nid.
To reproduce:
Create a paragraph type with one or two fields.
Add an Entity reference of that paragraph to a node type. Make it the allowed values "unlimited".
Create a couple of nodes, one with two paragraphs referenced and another with one paragraph referenced.
Create a page view of these nodes. Only 2 nodes should show.
(In order to expose a filter with those paragraph fields, you need to add a "relationship".)
Add this paragraph as a relationship. Now you should have duplicate nodes showing. probably 3.
Tried Solutions:
distinct
aggregation
If I take the sql and run it through phpmyadmin, I can remove the paragraph field from the selection part of the query but leave the join aspect and it will display the right query still but I can't pull the paragraph field from the select in the views build.
Creates duplicates.
SELECT node__field_dtna_number.delta AS node__field_dtna_number_delta, node__field_dtna_number.langcode AS node__field_dtna_number_langcode, node__field_dtna_number.bundle AS node__field_dtna_number_bundle, node__field_dtna_number.field_dtna_number_value AS node__field_dtna_number_field_dtna_number_value, node_field_data.nid AS nid, file_managed_node__field_drawing_file.fid AS file_managed_node__field_drawing_file_fid, paragraphs_item_field_data_node__field_application_relation.id AS paragraphs_item_field_data_node__field_application_relation_
FROM
{node_field_data} node_field_data
LEFT JOIN {node__field_drawing_file} node__field_drawing_file ON node_field_data.nid = node__field_drawing_file.entity_id AND node__field_drawing_file.deleted = '0' AND (node__field_drawing_file.langcode = node_field_data.langcode OR node__field_drawing_file.bundle = 'cac')
LEFT JOIN {file_managed} file_managed_node__field_drawing_file ON node__field_drawing_file.field_drawing_file_target_id = file_managed_node__field_drawing_file.fid
LEFT JOIN {node__field_application_relation} node__field_application_relation ON node_field_data.nid = node__field_application_relation.entity_id AND node__field_application_relation.deleted = '0' AND (node__field_application_relation.langcode = node_field_data.langcode OR node__field_application_relation.bundle = 'cac')
LEFT JOIN {paragraphs_item_field_data} paragraphs_item_field_data_node__field_application_relation ON node__field_application_relation.field_application_relation_target_revision_id = paragraphs_item_field_data_node__field_application_relation.revision_id
LEFT JOIN {node__field_dtna_number} node__field_dtna_number ON node_field_data.nid = node__field_dtna_number.entity_id AND node__field_dtna_number.deleted = '0' AND (node__field_dtna_number.langcode = node_field_data.langcode OR node__field_dtna_number.bundle = 'cac')
LEFT JOIN {node__field_searchable_oe} node__field_searchable_oe ON node_field_data.nid = node__field_searchable_oe.entity_id AND node__field_searchable_oe.deleted = '0' AND (node__field_searchable_oe.langcode = node_field_data.langcode OR node__field_searchable_oe.bundle = 'cac')
LEFT JOIN {node__field_searchable_prefixes} node__field_searchable_prefixes ON node_field_data.nid = node__field_searchable_prefixes.entity_id AND node__field_searchable_prefixes.deleted = '0' AND (node__field_searchable_prefixes.langcode = node_field_data.langcode OR node__field_searchable_prefixes.bundle = 'cac')
LEFT JOIN {node__field_duralite_prefix_numbers} node__field_duralite_prefix_numbers ON node_field_data.nid = node__field_duralite_prefix_numbers.entity_id AND node__field_duralite_prefix_numbers.deleted = '0'
WHERE (node_field_data.status = '1') AND (node_field_data.type IN ('cac')) AND (node_field_data.title IS NOT NULL) AND ((CONCAT_WS(' ', node__field_searchable_oe.field_searchable_oe_value, ' ', node__field_searchable_prefixes.field_searchable_prefixes_value, ' ', node__field_duralite_prefix_numbers.field_duralite_prefix_numbers_value, ' ', node__field_dtna_number.field_dtna_number_value) LIKE '%FRDAC-5Q%'))
LIMIT 20 OFFSET 0
Does not create duplicates:
(removed "paragraphs_item_field_data_node__field_application_relation.id AS paragraphs_item_field_data_node__field_application_relation_")
SELECT node__field_dtna_number.delta AS node__field_dtna_number_delta, node__field_dtna_number.langcode AS node__field_dtna_number_langcode, node__field_dtna_number.bundle AS node__field_dtna_number_bundle, node__field_dtna_number.field_dtna_number_value AS node__field_dtna_number_field_dtna_number_value, node_field_data.nid AS nid, file_managed_node__field_drawing_file.fid AS file_managed_node__field_drawing_file_fid
FROM
{node_field_data} node_field_data
LEFT JOIN {node__field_drawing_file} node__field_drawing_file ON node_field_data.nid = node__field_drawing_file.entity_id AND node__field_drawing_file.deleted = '0' AND (node__field_drawing_file.langcode = node_field_data.langcode OR node__field_drawing_file.bundle = 'cac')
LEFT JOIN {file_managed} file_managed_node__field_drawing_file ON node__field_drawing_file.field_drawing_file_target_id = file_managed_node__field_drawing_file.fid
LEFT JOIN {node__field_application_relation} node__field_application_relation ON node_field_data.nid = node__field_application_relation.entity_id AND node__field_application_relation.deleted = '0' AND (node__field_application_relation.langcode = node_field_data.langcode OR node__field_application_relation.bundle = 'cac')
LEFT JOIN {paragraphs_item_field_data} paragraphs_item_field_data_node__field_application_relation ON node__field_application_relation.field_application_relation_target_revision_id = paragraphs_item_field_data_node__field_application_relation.revision_id
LEFT JOIN {node__field_dtna_number} node__field_dtna_number ON node_field_data.nid = node__field_dtna_number.entity_id AND node__field_dtna_number.deleted = '0' AND (node__field_dtna_number.langcode = node_field_data.langcode OR node__field_dtna_number.bundle = 'cac')
LEFT JOIN {node__field_searchable_oe} node__field_searchable_oe ON node_field_data.nid = node__field_searchable_oe.entity_id AND node__field_searchable_oe.deleted = '0' AND (node__field_searchable_oe.langcode = node_field_data.langcode OR node__field_searchable_oe.bundle = 'cac')
LEFT JOIN {node__field_searchable_prefixes} node__field_searchable_prefixes ON node_field_data.nid = node__field_searchable_prefixes.entity_id AND node__field_searchable_prefixes.deleted = '0' AND (node__field_searchable_prefixes.langcode = node_field_data.langcode OR node__field_searchable_prefixes.bundle = 'cac')
LEFT JOIN {node__field_duralite_prefix_numbers} node__field_duralite_prefix_numbers ON node_field_data.nid = node__field_duralite_prefix_numbers.entity_id AND node__field_duralite_prefix_numbers.deleted = '0'
WHERE (node_field_data.status = '1') AND (node_field_data.type IN ('cac')) AND (node_field_data.title IS NOT NULL) AND ((CONCAT_WS(' ', node__field_searchable_oe.field_searchable_oe_value, ' ', node__field_searchable_prefixes.field_searchable_prefixes_value, ' ', node__field_duralite_prefix_numbers.field_duralite_prefix_numbers_value, ' ', node__field_dtna_number.field_dtna_number_value) LIKE '%FRDAC-5Q%'))
LIMIT 20 OFFSET 0
Comment | File | Size | Author |
---|
Comments
Comment #2
strawman CreditAttribution: strawman commentedComment #3
strawman CreditAttribution: strawman commentedComment #4
strawman CreditAttribution: strawman commentedComment #5
efrainhI can confirm this is happening. I have exactly the same issue.
Comment #6
evanzyl CreditAttribution: evanzyl commentedI'm also getting duplicate notes. Distinct and aggregation not working. Any fix yet?
Comment #7
miro_dietikerThis is a duplicate of the issue about Views support in Entity Reference Revisions.
Comment #8
ñull CreditAttribution: ñull as a volunteer commentedAfter trying two patches that do not fix this issue, I now strongly doubt that this is a duplicate. When it is, then the patch in the related issue should have fixed this one. I therefore reopen this issue to force explanation of this discrepancy.
Comment #9
tjerkr CreditAttribution: tjerkr commentedI can confirm this is not a duplicate. I'm having the same issue as #8.
Patches from entity_reference_revisions didn't solve the problem
Comment #10
tjerkr CreditAttribution: tjerkr commentedIt seems to be related to https://www.drupal.org/project/drupal/issues/2993688
When you add a (paragraphs) relation, the DISTINCT doesn't work anymore on nid because (in my case) the worktype.id is added as a column to the SELECT part:
The above query results in duplicated NID's:
+-------------------------+------+----------------------------------------------------+
| node_field_data_created | nid | paragraphs_item_field_data_node__field_worktype_id |
+-------------------------+------+----------------------------------------------------+
| 1544454937 | 7 | 3 |
| 1544428064 | 5 | 29 |
| 1544195142 | 4 | 1 |
| 1544195142 | 4 | 2 |
+-------------------------+------+----------------------------------------------------+
To solve the duplicated nid's result I used hook_views_query_alter() to add a groupBy nid to the query with the Sql::addGroupBy method.
Resulting in the following query:
It seems to work as expected by design, due the fact we are adding multiple references (target_id's) for one node to the distinct, which result to duplicated node results in views.
Comment #11
miro_dietikerThis issue is a duplicate and i didn't review your case in detail. The job of the other issue is to provide a working views integration. We (the maintainers) are not claiming its patch is ready at all.
What is more important though is that the expected use cases are provided as tests so we can make the Views integration work as expected. We only have a single test until now.
Comment #12
ñull CreditAttribution: ñull as a volunteer commentedThanks @tjerkr for the work around. It works for me too!
Comment #13
lamp5Yess, solution from #10 works.
Full example here.
Comment #14
mloyat CreditAttribution: mloyat commentedSolution #10 would work for me if I had'nt an ORDER on a field of the related paragrap to do.
Any suggestion on how to do ?
Thanks
Comment #15
3liThanks #10 and #13 for an easy to understand working example.
Comment #16
yeskmilo CreditAttribution: yeskmilo as a volunteer commentedThanks #10 and #13.
#13 is the best way to add groupBy(), this case works when you have a node with a multiple paragraph field.
Comment #17
mehul.shah CreditAttribution: mehul.shah at Axelerant commented#13 worked for me as well.
Just note that if you add a relationship with a field which has multiple values, this is bound to happen.
Comment #18
hodba CreditAttribution: hodba commentedYou can simply resolve this without any code from the UI as the following:
- Add a new ID field and exclude it from the display
- Activate aggregation
- Configure the ID field aggregation to "Group results together"
- set Group column to "Entity ID"
Comment #19
janv CreditAttribution: janv commented#18 works
Comment #20
GuitarKat CreditAttribution: GuitarKat commented#18 worked for me, except all I had to do was activate aggregation, it seemed to know which Entity ID or something.
Comment #21
super_romeo CreditAttribution: super_romeo commentedIt's not a duplicate.
This issue about NODE view with related paragraphs.
And "Views doesn't recognize relationship to host (2799479)" about PARAGRAPHS view with related nodes.
#13 works for me.
#18 does not.
Comment #22
amaisano CreditAttribution: amaisano commentedFYI #18 does not work for JSON (REST) Views displays.
Comment #23
colanThis is indeed a duplicate of #2993688: Views relationships with multi-valued entity reference fields invalidate Distinct query option, which is more general as the problem is not confined to Paragraphs. As such, there should not be an issue about it in this queue.
Please direct all efforts to the Drupal core issue. Thanks.
Comment #24
VasyOK CreditAttribution: VasyOK commented#18 works
if Group column "Entity ID" set on every views field
Comment #25
jaesperanza CreditAttribution: jaesperanza as a volunteer and commented#18 works.
#24 Yes, it requires a consistent Group results by Entity ID on every views field (each one should have the SAME aggregate setting), otherwise blank error screen is produced.
Comment #26
jamesfk CreditAttribution: jamesfk at Website Express Ltd. commentedhodba, that's a genius workaround!
I had the same problem on a views with a file relationship, and there was no translation on the relationship to filter by. Aggregating never occurred to me, but it works well until this can be resolved - thank you :)
Comment #27
apadernoComment #28
Syavych CreditAttribution: Syavych commented#18 doesn't work for me, as generates an error: "Unknown column 'node__field_image.field_image_' in 'field list'..." (this bug is already registered, but still not fully fixed). Any other workarounds?
Comment #29
sjhuskey CreditAttribution: sjhuskey as a volunteer commentedInspired by #18 I tried something similar. The duplication, as others have noted, is being caused by a relationship on two fields. I turned on aggregation, but I didn't add a new ID field. I just set the aggregation settings on those two fields to "Group results together" by "Entity ID".
In my case, I wanted to use the First Name and Last Name field from a user's profile in a view, so I set up a relationship to get access to those fields. The trouble was that now I was getting triplicates of the nodes in the view: one for the original, another for the first name, a third for the last name. Aggregation on those two field reduced the number to one.
Comment #30
cegascam CreditAttribution: cegascam commentedHi to everyone, #18 and #24 worked great for me.
But i have a question, we are making a gallery site, so we created the colection as the taxonomy and the pices of art as nodes.
Thanks to #18 we could create a taxonomy view that shows the colections names and a image from the node (without #18 and #24 help, we had the colections repeated for every pice of art vinculated to the taxonomy).
My question is: can i make the image showed at my taxonomy to be random (every time from a diferent node vinculated to that taxonomy).
Thanks everyone for your help.
Comment #31
tvalimaa CreditAttribution: tvalimaa commented#18 works fine.
#28 You need to change image field aggregation settings group to target id
Comment #32
parthabapari CreditAttribution: parthabapari commented#18 works perfectly fine for me.
I had a page built with multiple paragraph items. It was showing duplicate nodes for each item. #18 worked perfectly for me.
Comment #33
Muttecht CreditAttribution: Muttecht as a volunteer commented#18 worked for me.
Comment #34
Muttecht CreditAttribution: Muttecht as a volunteer commentedComment #35
rroose CreditAttribution: rroose commented#18 didn't work for me, but what did work for me was changing the 'Rendering language' of the view (below Language at the bottom of the middle column) and setting it to 'Interface text language selected for page'.
Comment #36
Ralf Eisler CreditAttribution: Ralf Eisler commented@hodba #18 works perfectly, thank you!
and
@rroose #18 (considering #24) for me works in combination with #35.
I chose “Content language selected for page” for “Language” and “Content language selected for page” as “Filter criteria”.
Comment #37
AndyLicht CreditAttribution: AndyLicht commentedI´m working with Drupal 8.9.18
At the moment no solution is working for me. You can see my configuration at the attached image.
I´ve created a relationship between nodes and media object over a vocabulary.
The media object has the field field_tags and the node has the field field_mt_post_tags both are referenced to the same vocabulary.
Now i´ve got the problem of duplicates and that are all images shown if only one tag is used together, but i want to have every image once and i want that the media is referenced to all tags of the referenced tags of the node.
If i´m using the code of #10/#13:
I got the following error, so i have added the "media: ID", than i get the error in the view-display (second image).
Has someone an idea to solve that problem?
Comment #38
perfectcu.be CreditAttribution: perfectcu.be as a volunteer and commented#13 worked for me with the following tweak.
entity_type and id:
became:
Also (prolly doesn't matter) I have the hook_views_query_alter() implementation in a separate file: your_module.views_execution.inc.
Hint: check the query, chunked out at the top of the view preview area, for tables in use by the view:
Comment #39
filipetakanap CreditAttribution: filipetakanap commented@perfectcu.be can you tell me where to put the code, please?
Comment #40
tylired CreditAttribution: tylired commentedMy solution was similar to #35.
In my case, some of the results in the view had files associated with them, and other results use a URL instead.
I was getting duplicates on the results that use files because of a media relationship and the site has 2 languages. My solution didn't use aggregation at all. What I needed to do was add additional filter criteria and logic using a filter group option.
I added 2 additional filters, in my case, for field_file:media: Media, which was the field that required the relationship to Media.
One filter sets the translation language to the Interface text language and the other filter looks for this field to be empty. Then, if you click the drop-down arrow next to add in the filter criteria section and select "And/Or Rearrange", a modal window opens and you should see "Create new filter group" as an option. I created a new group, dragged both of the new filters for the same field into the new group, and changed the operator from AND to OR.
Here's a screenshot: https://drive.google.com/file/d/1s6mWNAQ4emWy81-wOY6_BhQ-nWYCyFXW/view?usp=sharing
This way the results without a file can be displayed and the results that have a file only show up once because of the filter looking at the interface text language.
Comment #41
perfectcu.be CreditAttribution: perfectcu.be as a volunteer and commented@filipetakanap,
You'll have to create a new module or tack the code into a module you're already working on. You can skip a lot of boilerplate pain by installing Drupal Console and having it plop out a shiny new module for you.
Let’s assume that the module you're hacking away on is called "your_module". All your module code is in a folder called "your_module" and this folder is located where Drupal's autodiscovery mechanism will pick it up; one of the modules folders.
In my case I already had a hook_views_query_alter() implementation in your_module/your_module.views_execution.inc
File:
sites/default/modules/custom/your_module/your_module.views_execution.inc
A couple of notes:
your_module/your_module.views_execution.inc
. I remember reading somewhere that you did, but I've lost the thread* on that one, and I haven’t bothered to confirm that hook_views_query_alter() works from your mainyour_module/your_module.moudle
file; it might.$Query
like lamp5 is, because doing so throws an exception if you're using search_api, as the $Query is sometimes and instance ofDrupal\search_api\Plugin\views\query\SearchApiQuery
and sometimes an instance ofDrupal\views\Plugin\views\query\Sql
. I implemented my hook_views_query_alter() call before googleing into lamp5's gem (thanks man!) and I think I was type hinting toDrupal\views\Plugin\views\query\Sql
not QueryPluginBase? Sounds like it's time for a TODO!-Dan
*PS found the thread ... maybe it's different in 8.9.x+? I've got it working so I'm lackadaisical about digging into the deets on that one.
Comment #42
Shifali Baghel CreditAttribution: Shifali Baghel at Srijan | A Material+ Company for Drupal India Association commented#13 worked for me. Thanks
Comment #43
Raphael Apard CreditAttribution: Raphael Apard commented18 works for me. But i need to choose "Standard derivation" for field of entity from the relationship
Comment #44
seorusus CreditAttribution: seorusus as a volunteer and at Attico International commented#38 works correctly for me.
Comment #45
omkar_yewale CreditAttribution: omkar_yewale as a volunteer and at Srijan | A Material+ Company for Drupal Association commented#43 Works perfectly for me, thank you!
Comment #46
BaniMelhem CreditAttribution: BaniMelhem commentedSometimes the duplication happens because of the language for the related entity from the relationship and can be solved by adding a language filter for the new related entity under the FILTER CRITERIA area.
Comment #47
fsayoub CreditAttribution: fsayoub as a volunteer commentedJust a note that #13 fails PHPStan as an outdated method. I'm running Drupal 9.3.16, PHP8:
Comment #48
guptahemant CreditAttribution: guptahemant as a volunteer and at QED42 commentedTo avoid PHPstan problem, method_exists function can be used
Alternatively instanceof can also be used which is better in terms of predictability, for method signature etc, Also follows OOPS pattern and a bit faster compared to method_exists
Comment #49
itamair CreditAttribution: itamair commentedThanks @guptahemant ... your #48 works for me
Comment #50
rameshbalda CreditAttribution: rameshbalda as a volunteer commentedfunction your_module_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
if ($view->id() == 'myviewid' && $view->getDisplay()->display['id'] == 'block_1' ) {
$query->addField('', 'nid', '', ['function' => 'groupby']);
$query->addGroupBy('nid');
}
}
Comment #51
lubwn CreditAttribution: lubwn commentedI have a very big query with lots of sorting and exposed fields so I was not comfortable with adding custom query to this, potentionally breaking things.
I came up with elegant solution without coding. You basicly only need to know which multi-valued field is creating the duplicates. Often it is some taxonomy or entity reference or reference to image field or so, and then you just add filter to this field with setting of DELTA lower than 1. This will eventually filter-out the duplicates completely. No aggregation or coding needed.
Comment #52
itamair CreditAttribution: itamair commentedAgree with @lubwn ... usually properly limiting with Deltas the presence of Multivalue fields (also from relationships) and DISTINCT query option solves the duplications.
Comment #53
preciado04 CreditAttribution: preciado04 as a volunteer and commentedComment #13 worked for me.
The only detail is, I had to modify a little bit the query.
Comment #54
Vacilando CreditAttribution: Vacilando as a volunteer commented#24 (based on #18) works perfectly, thanks!
Is there in the meantime a module that solves this problem in a generic way for views with this kind of relationships?
Comment #55
attheshow CreditAttribution: attheshow at Richland Library commented#53 is working well for me.