Commit | Line | Data |
6c2a4396 |
1 | =head1 NAME |
2 | |
3 | DBIx::Class::Manual::SQLHackers::DELETE - DBIx::Class for SQL Hackers - DELETE |
4 | |
5 | =over |
6 | |
7 | =item L<Introduction|DBIx::Class::Manual::SQLHackers::Introduction> |
8 | |
9 | =item L<CREATE|DBIx::Class::Manual::SQLHackers::CREATE> |
10 | |
11 | =item L<INSERT|DBIx::Class::Manual::SQLHackers::INSERT> |
12 | |
13 | =item L<SELECT|DBIx::Class::Manual::SQLHackers::SELECT> |
14 | |
15 | =item L<UPDATE|DBIx::Class::Manual::SQLHackers::UPDATE> |
16 | |
17 | =item DELETE |
18 | |
19 | =item L<BEGIN, COMMIT|DBIx::Class::Manual::SQLHackers::Transactions> |
20 | |
21 | =back |
22 | |
23 | =head1 DELETEing data |
24 | |
25 | =head2 Delete a single row based on the primary key |
26 | |
27 | DELETE FROM users |
28 | WHERE id = 1; |
29 | |
30 | The simplest form of delete removes a single row from a table using |
31 | the primary key value. We B<find> the row, then call the B<delete> |
32 | method on it. B<delete> can be called on any result row object. |
33 | |
34 | =over |
35 | |
36 | =item 1. Create a Schema object representing the database you are working with: |
37 | |
38 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
39 | |
40 | =item 2. Call the B<find> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to delete: |
41 | |
42 | my $fred_user = $schema->resultset('User')->find({ id => 1 }); |
43 | |
44 | =item 3. Call B<delete> on the row object: |
45 | |
46 | $fred_user->delete; |
47 | |
48 | =back |
49 | |
50 | This can also be done as one statement, skipping the extra temporary |
51 | variable, if it is not needed later: |
52 | |
53 | $schema->resultset('User')->find({ id => 1 })->delete; |
54 | |
55 | In the first variant, the $fred_user row object will still contain the |
cd1e0628 |
56 | last known contents of Fred's data. A call to L<$fred_user->in_storage|DBIx::Class::Row/in_storage> will return |
57 | false (0), showing that the row object is no longer connected to a actual |
6c2a4396 |
58 | database row. |
59 | |
60 | =head2 Delete one or more rows based on a WHERE clause |
61 | |
62 | DELETE FROM posts |
63 | WHERE created_date <= '2000-01-01'; |
64 | |
65 | Use a ResultSet to define the WHERE clause using B<search>, then call |
66 | the B<delete> method on it directly. |
67 | |
68 | =over |
69 | |
70 | =item 1. Create a Schema object representing the database you are working with: |
71 | |
72 | my $schema = MyDatabase::Schema->connect('dbi:SQLite:my.db'); |
73 | |
74 | =item 2. Call the B<search> method on the resultset for the L<ResultSource|DBIx::Class::ResultSource> you wish to delete: |
75 | |
76 | my $old_posts = $schema->resultset('Post')->search({ |
77 | created_date => { '<=' => '2000-01-01' }, |
78 | }); |
79 | |
80 | =item 3. Call B<delete> on the row object: |
81 | |
82 | $old_posts->delete; |
83 | |
84 | =back |
85 | |
86 | Unlike the single row deletion above, the contents of the rows to be |
87 | deleted are never fetched from the database, so no record of them now |
88 | remains. |
89 | |
90 | NOTE: Calling B<delete> on a ResultSet object will not run any |
91 | overridden B<delete> methods in your Result Classes or any loaded |
92 | Components. To force these to run, call B<delete_all> instead: |
93 | |
94 | $old_posts->delete_all(); |
95 | |
96 | This will also issue a separate delete statement for each row to be removed. |
97 | |
98 | =head2 Cascading deletes |
99 | |
100 | DELETE FROM users |
101 | WHERE id = 1; |
102 | |
103 | DELETE FROM comments |
104 | WHERE user_id = 1; |
105 | |
106 | |
107 | Cascading deletes ensure the integrity of your data, if a User row is |
108 | removed, then any items belonging to that user (for example comments |
109 | created by the user), should also be removed. |
110 | |
111 | NOTE: This is a rather drastic action, to prevent problems in your |
112 | application, consider de-activating accounts instead of removing them! |
113 | |
2f41b1a9 |
114 | For the time being DBIx::Class defaults to cascade deletion for the following types |
6c2a4396 |
115 | of relationships: B<has_many>, B<has_one>, B<might_have>. That is, it |
2f41b1a9 |
116 | will automatically issue the above statements. It is recommended not to rely |
117 | on this implicit behavior, as it will be deprecated in a later version of DBIC. |
118 | Instead declare proper cascading constraints in your RDBMS as described in |
119 | L<DBIx::Class::Manual::SQLHackers::CREATE/Table creation with references>. |
6c2a4396 |
120 | |
2f41b1a9 |
121 | If your database is already properly set up to cascade deletes for you, |
122 | you can noop DBIx::Class' extra cascading statements: |
6c2a4396 |
123 | |
124 | __PACKAGE__->has_many('posts', |
125 | 'MyDatabase::Schema::Result::Post', |
126 | 'user_id', |
127 | { cascade_delete => 0 }); |
128 | |
129 | |
130 | |