Can't delete database join

[Bauer]

There are 2 long-standing bugs in the database join element that I sure wish someone knew how to fix.

1. You cannot edit an existing databasejoin element to change it from a standard dropdown to multiselect dropdown. (And probably the other way around too). Well you 'can' - but it then causes all sorts of issues and bugs.

2. Then if you try to delete that database join element to 'start over again' (because you made the 'mistake' of thinking #1 would actually work) you will get an error like this when it comes to removing the existing table as part of "Empty trash"....
An error has occurred.

1051 Unknown table 'fb_reports_repeat_param_hospital_type' SQL=DROP TABLE `fb_reports_repeat_param_hospital_type`​

..........​

​

The database join element I'm trying to delete is in a repeat group. The full name is "fb_breakouts___param_hospital_type".​

​

Yet this error shows that the code is trying to delete the associated 'repeat' table BUT using the parent table name (fb_reports) prefix.​

​

I'm pretty sure this bug was introduced just recently because at one time I remember posting that those 'repeat' tables were not being deleted when you deleted the element. So it looks like the intent to fix that issue was addressed - just not so well.​

 

[Bauer]

Just to followup on this.
I was able to completely delete the elements by ignoring the warning - and deleting the elements from the tables via phpMyAdmin.

Then I went to look at the elements in the actual group (not the joined group) and the elements I had trashed via the joined group list still showed in the non-joined group list. The 'trashed' elements I did were done from the repeat group list - like I have always been doing - assuming if you edit an element to the repeat group it gets reflected in the actual group for that table. And if you added a new element it got added to the actual group for that joined table. And that's the same way it is supposed to work when you delete any 'linked' element from the joined group, NO???

Anyhow - after removing all traces of those 2 elements from the list - and the from the table (via phpMyAdmin) - I added the same database join elements again in fabrik (via the repeat group element's list).

I attached a screen print from phpMyAdmin.

See how the 'repeat' table was created using 'fb_reports' rather than 'fb_breakouts' as the prefix?
There is no 'hospital_ownership' or 'hospital_types' element in the fb_reports table at all! And if there was - this bug would surely play havoc with anything using them.

The weird thing is the form seems to work. But there are over a dozen other database join elements that were created in the fb_breakouts table (over a year ago) and all of the associated 'repeat' tables are prefixed with 'fb_breakouts' (the name of the table that contains those 'repeat' elements - as I assume it should be).

So now I'm afraid to do anything in my project until this bug is fixed.

 

[felixkat]

A standard dropdown, or maybe better to say, anything other than multiselect will store the values in the parent table. A multiselect creates an additional table and sets up a one to many relationship, i.e a join.

So whilst theoretically you could switch between the two the data is not replicated. So if you enter 10 records as a dropdown and then switch to a multiselect you will not be able to access those records. The same would apply the other way around as well. However if you switch back you will see the old data again.

Fabrik never deletes old tables or columns, so if you switch to a multiselect the old column will still remain in the master table. We can never predict what people may want to do with that data. I think the only time we remove anything is when you physically click 'drop table' when emptying the trash. So there are times when you may have to do a manual cleanup as over time this can start causing confusion if you start spotting things you thought you had deleted months ago. I can't remember if this is documented anywhere but I do have some notes about the subject which I will publish at some point.

Whilst I don't think these are necessarily bugs, I do agree it can be confusing at times. However some of these situations are not necessarily the norm and so they are not always factored during the coding process. That is to say the code says do this for a dropdown or do that for a multiselect, there isn't necessarily an implementation that says and do this if you are changing from single dropdown to multiselect in a repeat group.

Whilst arguably this comes down to doing it right first time round because of careful planning.... there are of course when things change, usually because of a client request.

When it comes to repeated groups and \ or copy of lists it starts becoming a little more complex.

One thing to note, if you are physically removing data via phpmyadmin you have to make sure you are deleting everything from the right places otherwise you will get errors. With you above example you will have entries in many of the Fabrik tables.

#___fabrik_elements - Will be storing the element info
#___fabrik_forms - Form info
#___fabrik_groups - Group info
#___fabrik_formgroup - Form Group relationship
#___fabrik_joins - Containing join information of all sorts


Fabrik doesn't expect us to be poking around via phpmyadmin, although I do accept we have to do this at times. So if you physically delete something and haven't deleted all references to that something, some of the code treats it if it's still there. This is where errors start occuring.

If you are currently in a unusable state that it is a matter of going through the Fabrik tables to see what's missing or not applied. Just remember as above that references can be across many different Fabrik tables.

 

[Bauer]

Thanks for the reply felixcat - but this doesn't address the issue - and just exactly what's going on here.

Maybe it would be easier to explain if I asked you to do this...
Assuming you have a list somewhere that has been set with a joined (repeatable) group -
Create a new database join element in the joined repeat group (being sure to add the new element via the element list that is shown for the joined group)

Save the change and go into phpMyAdmin and see the name given to the '_repeat_' table for that databasejoin element that was just added.
Does that new '_repeat_' table name use the prefix from the repeat group table name (as it should) that it was created in - or does it use the parent table name?
For me it is now using the parent table name. This is wrong - and that's why I got that error when I went to delete the element.

The naming convection for those 'repeat' tables has always been...
tableNameThisElementIsIn_repeat_elementName

 

[felixkat]

Is this for the multi-select database join element?

 

[Bauer]

Is this for the multi-select database join element?

yes

 

[felixkat]

Okay I'll give it a whirl.

 

[Bauer]

Okay I'll give it a whirl.

Thank You.
The reason I got that error initially (before I went through all the trouble of 'starting over') is because the code was trying to
DROP TABLE `fb_reports_repeat_param_hospital_type`
when there was no `fb_reports_repeat_param_hospital_type`

The original database join I deleted was configured correctly - i.e. the associated '_repeat_' table name was `fb_breakouts_repeat_param_hospital_type` - AS IT SHOULD BE.

As I stated - that database join element named "param_hospital_type" is in the fb_breakouts table - not the fb_reports table.

 

[Bauer]

I'm just thinking.
Probably the fastest way to 'eliminate' the issues with changing the 'Render as' option of a database join element (which is only part of the problem I'm having now) - would be to just hide that 'Render as' option altogether as an editing option.

Would it be much trouble to have that option show ONLY when adding a new element - at least until someone can fix it to work correctly if an edit is made. (This isn't the first time I've made the 'mistake' of editing that option and messing up my tables - and all I really remember is that it was a nightmare to fix.)

 

[felixkat]

For the comments in the fourth post.

I created a List with the physically table name of z_bauer_a. The 'z_' is just my naming convention so I can blitz everything starting with 'z_' when I do a cleanup.

z_bauer_a has a group name of 'Bauer A'. I created a repeat group called 'Bauer B'. Within 'Bauer B' I created a databasejoin element as a multiselect. My phpmyadmin details are.

z_bauer_a <--- Main Table
z_bauer_a_22_repeat <--- Group Repeat, i.e 'Bauer B'
z_bauer_a_repeat_things <--- Databasejoin Multiselect.

They all start with z_bauer_a as technically they are all a part of the main table.

I then took a copy of z_bauer_a and called it z_bauer_y.
I also took a copy of z_bauer_a_22_repeat and called it z_bauer_z

I created the same join between the tables as they are a repeat group in the first setup. I then created the multiselect databasejoin.

From phpmyadmin

z_bauer_y
z_bauer_z
z_bauer_z_repeat_things

This so far is what I expected to see, although the multi-select doesn't actually work in the second example. I'm not sure if we can do this yet though as doing it this way you are joining to a table that is already joined. I thought Jaanus had changed something to allow us to do this but I need to check with him.

I've created a backup so I don't know if you want me to try and delete anything?

 

[Bauer]

Your first example is enough to prove my point.
And it is incorrect to say "They all start with z_bauer_a as technically they are all a part of the main table."
They all start with z_bauer_a because there is a new bug that is giving the *_repeat_* tables that are created to hold data for database join elements the wrong name if the element is created in a repeat group.

I have dozens of instances where I am using a database join element in my project. Some were created over a year ago - and it has always worked the way I explained. This one table where fb_reports-fb_breakouts are joined is just one of numerous examples I can provide of such usage.

I attached a screen print from phpMyAdmin showing a side-by-side view of all the fabrik '_repeat_' tables that were created for all the other database join elements in that fb_Breakouts table (which is joined to the parent fb_reports). As you see in the file structure list on the right, those elements are in the fb_breakouts table - and all of the other *_repeat_* tables listed below 'fb_breakouts' on the left - for all the other database join elements - were named correctly. I.e. prefaced with 'fb_breakouts' NOT the parent table name 'fb_reports" (as happened for you and with the 2 newest database join element I just added - as shown in the last image I attached).

What would happen if you had a database join element with the same element name ('things') in the z_bauer_a table? Wouldn't fabrik also try to create another '_repeat_' table for that database join and also name it 'z_bauer_a_repeat_things'????

You get what I'm saying here???

Those database join '_repeat_' tables were ALWAYS named with the full name of the element they were holding data for - but with the word '_repeat_' inserted between the table name and the element name. Period. And there was good reason for it.

Thank you for proving that discrepancy and assuring that it isn't 'just me'. Now the question is - what broke it and how does it get fixed?

Please pass this on to Rob or Hugh so it gets the attention it needs. Thank you.

 

[felixkat]

If you can give me a rough date when you think this worked with the alternative naming convention I can try an older version of Github.

 

[Bauer]

If you can give me a rough date when you think this worked with the alternative naming convention I can try an older version of Github.

Wish I could say. I must have 100 such old versions - and went back as far as 03/16. I hadn't had to change anything in that form for months now - and just the other day was reminded that 2 of the elements I had as simple dropdown selects had to be multiselect.

Using an old version isn't going to help. I don't want to have to re-invent the wheel - especially since the way it's working now is not such a good idea (The possible duplicate name issue, for one). Anyone who has used a dbjoin element in a repeat group in the past is going to be having sour grapes over this when they run into the problem I had. God forbid they try to delete one of those elements.

It was so screwed up I had to use Akeeba kickstart to restore from a day-old backup (and lost a days work). So now I'm in limbo - waiting and wondering if this is part of the 'fix' for Joomla 3.3. Or just how safe it is to update either Joomla or fabrik in this state?