[Resolved] SQL GROUP BY not working & Pagination Broken

[Author]

Posts

[zacharyL]

NOTE: This is directly related to my previous ticket, but I couldn't re-open it: https://toolset.com/forums/topic/wpv_filter_query-wpv_filter_query_post_process-not-firing/

I am trying to: Use an SQL GROUP BY clause in conjunction with a Query Var to filter out Products with Duplicate Style IDs (Meta Key: wpcf-bi__style)

Link to a page where the issue can be seen: hidden link

I expected to see: Only 1 Red Bead and 1 Turquoise Bead

Instead, I got: 2 of each Bead Color

I also noticed another related issue that is a bit unsettling. Here's some details for both issues:

Issue #1 - Loop filter still not working

1. The code running my desired filters is located at

plugins/bi-product-imports/plugin.php

in both installations - I've added code comments to explain what I'm doing

2. You can see it's not working here on the Toolset-Powered site: hidden link (showing 9 of 12 results, should be showing 9 of 10) - Notice there are 2 Red Beads and 2 Turquoise Beads. There should only be one of each because they have the same Style ID (Meta Key: wpcf-bi__style)

3. I've created a "Duplicator Package" for the working "vanilla" installation: hidden link

4. Disregard the previously provided "Duplicator Package" for the Toolset-Powered site. I have created a new one. Please just download it from the interface as the file is a but large: hidden link

Issue #2 - WPV Loop pagination showing duplicate results, even with my filter disabled

1. You will see 2 Red Beads and 2 Turquoise Beads on the first page here: hidden link

2. When clicking on Page 2, you see the same 2 Turquoise Beads again

Other Notes

1. I've updated all the relevant plugins and WordPress

2. I've attempted to clear caches and transients via WP-CLI and WooCommerce's UI

NOTE: Site access & Duplicator

Please use the credentials provided in the previous ticket (linked above) to access everything.

[Nigel Supporter Languages: English (English ) Spanish (Español ) Timezone: Europe/London (GMT+01:00)]

Hi Zachary

I can't comment on the specifics of your code, I'm afraid, support for custom code falls outside our support policy: https://toolset.com/toolset-support-policy/

I visited the site at hidden link.

The page shows 9 of 12 products, and when I pass to the second page it shows the remaining 3 of 12 (and does not repeat any of the products from the first page). There doesn't appear to be an issue with the pagination that I can see.

From your question I understand that some products are duplicates of others and you want to exclude them from the product archive, is that right?

Can you clarify how you indicate which products are duplicates of each other? I may be able to help with a solution.

[zacharyL]

Oddly enough, the products that are showing duplicate have changed, but the issue is still there. Here are some screenshots of what I'm seeing:

Page 1: hidden link
Page 2: hidden link

Regarding what I'm trying to do, there is a Meta Key (aka. Custom Field) on each product called "BI Style". The Meta Key itself is "wpcf-bi__style" and you can see it set here: hidden link (Screenshot: hidden link). There are multiple products with the same "BI Style", and I want to show only one of each. This is usually accomplished by a simple SLQ GROUP BY clause as I tried above, which works fine on vanilla WordPress/WooCommerce, but not with Toolset.

Background: My client sells jewelry and they use a "Unique SKU System", so every product has a different SKU, even 2 of the same product (ie. Red Pandora Bead). If my client has 10 Red Pandora Beads in stock, we don't want all 10 of them showing up separately in the store, so I'm trying to filter the Products Loop to only show one at a time.

I'm fine with doing this in a hook like "the_posts" and filtering using PHP array functions, etc, but I need to make sure the Pagination still works, etc. WP Query doesn't have a "GROUP BY" Query Var for me to set as per your original suggestion in the previous ticket, so I resorted to using the "posts_groupby" hook.

I have to find a way to make this work, and we're almost done with the site, and it works fine outside of Toolset, so if you guys can help me find a solution, I'd hugely appreciate it.

[Nigel Supporter Languages: English (English ) Spanish (Español ) Timezone: Europe/London (GMT+01:00)]

Hi Zachary

Firstly, sorry for the delay getting back to you, yesterday was a public holiday here.

Thanks for the clarification of what you are aiming to achieve.

I didn't set up a duplicate of your own site to look at your custom implementation, but I tried to achieve something similar on a vanilla WooCommerce install with the demo product data that ships with the plugin.

I didn't create any new custom fields, I worked with the existing meta, and chose the price as a way of excluding "duplicates" in the product archive, i.e. only one product would be displayed for any given price.

Without Toolset I achieved that with the following code:

add_action( 'pre_get_posts', 'customise_product_archive' );
function customise_product_archive( $query ) {

	if ( $query->is_main_query() && !is_admin() && is_post_type_archive( 'product' ) ) {

		$query->set('meta_key', '_price');
	}
}

add_filter( 'posts_groupby', 'custom_product_archive_groupby' );
function custom_product_archive_groupby( $groupby ){

	if ( is_main_query() && !is_admin() && is_post_type_archive( 'product' ) ) {
		global $wpdb;

		$groupby = $wpdb->postmeta . '.meta_value';
	}

	return $groupby;
}

