New Relationship Field Giving: "Syntax error/access violation: 1066 Not unique table"

[Zosh]

Hello Espo Community!

Just this week I created a fresh LAMP (Ubuntu Server v20.04, Apache v2.4.41, MySQL v8.0.25, PHP v7.4.3) server to run a testing/prototyping EspoCRM instance on. This is in an effort to get my company off of the absolutely atrocious Zoho CRM that we are massively dissatisfied with.

I've just been adding fields and creating some relationships to mimic how our current CRM system looks and functions, no extensions or anything loaded up. I seem to have broken something in how the client interprets Relationships between data!!

Being a furniture manufacturer, we frequently work with company executives. It helps to know who a given Contact's assistant is, or who another given Contact reports directly to. So, this sounds like a One-to-One relationship right? All I need is a field on the Contact page that I can lookup another Contact with. I decided to try this with a One-to-One Right relationship. However, when I implemented this I went to a Contact record named Wig-Wom Willy and tried set another Contact named Tom Twinkie as Willy's Assistant to test it, and boom, the 500 errors starting flowing in. Inputting data into this new Assistant 1t1R field seems to have broken something where the client GUI constantly throws 500 errors now! All my screenshots attached with annotations...

I did some investigating, checking out the EspoCRM log and discovering a constant "Syntax error or access violation: 1066 Not unique table/alias: 'contact'" on the test Contact, Wig-Wom Willy. From the log I see this error seems to be thrown from Line 72 of the SqlExecutor.php file, all included in my attachments.

Any idea why this is occurring? I presume I probably used the One-to-One entity incorrectly, however the Admin Doc doesn't really extrapolate this too much, but maybe that is just me. Really appreciate anyone's help!

Thanks,
Josh

[shalmaxb]

Hi, look at your relationship. You named it on the right side "contact", which is already occupied by the table contact (from the contact entity). To cure that you have o delete that relationship (sometimes even necessary to have a look into the database, if there was created another table named "contact", what I don not think, will be the case).
Then you create the same relationship again and give that right part (which in fact will try to create a table with that name, what will be impossible, if the name already exists) another name. This could be e.g. contactAssistant. The label you can name as you like and change later, the name you cannot change later. If you ever want to change a name, you have to delete and recreate the relationship.
So, think very thoroughly concerning the names, because with many relationships and wrong decision it could become quite a nightmare. Later it maybe very difficult to remember, what this relationship does. It is recommended also to document the customization with explanations. Lot of work, but it pays off later.

And one word for reading the error log. Normally the first line explains the cause, the reference to files like SqlExecutor.php only tells, where it is programmed to - in this case - avoid trying to give a name, that already exists. But you would never change anything in these mentioned files.

[esforim]

Don't know how you manage to create a relationship using two entity with same name, shal is absolutely right with the relationship to be different field/table name.

Personally I prefer to use Many-to-Many because many contact can have many assistants that change over time or more than one employee. With One-to-One you get an annoying issue of "one".

Personally I do something similar to what you doing, which is Contact-2many-ContactReferred which I link each Contact to a Contact that refer a client to us.

I suppose just create a new relationships from GUI admin and use that, not sure how to clean up your 500 Error issue though

[yuri]

I will fix that it's not allowed to create a link name that matches an entity name.

[Zosh]

shalmaxb I see, so essentially on a one-to-one right, the right section is essentially creating a new table to store the data being related? Thanks for the advice, I definitely want to try and be as specific as possible when naming and relating my data, and to this end I think we're making a good move in standing up a prototype of this system first before we go ahead and do the real thing in production.

esforim Maybe I'm just not experienced with this but it's difficult to know which side is doing what on the Relationship panel, nothing informs me "left-side is for this, right-side is for this", so does the same follow suite whenever I need to create a One-to-One Left relationship? Is this all basically the same as SQL joins, - where left joins bring in rows from left table but matched results from the right table, and same with SQL right joins bringing rows from right table but matched results from the left table?

yuri Leave it to me to break your application! I do see why this breaks now and is makes sense why it did.

Thanks everyone for your help, big time appreciate it!

*EDIT*

I fixed the 500s by simply deleting the contactAssistant relationship.

[yuri]

> I see, so essentially on a one-to-one right, the right section is essentially creating a new table to store the data being related? Thanks for the advice

It does not. When building SQL query the ORM assigns aliases. In your case: 'contact' alias for 'contact' table, and 'contact' alias for 'contact' relationship name. To prevent this from happening, the relation name (link name) should be different from the entity name.

[shalmaxb]

Only to understand that: Why the error log throws a

1066 Not unique table/alias

if there is no table with existing name created?

[Zosh]

Thanks for everyone's input on this. I did just have to clearly define both ends of the relationship and it works as intended just fine. Kinda seems silly in retrospective what created that issue, but oh well!

[esforim]

All that matter is issue resolve and rest easy till you/we break it again