Don't delete row if another table references (referential)

[pastvne]

Hi all:
I can think of a few ways to do this, the easiest is probably with a custom PHP delete list plug-in and some PHP. But here is what I want to do and need to ask if Fabrik has something built-in.

Suppose I have a table called MASTER
and I have another table called CHILD

Suppose that I have a bunch of records in CHILD that references a row in MASTER

I want to prevent the deletion of the row in MASTER if there are any records in CHILD that refer to it.

What's the easiest way to do this?

Thanks,
BD

 

[troester]

There's a "canDeleteRow" list plugin.
You can add some php code in the Advanced tab (..select count(*) from CHILD where foreign-key=master-id...)
So the user won't get the delete icon in "still referred" rows.

 

[pastvne]

Thanks Troester. I'll read-up on that!

 

[ontarget]

Hi,
Using Fabrik 4.0alpha3 / J4.2.3
I have exactly the situation described by @pastvne
I wish to prevent deletion of records if they are used elsewhere.
A user creates an event (event table) and other users make a claim for travel expenses etc on that event (claim table)
I am using the candeleterow plugin
Here is my code:

$db = JFactory::getDbo();
//claim table
$claimtable = $db->quoteName('aaa_claims');
//event id in claim table
$eventid = $db->quoteName('event_meeting');
//event table
$eventtable = $db->quoteName('aaa_events');
//id in event table
$id = $db->quoteName('id');
//count events
$query = "SELECT COUNT(*) FROM $eventtable WHERE $id IN (SELECT " . $db->quote($eventid) . " FROM $claimtable WHERE " . $db->quote($eventid) . " = $id)";
$db->setQuery($query);
$result = $db->loadResult();
return $result;

When i run the same sql in the database it returns a correct count (36/44) records in other words 36 events have claims on them.

SELECT COUNT(*) FROM `aaa_events` WHERE `aaa_events`.`id` IN ( SELECT `aaa_claims`.`event_meeting` FROM `aaa_claims` WHERE `aaa_claims`.`event_meeting` = `aaa_events`.`id` );

a var_dump($result) shows int(0) however it is 0 for every row.
This means a event can still be deleted even though its being used.

My access settings to delete is set to
or Use field: UserId
So in other words I want users to be able to delete their own events BUT i don't want them to delete them if they are being used in the claim table.

Would updating Fabrik help?

 

[troester]

1. This is F3 forum, please post F4 questions in F4 forums
2. You should update to the latest Fabrik4 before posting issues.
3. JFactory::getDbo(); isstill working but deprecated

var(dump) your query to see what you really are selecting.
I can't see a reference to some row value.

 

[ontarget]

Apologies will post to F4 forum!
I will run the updates and revert on the F4 forum