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

[asellami]

After migrating espo to latest Version 8.0.6, I encountered this error (ERROR: Process 659e67c05a4f6cb76 formula error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias) while running this formula:
$idContactPR2f68a2=record\findRelatedMany('Partena riatRD',
id,
'leads',10,'createdAt','desc',
'reportFilter61407c2b7dc93b179');

Before the migration, everything is going well.

How can I fix this please ?

[yuri]

Hi,

As the post doesn't conform the bug report rules, I moving it to another category.

[item]

Hi,
as you use Report (Advanced Pack) , what version is ?
maybe

[yuri]

Knowing the Advanced Pack version may be helpful.

[asellami]

Hi,
as you use Report (Advanced Pack) , what version is ?
maybe

Yes, i use the version 3.0.14 of Advanced Pack

[asellami]

Knowing the Advanced Pack version may be helpful.

version 3.0.14

[mkhalifa]

Good morning,
I add some detail in relation to the feedback made by my colleague asellami,
the formula is used in a node of a flowchart:
and when I add a log to display the sql query that is causing the problem I have this query :



SELECT DISTINCT lead.id AS `id`, lead.created_at AS `createdAt` FROM `lead` AS `lead` JOIN `lead_partenariat` AS `partenariatsMiddle` ON lead.id = partenariatsMiddle.lead_id AND partenariatsMiddle.deleted = 0 JOIN `partenariat` AS `partenariats` ON partenariats.id = partenariatsMiddle.partenariat_id AND partenariats.deleted = 0 LEFT JOIN `historique_activite_p_r` AS `historiqueActivitePRs` ON lead.id = historiqueActivitePRs.lead_id AND historiqueActivitePRs.deleted = 0 LEFT JOIN `lead_partenariat` AS `partenariatsMiddle` ON lead.id = partenariatsMiddle.lead_id AND partenariatsMiddle.deleted = 0 LEFT JOIN `partenariat` AS `partenariats` ON partenariats.id = partenariatsMiddle.partenariat_id AND partenariats.deleted = 0 LEFT JOIN `partenariat_r_d_lead` AS `partenariatRDsMiddle` ON lead.id = partenariatRDsMiddle.lead_id AND partenariatRDsMiddle.deleted = 0 LEFT JOIN `partenariat_r_d` AS `partenariatRDs` ON partenariatRDs.id = partenariatRDsMiddle.partenariat_r_d_id AND partenariatRDs.deleted = 0 LEFT JOIN `entity_email_address` AS `emailAddressesMultipleMiddle` ON lead.id = emailAddressesMultipleMiddle.entity_id AND emailAddressesMultipleMiddle.deleted = 0 AND emailAddressesMultipleMiddle.entity_type = 'Lead' LEFT JOIN `email_address` AS `emailAddressesMultiple` ON emailAddressesMultiple.id = emailAddressesMultipleMiddle.email_address_id AND emailAddressesMultiple.deleted = 0 WHERE partenariats.id = '633c10b4c1ceb0c23' AND (((historiqueActivitePRs.name LIKE '2023%') OR (historiqueActivitePRs.name LIKE '2024%'))) AND (((historiqueActivitePRs.name LIKE '%partenariat%') OR (historiqueActivitePRs.name LIKE 'opfs%'))) AND (((partenariats.name = '2023') OR (partenariats.name = '2024') OR (partenariatRDs.name = '2023') OR (partenariatRDs.name = '2024'))) AND ((((emailAddressesMultiple.lower <> '')) AND ((emailAddressesMultiple.lower IS NOT NULL)))) AND lead.deleted = 0 ORDER BY lead.created_at DESC LIMIT 0, 10 ;

ERROR 1066 (42000): Not unique table/alias: 'partenariatsMiddle'

in which we see very clearly that there are two times the alias partnershipsMiddle.

[yuri]

Hi,

This fix should help: https://github.com/espocrm/espocrm/commit/dcdc94365ee563f6078ac82b99aade60fe7cac1b#diff-73b56f1f6059e8b18a21445c693752decef218dcfdacc3eded d5a75b5fc56d6f

[asellami]

Hi,

This fix should help: https://github.com/espocrm/espocrm/commit/dcdc94365ee563f6078ac82b99aade60fe7cac1b#diff-73b56f1f6059e8b18a21445c693752decef218dcfdacc3eded d5a75b5fc56d6f

Thank you Yuri, I'm going to test this !