When I create a view of a product displays with only one field (e.g.; Content: Title ), I get one listing for each display. When I add a field for a referenced product (e.g.; (Referenced Product) Commerce Product: Image ), my view shows all of the variations encompassed by each product display. In other words, when I expect the view to show 2 items (the two displays), instead it shows ten items (the two displays X the five variations in each).

I don't know if this is related to: http://drupal.org/node/1200510 or if it's something I'm missing it setting up my view.

CommentFileSizeAuthor
#34 screenshot.jpg32.19 KBDonatoo
#34 view.txt22.27 KBDonatoo
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

rszrama’s picture

This is a known issue caused by the relationship. The underlying query produces a result for each product joined to from the node through the multi-value product reference field. I don't know the solution, but it seems to be related to needing some sort of single column distinct query in Views.

dbkern’s picture

That's exactly what I ended up doing. In fact, it may be better for some in that it allows you to manually specify which of the variations you'd like to display.

jantoine’s picture

Version: 7.x-1.0-beta4 » 7.x-1.x-dev

Hey Ryan,

How do I get a 'single column distinct query' in views as you suggest? I have the same issue and the only field that is making records distinct is 'commerce_product_field_data_field_product_product_id'. I am not adding this field anywhere, so I figured it is coming from the product relationship I added to get product fields. Any thoughts are appreciated.

Cheers,

Antoine

redna’s picture

Hi,

same problem here. My view is full of duplicated content. Is there any way to configure the view to show unique products?

Thanks in advance.

brunorios1’s picture

same here...

i tried to add a "Node ID" field excluded from the display and checked Distinct in Query Settings, but don't works...

thanks.

bojanz’s picture

Status: Active » Fixed

Wanted to answer this last night, but got gray-listed by drupal.org and then proceeded to bed..
Anyway...

I created a view of product display nodes. Added the relationship to products. Added two fields "Content: Title" and "Commerce Product: Image".
Got a row for every display node and product (so a node that references two products gets two rows). This is what I expect to happen.

Possible solution A:
1) I click on "Query settings" in the third column, and in query settings enable "Distinct" (it's a checkbox).
That kills the duplicates.

However, DISTINCT is usually a bit stupid, so it might not work in all cases. In those cases, you can try possible solution B:

2) I enable Aggregation (again, it's in the third column). I make sure to have a "Content: Nid" field added at the top of the fields list, so that it groups by that first. Might need to apply "MIN' to the title (by setting: Aggregation type to "Minimum" in "Aggregation settings" for that field).
Note that once you do that, the image will stop displaying. The solution for that is to go into "Aggregation settings" for the image field, and change the "Group column" from "fid" to "Entity ID". That makes it display again. I will create an issue in the Views queue to make this the default.

If both solutions fail for you, you will need to provide a view export in order for me to help you further.

rszrama’s picture

Wow, very cool! It'd be nice if we could aggregate all the referenced product IDs into the single "Add to Cart form" Views field to make it a multi-product form.

brunorios1’s picture

i need only one row to each Product Display.
i tried the solution 2) and i got this error:

An AJAX HTTP error occurred.
HTTP Result Code: 500
Debugging information follows.
Path: /mysite/admin/structure/views/view/catalog_terms/preview/page/ajax
StatusText: Service unavailable (with message)
ResponseText: EntityMalformedException: Missing bundle property on entity of type commerce_product. em entity_extract_ids() (linha 7389 de /home/myuser/wshared/www/mysite/includes/common.inc).

the view export: http://drupalbin.com/19721

thanks!

bojanz’s picture

@#8
You have malformed entities. Nothing I can do there. Open the {commerce_product} table in phpmyadmin, make sure the type column is never empty.

brunorios1’s picture

@bojanz

I found the mistake.

I have another field in the view, "Product: Price", but i forgot to change "Amount" to "Entity ID" in the "Aggregation settings -> Group collumn"...

now it's working perfectly!

Thanks for you tip!

brunorios1’s picture

Status: Fixed » Active

@bojanz,

i added a sort criteria "Commerce Product: Price (commerce_price:amount)" and checked "Expose this sort to visitors, to allow them to change it"...

but this breaked the aggregate functionality and shows all variations again.

there's a way to fix it?

thanks!

Camario39’s picture

I will have to try this as well.

I was thinking of trying "distinct" as well but in a normal SQL query you would just GROUP BY nid, title, and just MIN() MAX() AVG() everything else, right?

amfis’s picture

#6 worked for me, even exposed price filter works.

ReeceMarsland’s picture

#6 works if your using fields, now to find a solution if your using node/content display format.

jantoine’s picture

@bojanz,

I don't understand the agregation settings yet, but your solution in #6 worked for me. Thanks!

Cheers,

Antoine

MickL’s picture

#6 solution B works !! thanks alot !! :)

msmithcti’s picture

I tried the first solution in #6 and that worked fine as long as the 'Show' was set to content. As soon as I changed that to fields the problem resurfaced again. So I tried the second fix, I ended up not being able to view or edit the view. This is the error I'm getting:

Error
The website encountered an unexpected error. Please try again later.
Error message
EntityMalformedException: Missing bundle property on entity of type commerce_product. in entity_extract_ids() (line 7389 of path_to_drupal\includes\common.inc).

