Deleting related records

[titleistfour]

Hello,

I'm trying to figure out the easiest way to go about deleted related records in relationship tables.

1. InnoDB with foreign key and cascading delete.
2. Trigger afterDelete

With option 1, probably easiest. However, I lose my fulltext indexes because the version of mysql I have does not support them for the Innodb table type. Would this work even though Xataface has a fulltext index on the dataface__index table?

With option 2, I'm not exactly sure how to code this. The API site seems down. Should I write a sql query to handle this or is there an easier method to use?

Maybe there is another option.

Thanks for any tips.

Jay

[shannah]

The decision is up to you. Do you use full-text searches often? Xataface doesn't make use of any fulltext indexes except in the search index (which is only used for the full site search) and this uses its own table so the table type of your own tables makes no difference here.

afterDelete() is another option, but it only fires when making changes through Xataface. (i.e. if you make a change through PHPMyAdmin they aren't used of course).

Example afterDelete.

Code: Select all

function afterDelete($record){
    mysql_query("delete from my_related_table where fkey='".addslashes($record->val('id'))."'", df_db());
}


-Steve

[titleistfour]

Thank you Steve. If Xataface only uses fulltext index searchs from the full site search, then I can probably change my tables over to Innodb type and let the database handle the relational deletes for me. That would be easier and better for data integrity in the long run.

I will keep the other function for reference though, just in case.

Thank you!

[titleistfour]

Steve,

Sorry, another question. Is there an easy way to get the foreign key id inserted into my table automatically?

As an example, 3 tables
books
- id
- title
topics
- id
- topic
topic_to_book
- id
- topic_id
- book_id

A topic can have multiple books associated. I have Xataface setup to recognize that relationship. So, if I add a topic, then create a relationship to a book, it adds in the relationship to the topic_to_book table for me.

But how do I setup that foreign key relationship? Do I need another field in my book table, fk_topic_to_book_id? How do I get that inserted automatically by Xataface?

Thanks,
Jay

[shannah]

I'm not sure I fully understand the question.

When using a many-to-many relationship there is a foreign key relationship from the join table to each of the other two tables. e.g.
topic_id references topics.id
book_id references books.id

Xataface's relationships don't actually use the foreign keys - they are independent.

[titleistfour]

Perhaps my lack of understanding is the problem. I would just define my relationship table as Innodb and add a foreign key like so

ALTER TABLE book ADD FOREIGN KEY(id) REFERENCES topic_to_book (book_id) ON DELETE CASCADE;

If a book is removed, then the corresponding relationship in topic_to_book is removed.

[shannah]

Actually it is the other way around. The join table references the entity table.
ALTER TABLE topic_to_book ADD FOREIGN KEY(book_id) REFERENCES book (id) ON DELETE CASCADE;

[jb606]

I had a similar issue. If you use meta:class parent/children in the relationships.ini file you can call the getChildren function to remove all the child records. I'm still working on the code, notice no permissions or error checking. This thing does a recursive delete so make sure you have a good backup of your DB just in case...I didn't

Table Info:
t_buildings
building_id
building_...
t_rooms
room_id
building_id
room_...

Code: Select all

function beforeDelete(&$record) {
                $response =& Dataface_Application::getResponse();
                $c =& $record->getChildren(); //Find the records children
                if ($c) {
                        foreach( $c as $key ) { 
                                $moreC =& $key->getChildren();  //Check to see if the child record has children of it's own
                                if ($moreC) {
                                        $this->beforeDelete($key); 
                                }
                                else {
                                        $response['--msg'] .= "Removing " . $key->getTitle();
                                        $key->delete();
                                }
                        }
                }

        }



[shannah]

The getChildren() method is just a wrapper around the getRelatedRecordObjects() essentially - except that it relies on some custom config to mark which relationship contains the "children" of the record.

I.e. you could use this same strategy for any relationship by replacing getChildren() with getRelatedRecordObjects('relationship_name')

[titleistfour]

Interesting ideas, thanks. I might try that function and see which works best.

Jay

[jb606]

The getChildren() method is just a wrapper around the getRelatedRecordObjects() essentially - except that it relies on some custom config to mark which relationship contains the "children" of the record.

I.e. you could use this same strategy for any relationship by replacing getChildren() with getRelatedRecordObjects('relationship_name')

Cool, thanks for the info.