Need help with corrupt joins table

[Bauer]

My #__fabrik_joins table is all messed up again.

I have been sporadically getting some weird errors that mention joins on tables or elements that I deleted months ago.

I suspect it has to do with records that have zero as the element_id or group_id ? maybe it is trying to join the zero ids? And I realize there has been a lit of Github 'fixes' applied to my Fabrik component - some of which may have trashed some of the data without later correcting it. And there is always the possibility that I might have altered or deleted a table or two via phpAdmin instead of Fabrik.

I suggested long ago that some ?maintenance? script should be developed to verify the relationships in the Fabrik tables ? especially the joins and elements tables as they seem to be the 2 tables most easily trashed.

This morning I wrote a quick script that loops through the #__fabrik_joins table and queries each of the the related tables on the id specified in the joins table and reports if the id is not found.

What I need to know is what the rules would be ? that I can go by - to delete the invalid join records in the #__fabrik_joins table?

Here?s what I think is safe to delete - i.e. any #__fabrik_joins records where ?

1. there is an invalid ?element_id? - either 0 (zero) or not found in the elements table
2. there is an invalid ?group_id? - either 0 (zero) or not found in the groups table
3. there is an invalid ?list_id? ? an ID not found in the lists table
4. a ?join_from_table? table name where the table does not exist
5. a ?table_join? table name where the table does not exist

Out of 125 records in my #__fabrik_joins table , 96 come up as breaking these rules. Almost all of them break at least 3 of the 5 rules.

 

[troester]

I assume deleting joins manually will make it worse.

I recently had an error concerning a dbjoin element - the reason was that it was linked to a parent element which has been deleted: editing, unlinking, saving the element solved this issue.

To your list:
element id may be 0 (table joins)
list id may be 0 (+ join_from_table empty) (dbjoin/user elements)
I "think" group id 0 is ok in case of ajax fileupload, multiselect dbjoins etc.

 

[Bauer]

Thanks for responding troester. Ok let's dive into fixing my rules then. I knew list_id could be 0 or empty - that's why I only checked that the list_id (if not blank) existed in the list table.

I already deleted the join records that I thought were bad - I'll let you know if it caused any problems. But I still have a copy of the list my script created to cross-reference. I attached it if you want to take a look. (Each check/result is separated by a hyphen.)

So changes to my rules should be?...

1. there is an invalid ?element_id? - either 0 (zero) or not found in the elements table AND "table_join" is empty.

and maybe #2 should be just like the list_id? - only verify that the group_id is valid if there is a group_id and it is > 0?

 

[Paulina88]

Thanks iy was really good