Bit of a show stopper to say the least!

Are there any ways around this?

rfay’s picture

Related conversation on drupalcommerce.org

joachim’s picture

Title: View of Multiattribute Product shows all variations » View of products with multi-value product reference field show multiple rows per product node

Changing title to make this issue easier to find.

joachim’s picture

As far as I can tell, the fix proposed in #6 won't work if you also want your view to have exposed filters for properties that are on the product entities.

EDIT: scratch that, it works :D

rfay’s picture

@joachim, #18 was the thing I was trying to find on drupalcommerce.org :-)

joachim’s picture

> the fix proposed in #6

How about addinng a sample default view with set up in as a starting point for users?

> Wow, very cool! It'd be nice if we could aggregate all the referenced product IDs into the single "Add to Cart form" Views field to make it a multi-product form.

Another use case with the same requirement: suppose I have two products in the same display node, one is red and one is blue. I'd like to show my view as:

node title: Shirt
price: £10
available colours: red, blue, green

For both of these what I reckon we need is the ability to do this in the query:

GROUP_CONCAT(commerce_product_field_data_field_ref_product.product_id SEPARATOR ',') AS commerce_product_field_data_field_ref_product_product_id

We'd either need special Views handlers that add this to the query, or #1359298: Support Plugins for Views Aggregate in D7 (D8 Backport) in Views.

joachim’s picture

I've filed a patch to Views that adds the GROUP_CONCAT aggregation function. This would allow us to have a field handler for product ID which returns data in the form '1,2,3,4', ie, comma-separated product IDs.

We could then have the Views field handler for the add to cart form split that list up and from that make a multi-product add to cart form.

Patch is here, needs review! :D #1362524: add GROUP_CONCAT aggregate function; and move aggregate function helpers to a query extender?

rszrama’s picture

Oh man, that would be soooo awesome. Thanks so much, joachim!

jkthng’s picture

I faced the problem myself and this is my solution.

Requirement
-----------
I needed to have the product referenced 1st in my product display to appear.

Using the aggregation method #6 works (combining it with grouping columns together) but I am unable to select the first referenced product to appear in my product display views.

Suggested Solution
----------------
What I did was to add a filter for the product reference field and set the delta = 0. This removed all the duplicate product displays. So far (fingers crossed) I haven't found any disadvantages of doing it this way.

Hope this helps someone.

deggertsen’s picture

@jkthng Thanks! I couldn't get the #6 solution to work, but setting the delta to 0 in the field_product_reference:delta filter fixed the problem perfectly.

Darren Shelley’s picture

I personally use the solution proposed in #25 and it hasn't failed me.

As an additional piece of information:
If your view is returning "(Product) Commerce Product: Price" and you want this price to be indicative of the cart attribute chosen. You can do the following:

  1. add the field 'Content: Nid (Nid)', move to the top and exclude from display
  2. in the settings of '(Product) Commerce Product: Price' chose 'Customize field and label wrapper HTML', set the 'wrapper html element' to 'div', select 'create a css class' and set the class to: "node-[nid]-product-commerce-price"

A prime example of when this would be handy is if you had a product which has both ebook and print counterparts, and the price varied dependant on which was selected.

JMTorres’s picture

Thank you jkthng for #25. I wish I would have thought of that earlier. This may solve a lot of my views relationship issues. The only way I can think of that this could cause an issue is if for some reason something didn't get saved with a delta 0. I personally haven't encountered this since D5 with the Location module.

dianikol’s picture

#25 worked for me too. Also works perfect even with AJAX exposed filters and sorting

jazzdrive3’s picture

So is anyone currently working on the Add to Cart form field handler for views so attributes can be selected? It looks like #23 is a step in the right direction and just curious if any progress has been made. I can add my help where needed. It seems like a feature that is needed.

philipz’s picture

#25 worked great! So simple :)

#14 I am trying to create this view using Display Suite display format and it's sort of working... almost. I attached the Price "field" on product display node form for teasers and then make the taxonomy terms view display DS thumbnails. The products are showing and price is showing as expected. I don't have to set Product relationship in a view for this to work.
The problem is that somehow the depth filter is not working. The products display only if I'm on its' terms page and not those terms parents terms. The same view works with depth if I switch it to fields display format.

frost’s picture

+1 for the suggested enhancement in #30.

Use case:
Product type: has taxonomy 'catalogue' and field 'assembly' (values 'assembled' or 'flat pack')
Products: P1 and P2, which have all values identical except for 'price' and 'assembly' (one has 'assembled' and one has 'flat pack')
Product Display: Single product display D that references both P1 and P2.

When viewing D as a standalone node, I see the dropdown for 'assembly'; when I choose a value, the price automatically changes. Happiness.

Use case: catalogue view
For the online catalogue, I need the view for taxonomy term to show a table of products with title, image, description, price, choice of assembly and add to cart. The dropdown should work exactly as it does with the single node above - ie select the assembly and the price updates.

Using ideas in this thread I can get as far as reducing duplicates so I have a single row in the table which shows D with the values of P1 displayed. I can't get the dropdown to display. The closest I can get it to make the 'assembly' field a user-modifiable filter, but of course then it applies across the whole view, when what I want is to have that on the individual lines of each product where it applies.

Summit’s picture

