Help finding database items that lost parent item

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

On Apr 25, 1:57 am, Scott K. [email protected] 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

On Apr 25, 6:35 pm, Scott K. [email protected] 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

Scott K. wrote:

Frederick C. wrote:

On Apr 25, 1:57�am, Scott K. [email protected] 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
[email protected]

Frederick C. wrote:

On Apr 25, 1:57�am, Scott K. [email protected] 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?

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
[email protected]

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
[email protected]

Marnen Laibow-Koser wrote:

Scott K. wrote:

Frederick C. wrote:

On Apr 25, 1:57�am, Scott K. [email protected] 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
[email protected]

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.