SQL to Active Record: Psuedo columns from a join?

2
votes

I am teaching myself the framework and have been messing with Active Record. I'm stuck with a problem that is easy to solve with raw SQL, and I'm wondering the best way to port it to Active Record ...

I have the following database schema ...

CREATE TABLE chats (
  id int(11) DEFAULT NULL,
  user_id int(11) DEFAULT NULL,
  more longtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE statuses (
  chat_id int(11) DEFAULT NULL,
  user_id int(11) DEFAULT NULL,
  is_read tinyint(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE users (
  id int(11) DEFAULT NULL,
  name varchar(255) DEFAULT NULL,
  mail varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And I want to do the following in Active Record where the 1 in the join is the current user ID ...

SELECT 
c.*, s.is_read
FROM chats c
LEFT JOIN statuses s ON c.id = s.chat_id AND 1 = s.user_id

How do I set up this relationship so that I can have a simple 'is read' variable in my chat object? I know I can do this by performing various logic on the chat and status models I have but that seems far messier?

2
sqlruby-on-rails-3activerecord

2 Answers

0
votes
class Chat
  belongs_to :user
  has_one :status, :through => :user 
  scope :readed, joins(:status).where(:status => {:is_read => true})
  scope :unreaded, joins(:status).where(:status => {:is_read => false})

  delegate :is_read, :to => :status
end

class Status
  belongs_to :user
  belongs_to :chat
end

class User
  has_many :chats
  has_many :statuses
end

@current_user.chats.readed #=> select all user chats where chat status is_read = true
@current_user.chats.unreaded
@current_user.chats.first.is_read
0
votes
class Chat < ActiveRecord::Base
  has_many :statuses
end

class Status < ActiveRecord::Base
  belongs_to :chat
end

later in your code

@chat = Chat.where( 'some where clause' )
@chat.statuses.where( 'some where clause for status' ).is_read