Rails - Apr 2010 - 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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Apr 25, 1:57 am, Scott Kulik
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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.
>
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Frederick Cheung wrote:
> On Apr 25, 1:57�am, Scott Kulik
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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?
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Apr 25, 6:35 pm, Scott Kulik
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Frederick Cheung wrote:
> > On Apr 25, 1:57 am, Scott Kulik
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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?

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
> --
> Posted viahttp://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group
athttp://groups.google.com/group/rubyonrails-talk?hl=en.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Scott Kulik wrote:
> Frederick Cheung wrote:
>> On Apr 25, 1:57�am, Scott Kulik
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Marnen Laibow-Koser wrote:
> Scott Kulik wrote:
>> Frederick Cheung wrote:
>>> On Apr 25, 1:57�am, Scott Kulik
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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
> marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
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.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Scott Kulik 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
>> marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
> 
> 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
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.