# Help finding database items that lost parent item **URL:** \ **Category:** Rails\ **Created:** [April 25, 2010, 2:57am UTC](https://www.ruby-forum.com/t/help-finding-database-items-that-lost-parent-item/187537 "2010-04-25T02:57:42Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![skirkpatrick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/73ab20/32.png) [@skirkpatrick](https://www.ruby-forum.com/u/skirkpatrick)\ **Post date:** [April 25, 2010, 2:57am UTC](https://www.ruby-forum.com/t/help-finding-database-items-that-lost-parent-item/187537/1 "2010-04-25T02:57:42Z")
I have a table for private messages similar to: PMS: title body sent\_to (foreign key to users table) sent\_from (foreign key to users table) When I originally set it up I didn’t have it automatically a users pm’s when that user was deleted. So…I have basically been manually updating the database when a users has problems with their private messages page. I’m wondering if someone knows a good way I could scan the the pm’s table and delete messages that no longer have a user for sent\_to or sent\_from. I suppose this would probably best be done with a rake task but I’m unsure of the condition syntax to check for the existence of the user. Here is what I started to write: task(:deletePMs =\> :environment) do @pms = Pms.find(:all, :conditions =\> ‘???’) @pms.each do |pm| pm.destroy puts “PM =\> " + pm.id + " destroyed!” end end ---
**Author:** ![skirkpatrick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/73ab20/32.png) [@skirkpatrick](https://www.ruby-forum.com/u/skirkpatrick)\ **Post date:** [April 25, 2010, 10:56am UTC](https://www.ruby-forum.com/t/help-finding-database-items-that-lost-parent-item/187537/2 "2010-04-25T10:56:58Z")
On Apr 25, 1:57 am, Scott K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > updating the database when a users has problems with their private > messages page. > > I’m wondering if someone knows a good way I could scan the the pm’s > table and delete messages that no longer have a user for sent\_to or > sent\_from. sounds like a job for a left join. If I were you I’d also setup a foreign key constraint so that this sort of thing can’t happen in the future Fred ---
**Author:** ![skirkpatrick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/73ab20/32.png) [@skirkpatrick](https://www.ruby-forum.com/u/skirkpatrick)\ **Post date:** [April 25, 2010, 8:24pm UTC](https://www.ruby-forum.com/t/help-finding-database-items-that-lost-parent-item/187537/3 "2010-04-25T20:24:08Z")
On Apr 25, 6:35 pm, Scott K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > foreign key constraint so that this sort of thing can’t happen in the > > future > > > Fred > > Thanks Fred. The problem is i don’t know the syntax for the join. Did > you happen to know off the top of your head? I very highly reccomend learning that sort of stuff. select \* from foos left join bars on foos.id =bars.foo\_id Will join rows from foos with rows form bars. Unlike an inner join (where no rows are returned if there is no bar satisfying the condition for a given foo) with a left join such foos will result in a row being returned, with all the columns for bars being null, so you just need to have a IS NULL condition on a column that cannot be null (eg bars.id) to find all foos with no associated bar. Fred ---
**Author:** ![skirkpatrick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/73ab20/32.png) [@skirkpatrick](https://www.ruby-forum.com/u/skirkpatrick)\ **Post date:** [April 26, 2010, 2:21pm UTC](https://www.ruby-forum.com/t/help-finding-database-items-that-lost-parent-item/187537/4 "2010-04-26T14:21:47Z")
Scott K. wrote: > Frederick C. wrote: > > > On Apr 25, 1:57�am, Scott K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > > > updating the database when a users has problems with their private > > > messages page. > > > > > > I’m wondering if someone knows a good way I could scan the the pm’s > > > table and delete messages that no longer have a user for sent\_to or > > > sent\_from. > > > > sounds like a job for a left join. If I were you I’d also setup a > > foreign key constraint so that this sort of thing can’t happen in the > > future > > > > Fred > > Thanks Fred. The problem is i don’t know the syntax for the join. Did > you happen to know off the top of your head? Then please spend some quality time with an SQL reference. If you can’t write simple joins, then you are not yet ready to develop Web applications that use SQL databases. ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![skirkpatrick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/73ab20/32.png) [@skirkpatrick](https://www.ruby-forum.com/u/skirkpatrick)\ **Post date:** [April 25, 2010, 7:35pm UTC](https://www.ruby-forum.com/t/help-finding-database-items-that-lost-parent-item/187537/5 "2010-04-25T19:35:06Z")
Frederick C. wrote: > On Apr 25, 1:57�am, Scott K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > updating the database when a users has problems with their private > > messages page. > > > > I’m wondering if someone knows a good way I could scan the the pm’s > > table and delete messages that no longer have a user for sent\_to or > > sent\_from. > > sounds like a job for a left join. If I were you I’d also setup a > foreign key constraint so that this sort of thing can’t happen in the > future > > Fred Thanks Fred. The problem is i don’t know the syntax for the join. Did you happen to know off the top of your head? ---
**Author:** ![skirkpatrick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/73ab20/32.png) [@skirkpatrick](https://www.ruby-forum.com/u/skirkpatrick)\ **Post date:** [April 26, 2010, 4:58pm UTC](https://www.ruby-forum.com/t/help-finding-database-items-that-lost-parent-item/187537/6 "2010-04-26T16:58:04Z")
Scott K. wrote: […] > > Then please spend some quality time with an SQL reference. If you can’t > > write simple joins, then you are not yet ready to develop Web > > applications that use SQL databases. > > > > ## Best, > > > > Marnen Laibow-Koser > > [http://www.marnen.org](http://www.marnen.org) > > removed\_email\_address@domain.invalid > > Wow, that’s harsh. It was not intended to be, and I’m sorry it came across that way. > Just because I don’t know the syntax offhand without > doing some research doesn’t give you the right to bash me. If you > really want to talk down to people then I don’t think that you belong on > a forum that is about helping people using Ruby on Rails. I’m not bashing you. I just want to remind you of something that is, effectively, a prerequisite for what you’re doing. > BTW, I have successfully developed a website that gets 750,000 to > 1,000,000 page views a month. Good for you. > I thought this was a place I could come > to get some insight without worrying about looking stupid. ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![skirkpatrick](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/73ab20/32.png) [@skirkpatrick](https://www.ruby-forum.com/u/skirkpatrick)\ **Post date:** [April 26, 2010, 4:46pm UTC](https://www.ruby-forum.com/t/help-finding-database-items-that-lost-parent-item/187537/7 "2010-04-26T16:46:11Z")
Marnen Laibow-Koser wrote: > Scott K. wrote: > > > Frederick C. wrote: > > > > > On Apr 25, 1:57�am, Scott K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > > > > > updating the database when a users has problems with their private > > > > messages page. > > > > > > > > I’m wondering if someone knows a good way I could scan the the pm’s > > > > table and delete messages that no longer have a user for sent\_to or > > > > sent\_from. > > > > > > sounds like a job for a left join. If I were you I’d also setup a > > > foreign key constraint so that this sort of thing can’t happen in the > > > future > > > > > > Fred > > > > Thanks Fred. The problem is i don’t know the syntax for the join. Did > > you happen to know off the top of your head? > > Then please spend some quality time with an SQL reference. If you can’t > write simple joins, then you are not yet ready to develop Web > applications that use SQL databases. > > ## Best, > > Marnen Laibow-Koser > [http://www.marnen.org](http://www.marnen.org) > removed\_email\_address@domain.invalid Wow, that’s harsh. Just because I don’t know the syntax offhand without doing some research doesn’t give you the right to bash me. If you really want to talk down to people then I don’t think that you belong on a forum that is about helping people using Ruby on Rails. BTW, I have successfully developed a website that gets 750,000 to 1,000,000 page views a month. I thought this was a place I could come to get some insight without worrying about looking stupid.