Paginating Extended Query

[BrandonJamesBorders]

Description

Is there a trick to be able to paginate extended queries? I get this error when trying to do so:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'purchase_counts'
The SQL being executed was: SELECT elements.id, elements.fieldLayoutId, elements.uid, elements.enabled, elements.archived, elements.dateCreated, elements.dateUpdated, elements_sites.slug, elements_sites.siteId, elements_sites.uri,

Steps to reproduce

1. {% set productQuery = craft.purchasePatterns.extended().orderBy('qtyCount DESC').relatedTo(category).limit(24) %}
2. {% paginate productQuery as pageInfo, productEntries %}

Additional info

• Craft version: 3.4.23
• Plugin version: 1.1.5
• PHP version: 7.3.16
• Database driver & version: MySQL 5.7.29

[mark-adigital]

Realise this is an old thread, and I'm not sure pagination was the cause of the issue for us, but we did encounter this and fixed it by adding a conditional to purchase-patterns/src/elements/db/ProductQueryExtended.php

if ($this->join == null) {
	$this->leftJoin(
		'{{%purchase_counts}} purchase_counts',
		'[[commerce_products.id]] = [[purchase_counts.product_id]]'
	);
}

This prevented the same JOIN being performed twice and seems to have resolved the problem in our use case.