Fixed Join list delete related

[elmarra]

Good morning everyone.
I have three lists.
List A, List B, List C
Both list A and B join list C via:
Date > Unions
left join from list A (also B) to list C
from the mydatabasejoin column (list A and B) to the ID column (list c).
merge rows and reduce data display mode.
Clear join data on yes.
List A and List B are the two lists from which I add data
While List C contains some of the data inserted from the two lists.
Since the data is inserted from both List A and List B with the delete join data option active.
Now for example I delete a row. Suppose this has ID 4 In list A it deletes the row with ID4 in list C.
despite this having been inserted from the B list (and therefore should not be eliminated).
This is because having list A and list B where I insert the data, the IDs do not correspond to those in list C.
The logic is right.
But how could I remedy this situation?

 

[troester]

Trying to understand your setup:

ListA LEFTJOIN ListC from listA.dbjoin to listC.id
ListB LEFTJOIN ListC from listB.dbjoin to listC.id

So both joins are not repeatable (and merge rows/reduce data doesn't make sense/doesn't have any effect).

"Delete joined data" is thought for joins from x.id to y.foreignKey.
So maybe there's a bug if you activate it in case of joining from x.FK to y.ID (I didn't test until now, I'll have a look).

Deactivate "Delete joined data" for now.

If urgent you can add a form php plugin onRecordDeletion or list php events onDeleteRows (use only one of these, it's the "same" trigger, not sure what will happen if both are active) and delete the record with custom code.

 

[elmarra]

ListA LEFTJOIN ListC from listA.dbjoin to listC.id
ListB LEFTJOIN ListC from listB.dbjoin to listC.id

Exactly. I have merged the rows and reduced the data because both lists have a repeating group.
Therefore, in order to delete the data in listA_01_repeat as well (which is automatically generated from the repeating group).
Both lists have two unions: one automatic created by the repeating groups and mine set to connect to list C. If I disable the deletion of joined data when I delete a record, it will not delete the one related to the repeating group.
Therefore, I will need to disable the deletion of joined data; otherwise, I risk deleting a record that shouldn't be deleted.
I will need to configure the PHP plugin to delete both the record from the repeating group table and the records related to list C, correct?

 

[troester]

Yes, with multiple list joins it's complicated...
because 'delete joined' is not per join but per list.

I had a look at the code, it's really a bug in case of joining x.FK to y.ID (and I don't see a quick solution).

Can you change your setup by adding e.g. 2 dbjoins to listC, one for joining listA.id to listC.dbjoinA, one for joining listB.id to listC.dbjoinB?

Then 'delete joined' should do, deleting the correct records in listC and for your repeat group tables.

Otherwise you must do all the 'delete joined' on your own.

 

[elmarra]

Can you change your setup by adding e.g. 2 dbjoins to listC, one for joining listA.id to listC.dbjoinA, one for joining listB.id to listC.dbjoinB?

"I didn't understand much.
In listA_id, do I need to set the join to listC dbjoinA and in listB_id to listC dbjoinB? So, I need to create the dbjoin element in listC and not in listA and listB? Did I understand correctly?"

 

[troester]

Yes, exactly.
2 new dbjoins in listC and then new joins in listA and listB.

Then you have the "usual" joins
from x.ID to y.FK
which should be handled correctly on record deletion.

 

[elmarra]

ok exactly.
In listC I created two dbjoin elements.
dbja which is linked to list id a.
Same thing for dbjb towards list id b.
In fact, now if I delete a record it deletes the exact one.

 

[elmarra]

Oops the only thing I noticed now is this.
On list C
there is no longer any linked data.
with the previous configuration
On list C
I had the unions and therefore in the table I had two connections:
ListA and ListB.
so from the ListC I no longer have the link to the union
Do you think I can somehow recover this too?

 

[troester]

Hmm, you'll need additionally the dbjoins in listA and listB (dbjoin elements are "creating" the related data, not a list join).
I think you can set the dbjoin value with a php form plugin onAfterProcess in your listA and listB forms, fetching the newly created listC id.

(Maybe I'll get a fix for the delete related issue till next week).

 

[elmarra]

Okay, I have added two more dbjoins.
In list A and B.
Dbjoin to id list C.
I merged the lists.
Then, in both list A and B, I inserted the following code:

use Joomla\CMS\Factory;
$mydb = Factory::getContainer()->get('DatabaseDriver');
$listaCIdQuery = "SELECT id FROM list_c ORDER BY id DESC LIMIT 1";
$mydb->setQuery($listaCIdQuery);
$listaCId = $mydb->loadResult();
if ($listaCId) {
    $linkUpdateQuery = "UPDATE list_a
                        SET dbj_a = " . $mydb->quote($listaCId) . "
                        WHERE id = (SELECT MAX(id) FROM list_a)";
    $mydb->setQuery($linkUpdateQuery);
    $mydb->execute();
}

and it works. So if you manage to fix the bug, will the deletion function normally?

 

[troester]

Glad you got it working.

What do you mean with "I merged the lists."?

So if you manage to fix the bug, will the deletion function normally?

I hope so

 

[elmarra]

What do you mean with "I merged the lists."?

When I say I combined the lists, I mean that now in list C, I have the link to the details based on list A or B. (I mean not the join of the lists, but the linked data.)

Now if I delete a row in list a or b I also delete the record in list c.
If I delete a record from the list c it deletes the related data in the other two lists but the row remains (I think this is normal)

I hope so

Keep us updated

 

[troester]

Bitbucket

bitbucket.org