Hi,
Wouldn't it be great to have a view like this in the module: view of related product by product reference field?
Does somebody have a correct clean related product view which can be attached here please?
EDIT: Found this one! http://drupal.org/node/1276450 Tuned it a little bit. Only problem is excluding current product in node, see: http://drupal.org/node/1505198

Greetings, Martijn

Donatoo’s picture

FileSize
22.27 KB
32.19 KB

Hi everybody!

We're developping a charity website. For this purpose, we followed Randy Fay's 'Using Custom Line Items To Provide a Donation Feature for Drupal Commerce" (http://www.commerceguys.com/resources/articles/238) screencast.

Now, we've been facing an annoying issue with the shopping cart views when using "Relationship: Content: Referenced Product" to retrieve the title of the node a donation is referenced to.

Our problem is now the following:

Basically, after making a donation, we're viewing our cart block (see screenshot attached). As you can see, the shopping cart is listing every node containing our donation product reference and we're stuck with that.

We tried both solutions in #6 but since the nid is different, it won't work.

Any idea on how to fix this?

dianikol’s picture

Did you try #25??

Donatoo’s picture

Thanks, I did but didn't manage to find the necessary field to do the action.

I'm not sure duplicates is the right term no more. In fact, If you take a look at my screenshot, you'll see that all the nodes referencing the donation product are being displayed.

Am I living in hell?

dianikol’s picture

Hmm try to find product_delta in the filters and set it to 0 to see what happen

Donatoo’s picture

Tried it, doesn't work either.

Darren Shelley’s picture

@Donatoo, this may help you or others looking:

If you want to create a view of commerce product presentation nodes and need the additional information such as price and the 'add to basket button' then in most usecases i would say the best approach is to create a 'product listing' view mode using display suite: http://drupal.org/project/ds. (experienced users may also want to consider: http://drupal.org/project/entity_view_mode)

Much like a full node and teaser, display suite will let you choose which fields are shown in that view mode. You can also select order.

Once you've created one you can remove the relationship on the products field and change your view to either have a 'rendered entity' row style OR add a field to show the complete entity, and in either select 'product listing'.

Now this won't cater for everything, if you wanted to show a full price listing you'd want to tackle that with relationships so that you have one row per price.

But as this thread is all about showing only one instance per node (even where there is more than 1 underlying product) and most people are trying to do is create a product listing page then this seems a viable option.

Using a delta of 0 or distinct will be very problematic if your pricing structure is different dependant on the sub product selected.

Donatoo’s picture

