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).