Searching fails with pagination

[lukeyouell]

The following database exception is thrown when trying to paginate search results:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'simplemap'

[lukeyouell]

I've narrowed down what I think is the issue, JOIN is being repeated in the SQL query:

JOIN `simplemaps` `simplemap` ON (`elements`.`id` = `simplemap`.`ownerId`) AND (`elements_sites`.`siteId` = `simplemap`.`ownerSiteId`)
JOIN `simplemaps` `simplemap` ON (`elements`.`id` = `simplemap`.`ownerId`) AND (`elements_sites`.`siteId` = `simplemap`.`ownerSiteId`)

[brandonkelly]

This is the culprit:

simplemap/src/services/MapService.php

Lines 258 to 266 in e3cbea8

	$query->join(
	'JOIN',
	"{$tableName} simplemap",
	[
	'and',
	'[[elements.id]] = [[simplemap.ownerId]]',
	'[[elements_sites.siteId]] = [[simplemap.ownerSiteId]]',
	]
	);

A field’s modifyElementsQuery() method will be called each time an element query is executed, so doing this could result in multiple JOINs getting added to the query.

Element queries aren’t ever built and executed directly, though. They create an internal Query object, accessible via $query->query, which is what actually gets built into SQL and executed, and that gets recreated each time the element query is prepared for execution. So changing that first line to

$query->query->join(

should do the trick.

[lukeyouell]

I seem to have fixed the issue by replacing

$query
  ->subQuery
    ->andWhere($restrict)
    ->andWhere("$distanceSearch <= $radius");

with

$tableName = MapRecord::$tableName;

$query
  ->subQuery
    ->join(
          JOIN',
	  "{$tableName} simplemap",
	  [
	    'and',
	    '[[elements.id]] = [[simplemap.ownerId]]',
	    '[[elements_sites.siteId]] = [[simplemap.ownerSiteId]]',
	  ]
	)
	->andWhere($restrict)
	->andWhere("$distanceSearch <= $radius");

I'm not sure if this is the perfect fix, but it's at least got the pagination working for me until the next release.

[Tam]

If it helps, I'm getting a completely different error 😄

SQLSTATE[42803]: Grouping error: 7 ERROR: column "structureelements.lft" must appear in the GROUP BY clause or be used in an aggregate function
LINE 17: ORDER BY "structureelements"."lft", "elements"."dateCreated"...

while @brandonkelly's fix gives me

SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "simplemap"
LINE 8: ..."."siteId"=$2) AND ("content"."siteId"=$3) AND ((("simplemap...

and your fix, @lukeyouell, throws

SQLSTATE[42712]: Duplicate alias: 7 ERROR: table name "simplemap" specified more than once

Could you give me an example of your template code, and let me know what versions of Craft & SimpleMap you're running and what DB driver you're using?

[lukeyouell]

@Tam

Template code:

{% paginate craft.entries.section('propertyFeed').propertyLocation({
      location: filter.location,
      radius: filter.radius,
      unit: 'mi'
    })
    .propertyPrice(filter.budget)
    .propertyType(filter.type)
    .propertyBedrooms(filter.bedrooms)
    .propertyBathrooms(filter.bathrooms)
    .orderBy(filter.sort).limit(6) as page, properties
%}

Craft 3 RC-5
SimpleMap 3.1.3
DB Driver MySQL 5.7.20

[Tam]

Hmm... I have a fix, but it only seems to work with MySQL. Postgres keeps throwing this error (fresh install, RC5):

SQLSTATE[42803]: Grouping error: 7 ERROR: column "structureelements.lft" must appear in the GROUP BY clause or be used in an aggregate function
LINE 17: ORDER BY "structureelements"."lft", "elements"."dateCreated"...

The fix is basically what Brandon suggested, but extended to the subQuery as well (a tab is 4 spaces GitHub 😠 ):
Replace

simplemap/src/services/MapService.php

Lines 258 to 266 in e3cbea8

	$query->join(
	'JOIN',
	"{$tableName} simplemap",
	[
	'and',
	'[[elements.id]] = [[simplemap.ownerId]]',
	'[[elements_sites.siteId]] = [[simplemap.ownerSiteId]]',
	]
	);

with

$on = [
	'and',
	'[[elements.id]] = [[simplemap.ownerId]]',
	'[[elements_sites.siteId]] = [[simplemap.ownerSiteId]]',
];

$query->query->join(
	'JOIN',
	"{$tableName} simplemap",
	$on
);

$query->subQuery->join(
	'JOIN',
	"{$tableName} simplemap",
	$on
);

@brandonkelly Can you shed any light on the error above?

[lukeyouell]

@Tam that works for me!

[rensverschuren]

Issue doesn’t seem to be resolved. Could anyone point to a solution? Many thanks!

Database used: PostgreSQL 11
Craft: 3.1.19
Simple Map: 3.4.7

Template code:

{% set foundJobs = foundJobs.location({
  location: postalCodeQuery,
  country: 'NL',
  radius: 10
}).orderBy('distance') %}

{% paginate foundJobs.limit(10) as pageInfo, pageEntries %}