matthewknill's avatar

Getting syntax error with paginate and table join

I have a scope for my Contact model that takes an array of filters. A contact belongs to an organisation and the scope works fine except when I use it with pagination. For brevity purposes, I won't show the whole scope but the part that breaks the pagination is the following (which is essentially used to filter to contacts that are a part of an organisation with given tags):

->when($filters['organisationTags'] ?? null, function (Builder $query, $orgTags) {
            $query->join('organisations', 'contacts.organisation_id', '=', 'organisations.id');
            $query->where(function ($query) use ($orgTags) {
                for ($i = 0; $i < count($orgTags); $i++) {
                    if ($i === 0) {
                        $query->whereJsonContains('organisations.tags', $orgTags[$i]);
                    } else {
                        $query->orWhereJsonContains('organisations.tags', $orgTags[$i]);
                    }
                }
            });
        })

And I'm accessing the scope like this:

return Contact::filter($filters)->orderBy('first_name')
            ->filter([
                'search' => $filters[$filterKeyPrefix.'search'] ?? null,
                'trashed' => $filters[$filterKeyPrefix.'trashed'] ?? null,
                'tags' => $filters[$filterKeyPrefix.'tags'] ?? null,
                'organisation' => $filters[$filterKeyPrefix.'organisation'] ?? null,
                'organisationTags' => $filters[$filterKeyPrefix.'organisationTags'] ?? null,
            ])->paginate($paginate, ['contacts.*'], $filterKeyPrefix.'page')
            ->withQueryString()
            ->through(fn ($contact) => [
                // Fields
            ]);

Upon running this with something in the organisationTags filter, I get the following error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'organisations'

SELECT
  count(*) AS aggregate
FROM
  `contacts`
  INNER JOIN `organisations` ON `contacts`.`organisation_id` = `organisations`.`id`
  INNER JOIN `organisations` ON `contacts`.`organisation_id` = `organisations`.`id`
WHERE
  (json_contains(`organisations`.`tags`, "tag1"))
  AND (json_contains(`organisations`.`tags`, "tag1"))
  AND `contacts`.`deleted_at` IS NULL
  AND `contacts`.`account_id` = 1

I know this is something to do with the table join but I have no idea why it's doing two inner joins and not one in the above statement (please note again that scope works without pagination).

0 likes
3 replies
Sinnbeck's avatar

I only see one join but your query has two?

Anyways. You can alias it to avoid naming conflicts

->when($filters['organisationTags'] ?? null, function (Builder $query, $orgTags) {
            $query->join('organisations as org', 'contacts.organisation_id', '=', 'org.id');
            $query->where(function ($query) use ($orgTags) {
                for ($i = 0; $i < count($orgTags); $i++) {
                    if ($i === 0) {
                        $query->whereJsonContains('org.tags', $orgTags[$i]);
                    } else {
                        $query->orWhereJsonContains('org.tags', $orgTags[$i]);
                    }
                }
            });
        })
matthewknill's avatar

@Sinnbeck as mentioned, the scope works absolutely fine without the pagination. I can also confirm that I am only doing one join in my entire query.

matthewknill's avatar
matthewknill
OP
Best Answer
Level 1

Figured it out... I was for some reason calling the filter method twice, which caused multiple table joins.

Please or to participate in this conversation.