Thank you Darren, DS is already installed (since it's an amazing module) and I'm going to check this out.

TechNikh’s picture

filter with delta = 0 removed duplicates. hope it doesn't miss any products!!

jozzhart’s picture

#41 Worked for me

carrierawks’s picture

This is how my view is setup, and I can't figure out what exactly delta=0 is supposed to be. I am trying to just show a couple of products and a link their use applications on a website. If someone could look it over, maybe they could save me from insanity! lol Anyway here is what I have doing for my Drupal 6 install:

$view = new view;
$view->name = 'application_products';
$view->description = 'Product applications';
$view->tag = '';
$view->base_table = 'node';
$view->core = 6;
$view->api_version = '2';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'nid' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'absolute' => 0,
      'link_class' => '',
      'alt' => '',
      'rel' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
      'strip_tags' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'hide_alter_empty' => 1,
    'link_to_node' => 0,
    'exclude' => 1,
    'id' => 'nid',
    'table' => 'node',
    'field' => 'nid',
    'relationship' => 'none',
  ),
  'tid_1' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'absolute' => 0,
      'link_class' => '',
      'alt' => '',
      'rel' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
      'strip_tags' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'hide_alter_empty' => 1,
    'imagecache_preset' => 'uc_thumbnail',
    'link_to_taxonomy' => 1,
    'exclude' => 0,
    'id' => 'tid_1',
    'table' => 'term_image',
    'field' => 'tid',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Override',
    ),
  ),
));
$handler->override_option('sorts', array(
  'tid' => array(
    'order' => 'ASC',
    'id' => 'tid',
    'table' => 'term_data',
    'field' => 'tid',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'vid' => array(
    'operator' => 'in',
    'value' => array(
      2 => '2',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'vid',
    'table' => 'term_data',
    'field' => 'vid',
    'relationship' => 'none',
  ),
  'delta' => array(
    'operator' => '=',
    'value' => array(
      'value' => '0',
      'min' => '',
      'max' => '',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'delta',
    'table' => 'node_data_field_type',
    'field' => 'delta',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('style_plugin', 'grid');
$handler->override_option('style_options', array(
  'grouping' => '',
  'columns' => '4',
  'alignment' => 'horizontal',
  'fill_single_line' => 1,
  'summary' => '',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('fields', array(
  'all_tid' => array(
    'label' => 'All Terms images',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'absolute' => 0,
      'link_class' => '',
      'alt' => '',
      'rel' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
      'strip_tags' => 0,
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'hide_alter_empty' => 1,
    'imagecache_preset' => '',
    'link_to_taxonomy' => 0,
    'only_one_image' => 1,
    'limit' => 1,
    'vids' => array(
      2 => 2,
      1 => 0,
      3 => 0,
      4 => 0,
    ),
    'exclude' => 0,
    'id' => 'all_tid',
    'table' => 'term_image',
    'field' => 'all_tid',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'vid' => array(
    'operator' => 'in',
    'value' => array(
      2 => '2',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'vid',
    'table' => 'term_data',
    'field' => 'vid',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('path', 'applications');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'primary-links',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
  'name' => 'navigation',
));
ahimsauzi’s picture

I have a few fields, some are not exposed and reused as a rewrite. #6 worked for me but I had to apply both aggregation and distinct. The Commerce field used are Price, Image and add to cart.

As far as aggregation settings, I followed #6 recommendation however I noticed that checking any of the Group Columns (additional) boxes, breaks the view.

Hope this help.

kiwimind’s picture

I wish I'd found this thread back when I was building my last store when I was having this issue. I ended up getting round it by adding a reference back to the entities, asking the store admin to choose one as a default display.

I'll be trying these ideas soon.

gynekolog’s picture

#6 Works fine but what when i have a multiple attributes and i want view all attributes in one row (with one node title and one picture)?

chanella’s picture

#6 wasn't working for me but this one did it. Thanks!

lyle@4fs’s picture

Hi Guys

I am trying to aggregate an entity reference pulling in product images, is this possible?

Is there any way I could limit the variation being pulled in through the entity reference as I have a few variations for each product within my view.

Regards

dedek’s picture

Hi Guys, try this, work for me for fields as well as content:
in views do this:

Use aggregation: Yes
in Filter criteria > add: Content: Nid
in Content: Nid: Operator -> Is not empty (NOT NULL)
in filter Aggregation settings: Aggregation type -> Minimum

sjs205’s picture

#6 didn't work for me, #41 worked like a charm...

edboost’s picture

#25/#41 is brilliant.

As it took me a little while to find the right filter:

1) Choose filter: "Content: Product (field_product:delta)"
2) Operator "is equal to"
3) Enter 0

Dupes disappear. Thank you!

gratefulsk’s picture

That was it! I can confirm that edboost's solution works nicely.

brunorios1’s picture

The problem with field_product:delta = 0 approach:

I'm using a filter "Commerce Product: Stock > 0" to list only products available in stock.
I have two product variants (size P and size G) in the product display (Cool T-Shirt).
The P size is the delta = 0 and the G size is the delta = 1.

If i use the filter "field_product:delta=0" the Cool T-Shirt product display won't be displayed, because the delta = 0 is out of stock, althought the delta = 1 is available.

We need a workaround for this use case.

Instead, i'm using aggregation until we have a workaround.

Thanks!

joachim’s picture

In SQL, would there be a way to JOIN to the minimum delta?

If so, we could write a Views custom relationship handler for that.

Isostar’s picture

I also have a problem with the delta = 0 approach.
When a product is disabled, but stil is referenced as first product in a display, a price of 0 is shown.
I would like to show the first referenced product which is activated.

Aggregation gives me SQL errors.

dianikol’s picture

There is a problem with the delta = 0;

Suppose that in the product (not in product display) i have a field witch is for color for example. In the view i have a filter with that color field being exposed. If i filter the view with color it won't show anything accept the first product (delta = 0) has that color. If the second variation (delta = 1) has that color it won't show up in the view.

If i remove the delta = 0 filter i have a view with thousands of product which is unwanted.

What do you suggest for that?

gynekolog’s picture

dianikol:
I use aggregation and all is working.

edit: and for all values (color, size..) i use http://drupal.org/project/field_extractor

joachim’s picture

> If i filter the view with color it won't show anything accept the first product (delta = 0) has that color. If the second variation (delta = 1) has that color it won't show up in the view.

You need a GROUP_CONCAT on the colours if you want them all to show.

howardhuang’s picture

#25 fixed the duplicated result and #27 did the price change trick, Thanks

FriedJam’s picture

I've found that switching on aggregation can cause other issues, but there is a bizarre workaround. If you go through the process outlined in #49 et al, save the view, then switch aggregation off, it seems to solve the problem with actually needing to use aggregation.

It's a bit hacky - I haven't tested whether it stays fixed once you start adding/removing fields. It could point towards a potential long-term fix though. Whatever aggregation is doing, it doesn't seem to need the whole thing to work correctly, so maybe we can find the right bit & separate it as an option in views?

rutan’s picture

#60 Works for me aswell in combination with a nid field, feels like a rather ugly solution. It would be great if this functionality could be fixed to work more reliable. I tested to add and remove fields and that works. But if I remove my "nid" field, it breaks and I need to do redo it again.

dianikol’s picture

A workaround that i use is to alter the query when the user search a product that is a variation. Initially i have a delta = 0; Then if the user search a variation i change the delta to a higher number and then it returns the appropriate results

drupalerocant’s picture

#60 doesn't work for me. Whenever I turn off aggregation the duplicates appear again.
I have to use aggregation to get rid of the duplicates.
The problem I have now is that when a customer chooses a product from the view to put it on the shopping cart, then chooses one variation which is in my case a size XS T-shirt. This can be a problem as if the customer doesn't realize and has a XL size, can buy the wrong t-shirt.
Is there a way of skipping this problem?
Thanks!

TechNikh’s picture

ideally the list view should show a dropdown field of products, just like the product display node view page.

PhilY’s picture

Hi,
As a lot of users, I want to display the products display nodes in grid, so I required a custom view.
But I needed several other store product's fields also: sku, price, image.
And then, the view becomes messy with product variations: duplicates.
I tried 'distinct' sql request or 'aggregation' tips without success (mostly because of all the added fields?)

But, #25 (avoids duplicates) and #27 (displays field's value according to the selected variation) did the trick and it now works as expected.

Commerce module should definitly come with an inactive example view (as Views comes with an alternative to taxonomy display).

Thanks to all for being part of the wonderfull Drupal community.

Barto.G’s picture

Using #25 is the easiest solution .

#6 also works but if you have 2 products sharing a variation add #25 solution to make sure you get the fields from the first variation (image and price for exemple)

Sinan Erdem’s picture

delta trick on #25 works for me also. But one problem of this approach is that, if you want to use exposed filters to filter some products, it will only search inside displayed variations (the variations whose delta = 0) only. For example: if you have a "color" filter, and if the user searches for "red" products, it will only show the products whose first variations are red.

Edit: Sorry, this issue is stated on comment #53 before.

Sinan Erdem’s picture

Kickstart uses the approach explained on comment #39. One tricky thing is that, if you use this approach, exposed filters for product variations' attributes (size, color, etc) are not available. In Kickstart, they used Search API and created an index table, indexed the fields for attributes. Then, when creating the list view, they used that index' table instead of content table. This way, you can have explosed filters on those attributes.

immoreel’s picture

While the solution offered in #25 worked like a charm, the update to v. 1.8 breaks this functionality, I can no longer choose a relationship....

pixelsweatshop’s picture

Category: Support request » Bug report
Priority: Normal » Major
Issue summary: View changes

I am going to move this to a bug report and bump this up to major. The solutions provided in this thread are workarounds and don't work for every scenario. Commerce also depends on views so this should be cleared up as it doesn't behave as expected. Feel free to disagree with me though. :)

rszrama’s picture

Category: Bug report » Support request
Priority: Major » Normal

I'm moving this back, because it isn't a bug... just the way database queries work. : P

What solutions above did you try that didn't work for you? I'd think filtering by product reference delta value should work in most cases.

Kolo’s picture

Pricing method explained in [#27] works only with "Unformatted list", "grid" and "HTML list" formats. Any idea how to make it work in table format?

pixelsweatshop’s picture

What solutions above did you try that didn't work for you? I'd think filtering by product reference delta value should work in most cases.

I tried this and because I have other content types without a product reference field in the same view those disappear.

Turning on Aggregation and grouping the results throw up an error:

Notice: Undefined index: uri in theme_image_formatter() (line 602 of /modules/image/image.field.inc).

If I used a multivalue field with the entity reference module and add that as the relationship in the view, it doesn't create duplicates. So is this unique to the product reference field in commerce?

hockey2112’s picture

#25 seems to have fixed my issue! Thanks!

leendertdb’s picture

#6 and #25 didn't seem to work for my usecase with multiple custom attribute fields like color, size etc.

What I ended up with is using the hook_views_pre_execute() function in a custom module which adds a group by command to the SQL query which in turn eliminates all the duplicates based on the node ID.

/**
 * Implements hook_views_pre_execute().
 *
 * Rewrite SQL query to prevent duplicates based on NID
 */
function my_module_views_pre_execute(&$view)  {
  if ($view->name == '<your_view_name>') {
    $query = $view->build_info['query'];
    $count_query = $view->build_info['count_query'];
    $query->groupBy('nid');
    $count_query->groupBy('nid');
  }
}

Replace "my_module" with your custom module prefix, and "your_view_name" with your view name.

Edit 2015: With another project I used this code on I noticed that the paging count is off when you use this snippet since the paging count is calculated by a different query (comment #77 also mentioned this). I have added the count_query modifier to the snippet above. It should now work like supposed.

zmove’s picture

and change the "Group column" from "fid" to "Entity ID"

I don't find that option under the aggragation setting of a field, I only have a select list to chose group results, min, max, sum etc.... where is that option ?

TaraRowell’s picture

@leendertdb
Thank you for the snippet! It works really well. Now I only have to figure out why my pager does not read the value properly. It thinks there are still duplicated values and wants to page 'phantom' results.

hockey2112’s picture

Turns out, #25 did not fix my issue. When that delta filter was enabled, the filtered view would only return Product Displays that had a Product the selected option "first" in the Product Reference list.

In order words, if Product A had SKU 123 (color: red; size: large), SKU 456 (color: blue; size: large), and SKU 789 (color: green; size: large) in its Product Reference field, and you filtered the View by "Color > Blue", SKU 456 would NOT be one of the returned values.

My fix was simple enough: I removed the delta filter, and I went to Other > Query Settings and checked "Distinct". I still need to do more testing, but it looks like this did the trick for me.

dankung1’s picture

method of #25 is awesome, thank you, @jkthng

I faced the problem myself and this is my solution.

Requirement
-----------
I needed to have the product referenced 1st in my product display to appear.

Using the aggregation method #6 works (combining it with grouping columns together) but I am unable to select the first referenced product to appear in my product display views.

Suggested Solution
----------------
What I did was to add a filter for the product reference field and set the delta = 0. This removed all the duplicate product displays. So far (fingers crossed) I haven't found any disadvantages of doing it this way.

Hope this helps someone.

mmcmmc’s picture

#79 add a filter for the product reference field and set the delta = 0
Thanks, dankung1!

fahadurrehman’s picture

For Me this Worked Perfectly https://www.drupal.org/node/1200536#comment-5452090
Filter results by delta.

justtoomany’s picture

Hi everyone it's been a while since I started searching over the web for a workaround in an issue about product catalogue building but haven't found it been analyzed satisfactorily yet and although it might be a litle bit irrelevant to this topic I believe it's the best place I've found to start the discussion.

The topic in this issue explains how to list products manipulating either fields or nodes and focuses on how to avoid duplicates.
First of all I agree that for the list of content case the solution is the distinct setting for "dangerous" filters and product reference delta filter set to 0 for the list of fields. .

What happens when you try building an advanced product catalogue using drupal7 commerce? Focusing on showing a view of all node teasers without dublicates or product variations without variations of the same product display appearing one after the other, is something very importand, but it is when attempting to take the view a bit further adding exposed filters that one has to really make up his mind between fields or nodes and the dilemmas one reaches at that point haven't been spoken yet or at least dealt with extensively! I am still in search of how to achieve the simplest but errorless senario of a complete view of products with exposed filters for plain fields and attributes.

If you use fields listing approach and your exposed filter is a node field having used the product reference delta fitler will work as desired but if the exposed filter is a product field( attribute), having set product reference delta to 0 causes the view to search for the attribute only in the first referenced product so only if your lucky the right entity might show up. Even if one can find a way around this ( can't recall the solution right now and not sure if I found one actually) , then another problem comes up, which is when finally clicking on the desired list of fields eventually, you land on the node page the shows the wrong variation or again only if you are lucky and have clicked on the variation that is listed first in the product reference field of the matched product display you will land on node display with the right variation.

The node listing approach doesn't need a delta field to work perfectly and won't have a problem always finding the attribute choosen in exposed filters without causing any duplicates (just use distinct in query settings) but then again when a customer checks black(top) and gets a node teaser that does have a black(top) variation somewhere but demonstrates a blue(top) one, there is a big problem. This appoach at least doesn't mix the customer up when finally landing on a node page since he finds the same product variation appearing as the one in the teaser he clicked ( the first one referenced in product display).

I also have to point out that the scene becomes as simple as described as long as the fields in product display and Product bundle are organised in a way that won't lead in using the product reference relationship to retrieve any information that could have been set on the product display side. Furthermore not using nested taxonomys keeps exposed filters consistent and in addition although allowing multiple values for exposed filters will cause some fustration, having all the above in consideration, there shouldn't be an issue there. I've tried some things on different occasions including commerce product urls with tokens or the relationship that appears after adding the relationship taxonomy terms on node ( Taxonomy term: Commerce Product using color). Taxonomy Entity Index also sounded promising but still can't figure out a good solution or imagine how others have gotten this product display - variation organizing of products to actually do the trick without causing havock in other functional areas. At the time being I'm working on a not perfect but alluring front end workaround that some of us might settle for. Having not solved such a should-be basic issue definitely makes me wonder whether I'm one of the few that haven't stumbled over the "how to" yet or am missing out on something not trivial. I would appreciate some guidance.

alex.87’s picture

Hi @justtoomany, i just came to a point where you are.

Doing my first Drupal Commerce website, i never know that it would be so limited with simple filters of different variations of a product.

I mean how to say to a client that you either need to show 5 same products on main products page ( black shirts with diferent sizes variations ) to be able to use exposed filters, or other scenario is to tell them to remove size from filters...

Did you found any working solution to this?

rszrama’s picture

I don't think you need to tell your client anything... without first trying any of the several solutions listed above. : P

Have you read the thread and tried things, like the delta based filter?

alex.87’s picture

Not telling anything yet :) just trying...

So my current setup is this:

  • Im using 2 taxonomy vocabularies Color and Size
  • For one product display i have up to 12 variations

My goal is to have all color variations on my cataologue page, but not sizes. And when user clicks on red t-shirt he gets to a product display where red color is displayed, but it still have option to change colors and sizes there.

So i tried 2 ways of creating my product catalogue.

First way: With Search API and Commerce Search API i created my products catalogue exactly the same as in Commerce Kickstart, and described in their video tutorial on commerce guys website.

So it lists me all of my product display nodes, but the problem is that im getting only products that are black color. This is because for every product, i have added black variation first and than other colors.

Other problem with this approach is when user is using facet color filter and select red, it will still get only black color in results even if the filter is applied and page reloaded.

This isnt good UX because user wants red shirt, he select RED in filter but he gets black shirt as a result.

So obvious solution here is to add one product display per color. But than i lose color selection on add to cart form. If i could only find a way to add this color attribute on add to cart form but to have one product display per color.

Second way to create product catalogue is: using Views > Product Display. But than to be able to get fields that i want i need to add Relationship to referenced product.

When i add relationship it lists me all product variations ( 12 per piece ) witch is too much, as i dont need different sizes, only colors.

Using distinct and Aggregate isnt working, distinct have no impact at all, and Aggregate trows me error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS field_data_commerce_price_, field_data_field_product_image. AS field_data_fie' at line 1

Third solution is to add filter for the product reference field and set the delta = 0, but than it excludes all other variations and exposed filters and unusable.

Dunno where to look next.

justtoomany’s picture

Hi @ul71m0. You got me a bit mixed up, maybe you should refrase you second way method. If I were you I'd use product display listings and wouldn't mind adding a relationship. Just be sure to choose the relationship when adding product side fields in views filter criteria. Something else very important is that when adding fields on the product entity type side I choose entity reference cause taxonomy reference didn't work as expected. That plus distinct might work for you. If you take a close look at my previest post you'll find that this case gets rid of dublicates and makes the exposed filter attributes work but your customers still have to make some assumptions, meaning the solution isn't perfect yet. After trial and error I realized that a javascript solution (haven't used drupal behaviours which is recomended) works but only after the default result has been rendered. example: you tick black,a blue shirt comes up and it instantly switches to the black variation.
The view off fields approach supposedly doesn't need something extra code to get the right variation shown, but just like the product display teaser listing case, after selecting a specific variation you always land at the node page showing the first variation referenced ~usually the wrong one.

Greetings @rszrama, I would really appreciate it if you could comment on my post #82 ( also mentioned in this one ). There doesn't seem to be a solution for my issue. I am the first student in my university undertaking a Thesis with Drupal 7 (or any).. and your expertise for it's succesful completion will also benefit future students.

alex.87’s picture

Huh i didnt think that i understood you, but what i want to achieve is

  • To have all colors variations on my product catalogue page
  • Than when user select one variation and enters single node, to have ability to chose other colors variations in Add to cart form
  • To have working filters, and when you filter buy red color to show only red prodcuts, and you click on one of them, landing color on the page to be red variation of a product. But this requriment have lowest priority, frist two are most important

Till today i didnt found solution for this.

joegl’s picture

I am having this issue as well. I've been able to get the attributes through the views, which is nice, but I can't seem to get a price field that will update when an attribute is changed. Does anyone know how I can get a variable price field through views? I am using the content: Product field with the Add to Cart Form display but that does not include a price at all. When I add a price using relationships, that field doesn't get updated when the attribute changes.

justtoomany’s picture

@ul71m0 I'll go backwards.

To have working filters, and when you filter buy red color to show only red prodcuts, and you click on one of them, landing color on the page to be red variation of a product.

That is the main problem I analyzed in my post #82 and which frustrates me the most. Well it's the details that distinguish a profesional from the rest of the crowd..

Than when user select one variation and enters single node, to have ability to chose other colors variations in Add to cart form

That's standard functionality. You should make the color field act as an attribute on add to cart form. Don't tick that box for the size field since you don't need it.

To have all colors variations on my product catalogue page

Again standard functionality. You should show fields not nodes. If the problem is the sizes variations showing up both in the view and the add to cart form, I don't see a reason to use a size field at all.

alex.87’s picture

@justtomany Let me answer you

That is the main problem I analyzed in my post #82 and which frustrates me the most. Well it's the details that distinguish a profesional from the rest of the crowd..

Got this one.

That's standard functionality. You should make the color field act as an attribute on add to cart form. Don't tick that box for the size field since you don't need it.

Well its the standard functionality when you have one product display for all the attributes, but in case when you have one product display per color than you cant select other colors . Because when you add one product display per color than everything other works (proper listing and filters ), so if i can just find solution how to add rest of the colors when i create one product display per colo that will solve all problems.

Again standard functionality. You should show fields not nodes. If the problem is the sizes variations showing up both in the view and the add to cart form, I don't see a reason to use a size field at all.

Its not standard functionality when you use Search API to create your product catalogue, if you check in Commerce Kickstart this is the way they created product catalogue page.

And size is important attribute to client so we cant go without size on add to cart form.

I think you didnt understood me on this. If i just create view of product display, and add relationship product reference that i will get all possibile variations including all colors and all sizes showing in my view... So i will have red shirt showing up five times in my product catalogue ( xs s l m xl )

alex.87’s picture

Still no solution on this... ?

mihail.andreev’s picture

Hello to everyone. This topic is a good one. I have a project that required to combine the products into one. I've tried a lot of ways and ended up here. As everybody tried #6 I used it too, but with one exception which might be helpful to others. Solution #1 didn't worked neither #2. I have product display from Views with so many filters which i think is the problem. My solution was to combine the two methods in one. I made Aggregation and Query Settings. Fingers crossed that will still work after a few days of working.

adriaav’s picture

I've been using the filter "Content: Product variations (field_product:delta) = 0" for a while and its working pretty good. I hope this is what you were looking for.

fox mulder’s picture

adriaav: Your solution helped me, thanks.

demonde’s picture

#27 solves the issue

1. Using "Content: Product variations (field_product:delta) = 0"
2. Any dependant field has to have the correct wrapper class like "node-[nid]-product-field-image" or "node-[nid]-product-commerce-price", depending on the field name, where nid is taken from a node:nid field.

I think this might be a bug, but maybe it is not possible to deal another way.

No 1 is the way drupal works, but there could be an additional relationship, that does imply the filter to one item.
No 2 is by making the ajax callback by the class. Since views let the user controll the classes, it might be difficult to change this, but the callback for the views field could run on the default views classes instead. Or is there a way to change the depending fields on another way?

In my point of views ;-) the actual solution is a workaround, not a proper solution.

mennonot’s picture

Solution #25 worked for me.

mennonot’s picture

Solution #25 worked for me to manage the display of my product variations in views so that only one image and items is displayed.

yalasta’s picture

solution #93 worked for me. thanks adriaav!

HansKuiters’s picture

#25 and #93 and all others with the same solution: thank you!

alex.87’s picture

Well #93 works until you start using filters, if you need filters ( like me ) than its not going to work because it includes only first variation of whatever is in that field.

For example i have 10 different color variations, and using this solution im limited only to one color so my facet filters are unusable.

Still looking for real solution on this.

bojanz’s picture

@Alexandar Davidovic
The real solution is to use Search API for your listings with facets.
The behavior you are seeing is how SQL itself works, there's nothing to solve.

alex.87’s picture

@bojanz

Im using Search API, and i have products that have Colors and Sizes variations. What i just need is to show all colors variations on my listing page, and when user clicks on Red shirt, he lands on product display page where red shirt is selected, but all other variations are there in dropdown.

But i couldn't find a way to do that with Search API.

IckZ’s picture

I run into the same issue like Alexander Davidvic. I have multiple variations products in size and color and I just want to show all products with different colors. The Size should be aggregated if

- one product with the same color but different size is already viewed.

Is there any working solution for this?

zenimagine’s picture

The solution number 93 worked for me, but I'm content types blog, products, ...
So if I apply this solution for the "Taxonomy term (Content)" then the contents of my blog are not displayed.

JamesRobertson’s picture

Wow. @Darren Shelly #27 you are a very clever person!

vlad.dancer’s picture

Hi everyone.
I want to share my aproach how to show product displays without duplicates.
In general I'm using method from #25 with "delta" views filter.

But when you have a product stock functionality or simply use product status to show/hide a particular product (like mentioned in #53),
then if product with delta 0 is out of stock or disabled, this implies that product display won't be displayed in a view too.

To avoid last issue I use such lifehack:
- implement hook_node_presave
- use early negative conditions to filter out node types that aren't product displays
- magic is here -> sort "field_product" items by status/stock using EFQ/db_select/db_query.

Sample code:

/**
 * Implements hook_node_presave().
 *
 * Sort products by status,
 * this workaround used in shop catalog,
 * because views can use only fixed (first) product from product display,
 * so product catalog does not include disabled first product,
 * if there is at least one enabled product in particular product display.
 */
function mymodule_node_presave($node) {
  if (!in_array($node->type, array('product_with_palette', 'product_display'))) {
    return FALSE;
  }

  if (count($node->field_product[LANGUAGE_NONE]) > 0) {
    $products_id = array_column($node->field_product[LANGUAGE_NONE], 'product_id');

    $query = db_select('commerce_product', 'p');
    $query->addField('p', 'product_id');
    $query->condition('product_id', $products_id, 'IN');
    $query->condition('status', 1);

    $products_in_stock = $query->execute()->fetchCol();

    usort($node->field_product[LANGUAGE_NONE], function($a, $b) use($products_in_stock) {
      $a_in_stock = in_array($a['product_id'], $products_in_stock);
      $b_in_stock = in_array($b['product_id'], $products_in_stock);

      if ($a_in_stock == $b_in_stock) {
        return 0;
      }

      return ($a_in_stock > $b_in_stock) ? -1 : 1;
    });
  }
}

So what's happened above:
- on node_save event, no matter via migrate module, rules module, or via ui mymodule_node_presave triggered and sorted products in product display by availability (from available to unavailable)
- than view always show product display, because product with delta 0 will be available now.

Disadvantages:
- you can't set exactly manual products order, only inside a group of products with same status.
- ?

What do you think?

designate’s picture

Finally got it fixed using the views distinct module with filter repeats set on nid. Performance wise no issues.

ljgra’s picture

My products have size range (Size Taxonomy: S, M, L, etc.). I wanted to display only 1 product info from size taxonomy. I headed out to taxonomy term view and under FORMAT > Show, chose Rendered entity. You may want to use Teaser of create/add your own custom DS view. This made it possible to make my product display show only 1 product info with size variations.

millionleaves’s picture

My products have multiple variations each with different prices. I got this working by combining #27 and #107. Thanks to both (and all others who have helped on this thread).

barnabas.nagy’s picture

Version: 7.x-1.x-dev » 7.x-1.14

I'm using the Views Distinct module to kill the duplications. The exposed views filters working fine with this, but pagination is wrong. The problem is that Views Distinct affects to the rendered result of the views module (and the SQL LIMIT runs before).
The result is that fewer products will be displayed on a page and if you using a grid output, this is quite visible. :(
In my case, the cause of duplication is just the different prices not the different colors os sizes...

Unfortunately, I still can not find the solution if I want to use exposed filters (delta = 0 unusuable), and correct paginations....
Has anyone handled this combination?

HansKuiters’s picture

Didn't handle this combination. If everything else works fine perhaps you could solve the pagination issue with Views Infinite Scroll

barnabas.nagy’s picture

This is indeed a good solution, if the SEO is not a priority for you. (in my case it is very important)

kevster’s picture

all these years later and #25 worked well for me - the problem only surfaced when I added sorts using BEF and had to add the relationship to referenced products, also added a hidden NID as first field...