The first part was necessary because the query needs to include a custom field if you are to group by a custom field.

I then activated Types, Views, and WooCommerce Views and created a custom product archive.

I didn't need to make any changes to the above code, when I viewed the shop page it simply eliminated all price "duplicates".

This is the resulting SQL query:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts 
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1 
AND ( wp_posts.ID NOT IN ( 
SELECT object_id 
FROM wp_term_relationships 
WHERE term_taxonomy_id IN (12) ) 
AND wp_posts.ID NOT IN ( 
SELECT object_id 
FROM wp_term_relationships 
WHERE term_taxonomy_id IN (12) ) )
AND ( wp_postmeta.meta_key = '_price' )
AND wp_posts.post_type = 'product'
AND ((wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private'))
GROUP BY wp_postmeta.meta_value
ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC
LIMIT 0, 10

I think you should be able to modify the above to achieve something similar with the wpcf-bi__style post meta.

Just as a reminder, we can't support custom code; I found the above to work in my limited testing, but you will have to adapt it to your needs and test accordingly.

[zacharyL]

Issue #1 (NOT RESOLVED) - Loop filter still not working

The code you sent over is exactly what I'm doing already. There are only a couple subtle differences:

1. I'm using the 'woocommerce_product_query' hook instead of 'pre_get_posts' because 'pre_get_posts' doesn't seem to be working for me in either scenario, even with a vanilla WP install and a simple Toolset Archive Layout. 'pre_get_posts' results in none of the duplicate products showing up rather than only one.

2. I was making an extra check using a global variable (although I'm not thrilled about it) because the product archive loops aren't behaving the way I expect on my site right now with Toolset/Divi/WooCommerce. This however isn't necessary for the simple Vanilla WordPress test case both of us set up.

Question: If you change 'pre_get_posts' to 'woocommerce_product_query', does it work on your end?

Either way, it doesn't work at all on breakiron.almost.online.

Issue #2 (RESOLVED) - WPV Loop pagination showing duplicate results, even with my filter disabled

This was resolved by simply picking a sort value other than "Menu Order".

Issue #3 (NEW ISSUE) - Extra WooCommerce Loop and Pagination Showing (Likely related to Issue #1)

We're getting extra WooCommerce Loop Output up top and pagination in the footer (Link: hidden link - Screenshots: hidden link & hidden link).

Due to Toolset's Divi Integration Issues as of late, we had to disable to Divi Integration Plugin. Re-enabling it completely trashes our site, so we built the site without it. I'm wondering if this is causing it, but I'm not sure what to do since the site seems to explode if we re-enable it. Thoughts on this? Is the fix out yet?

NOTICE: Here's the kicker; With my SQL GROUP BY filter enabled, the extra Loop Output up top is CORRECT, but the Views Loop Output IS NOT CORRECT. See here: hidden link

This proves that my filter is working, but I cannot for the life of me figure out why this extra loop output is showing up OR why I can't get my filter to operate on the correct query. I've tried everything I can think of and the best I've gotten was either how it is currently, OR both loops completely not working.

I appreciate your help, please advise!

[Nigel Supporter Languages: English (English ) Spanish (Español ) Timezone: Europe/London (GMT+01:00)]

Hi Zachary

I tried switching out the pre_get_posts hook for the woocommerce_product_query hook and got the same results, it worked as expected both with WooCommerce-only and with Toolset + WooCommerce.

Checking the WC source code, the woocommerce_product_query is triggered by the pre_get_posts action, so that is perhaps not surprising.

What is surprising is that it doesn't work for you using the pre_get_posts hook, and that is something of a red flag and I would direct my efforts to getting it to work there. pre_get_posts runs on all post queries and must be being triggered if woocommerce_product_query is being triggered.

When you say "the product archive loops aren't behaving the way I expect on my site right now", again, that is another flag that you have some underlying issue which prevents the simple test case I used translating to your site.

For testing I suggest you switch to twentyseventeen so that you can rule out any issues coming from Divi.

We are in something of a transition with our integration plugins, shifting to maintaining them but discouraging clients from using them. Without the integration plugin Layouts will only control the content area of the page and leave generation of the header, footer, and sidebars to the theme. If you are not using the integration plugin currently, I recommend you continue without it.

If you switch theme, do you still get the additional pagination links in the header and footer? It's not clear to me looking at the linked page what is generating them.

There is something odd about your page markup.

You have body > div#page-container > header#main-header and this header contains the site logo and navigation but can be deleted without effect, they are duplicated inside the next div.

[zacharyL]

HOLY COW. I simply enabled Twenty Seventeen and re-enabled Divi and it seems all our problems went away. Something must have been cached somewhere. I'm always hesitant to disable themes as I've had weird irreversible side effects with poorly developed themes doing so, but it seems everything is still in order at first glance.

Thank you very much for your help! I will let you know if I have more issues.