Topic: Any Chance of Making This Easier on the Database?

Well, working from a book called RailsSpace (I'm sure many of you have heard of it), I was a little disappointed to see that they used a double-blind e-mail system, instead of a PM system.  So, I set out to create my own private messaging system.  I've got it working, but I'm afraid its a little hard on the database.

I'm allowing multiple recipients for each message, which, basically, is the source of all my problems.  Any associations (at least, any I could think of to write) between the user model and the messaging model made it so that only the first recipient was recognized (in the database, recipients are separated by a comma).  So, I had to remove associations and write my own method for finding recipients (and finding who's deleted the message, and who's read it, but all three suffer the same problems).  Now I've ended up loading ALL messages in the database into an array, in order to determine which ones I actually want.  There has to be a better way to do this.  I don't suppose anyone can point me in the right direction?

The code for my deleted_messages method (pretty much the same as my read messages and received messages methods):

def deleted_messages
    @all_messages = Message.find(:all)
    @messages_deleted = Array.new
    @all_messages.each{|message|
      if message.deleted_by !=nil
        @deleted_by = message.deleted_by.split(",")
        @deleted_by.each{|deleted|
          if deleted.to_i == id
            @messages_deleted[@messages_deleted.length] = message
          end
        }
      end
    }
    @messages_deleted
  end

Thanks in advance for your help smile

Re: Any Chance of Making This Easier on the Database?

if you use mySQL:

@deleted_messages = Message.find :all, 
                                 :conditions => ["deleted_by REGEXP ?","[,^]#{id}[,$]"]

though i'm not sure how hard regular expressions are on the database ... but definately better than loading everything into mem

Re: Any Chance of Making This Easier on the Database?

Thanks very much Duplex!  That does exactly what I'm looking for smile