Database join element conflict

[keianrad]

I have a form with many to many relationship. In the list view there is a conflict between two of database join elements, One of related data shows wrong number.
I found the database join element that has conflict, It doesn't have any problem. I delete it and made a new one but the problem is still available.

These two database joins are joined to two different tables! I don't know exactly what is the problem, everything is correct.


If you need to check it:
Site: SSA
List: Clients
Related Data: Contacts

 

[cheesegrits]

That's a difficult one to figure out remotely, I'll have to see if I can replicate the problem here. If not, we'd have to arrange to get an Akeeba copy of your site.

-- hugh

 

[keianrad]

That's a difficult one to figure out remotely, I'll have to see if I can replicate the problem here. If not, we'd have to arrange to get an Akeeba copy of your site.

-- hugh

Should I install Akeeba?

 

[cheesegrits]

Yes - I'll need to install a copy of your site here to do the project management work anyway.

So install it, and do a full backup.

-- hugh

 

[cheesegrits]

I have a local copy of your site now, I'll take a look at this.

-- hugh

 

[keianrad]

Thanks.

 

[keianrad]

Did you have time to check it?

 

[cheesegrits]

Nope, I was too busy doing the other work for you. I'll look this evening.

Sent from my HTC6545LVW using Tapatalk

 

[keianrad]

It is still available. There is a cascading element named adviser in the contact list that it is caused to show duplicate record. it is only one in the DB. I changed the element to database join type but it is still available.
Only when I unpublished the element it solved.

 

[cheesegrits]

I don't see duplicates in the Contacts list on my copy of the site.

And there's no Adviser element, and no "Office" field that it uses as the Watch element.

So I guess I'll need to take another Akeeba copy of your site and re-install it here.

-- hugh

 

[keianrad]

Would you please check the issue on the contact form? It is still available.

 

[cheesegrits]

OK, the problem is because you are using user_id as the value of that adviser_id join (CDD). And that isn't unique. There are (for example) multiple instances of user ID 800.

So when the row selection for app_contacts does this ...

SELECT ... app_contacts.adviser_id AS app_contacts___adviser_id ... FROM app_contacts LEFT JOIN app_staff ON app_staff.user_id = app_contacts.adviser_id ...

... when app_contacts.adviser_id is 800 ... that selects multiple rows, because user_id isn't a unique value in app_staff.

The 'value' of a join element MUST be unique in the joined table. It must uniquely identify the selected row.

If you recall, in one of our Skype sessions I was trying to explain this, when we were having a long discussion about the fact that you are using the user_id rather than the 'id'. I understand that you want to be able to filter on user id's, but this approach isn't going to work.

-- hugh