3

I have this code to delete data from multiple tables in one go:

DB::table('tb_stikes_register_school')->where('register_id', $_POST['id'])->delete();
            DB::table('tb_stikes_register_guardian')->where('register_id', $_POST['id'])->delete();
            DB::table('tb_stikes_register_student')->where('register_id', $_POST['id'])->delete();

I'm trying to shorten this into 1 query only, register_id from guardian and school tables is the foreign key of student table. I've been trying to use join but only student table record is deleted. Is there any workaround this?

user2002495
  • 2,126
  • 8
  • 31
  • 61
  • Do you try to delete this when you are deleting a student? – Vit Kos Feb 13 '14 at 08:11
  • No, the `register_student` table is independent (it has no model). The role of that table is only to store registration data given by users (which i split into three, register_student, register_guardian, register_school), and has nothing to do with real student data and/or model. – user2002495 Feb 13 '14 at 08:37
  • the "only student table record is deleted" i mentioned in the question is referring to `tb_stikes_register_student` – user2002495 Feb 13 '14 at 08:39
  • 1
    Have you tried using `->select('*')` when using the JOIN? Otherwise an option could be to put table constraint on the migrations, such as `->onDelete('cascade')` – clod986 Feb 13 '14 at 14:06
  • @clod986: `onDelete('cascade')` works, thanks. Using `select('*')` won't help by the way. – user2002495 Feb 13 '14 at 15:14

1 Answers1

2

Something like this maybe - haven't tested

DB::table(DB::raw('FROM tb_stikes_register_school, tb_stikes_register_guardian, tb_stikes_register_student'))
->join(ENTER JOIN INFO) // wasn't clear how your tables were related
->where('register_id', $_POST['id'])
->delete();

Or you could use a fully raw query:

 DB::query('SQL statement here');

Basically recreating something similar to this: delete rows from multiple tables

Community
  • 1
  • 1
GWed
  • 15,167
  • 5
  • 62
  • 99
  • That is exactly how I did it first time, with the student's `register_id` joined with school and guardian's `register_id`, and look where student's `register_id` is same as `$_POST['id']`.But with this method only records from `tb_stikes_register_student` is deleted. What I want is the record with same `register_id` `guardian` and `school` record is also deleted. This question actually has been solved, see above comments, thanks for your time. – user2002495 Feb 14 '14 at 07:43