Databases
StackOverflow2013
Postsdbo
POStructuring using rails ActiveRecord
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStructuring using rails ActiveRecord
    primarykey
    data
    text
    <p>I'm virtually a beginner at both Ruby and the Rails framework, which was why I've decided to ask for help before I do something that breaks the conventions of the framework.</p> <p>I have a fairly solid <code>OO</code> programming background, and I'm pretty comfortable with beginner->intermediate level SQL queries. However, I've been having trouble wrapping my head around the <code>ActiveRecord</code> class that Rails supplies. My immediate instinct is to just scrap the ActiveRecord class entirely, and write out my own SQL queries by hand and wrap them up in models. However, I know ActiveRecords are a fairly integral part of the Rails framework, and avoiding them will just cause me pain in the future.</p> <p>The following is my <code>MySQL</code> schema as it stands (I'll write a <code>Rails Migration</code> later on). I'll try to keep this question as concise as possible, but I may have to go into a little background to explain why I've modeled the schema as I have. I'm not overly attached to it, so if people have better ideas on the structure that'd be great.</p> <pre><code>-- Users table is a minimalized version of what it probably will be, but contains all pertinent information CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) UNIQUE NOT NULL ) Engine=InnoDB; CREATE TABLE IF NOT EXISTS hashtags ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, tag VARCHAR(30) UNIQUE NOT NULL ) Engine=InnoDB; CREATE TABLE IF NOT EXISTS content_mentions ( content_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, INDEX(content_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ) Engine=InnoDB; CREATE TABLE IF NOT EXISTS content_hashtags ( content_id INT UNSIGNED NOT NULL, hashtag_id INT UNSIGNED NOT NULL, INDEX(content_id), FOREIGN KEY(hashtag_id) REFERENCES hashtags(id) ON DELETE CASCADE ) Engine=InnoDB; CREATE TABLE IF NOT EXISTS content_comments ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, content_id INT UNSIGNED NOT NULL, text_body VARCHAR(1000) NOT NULL, date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX(content_id) ) Engine=InnoDB; CREATE TABLE IF NOT EXISTS polls ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, question VARCHAR(100) NOT NULL, text_body VARCHAR(1000) NOT NULL, date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ) Engine=InnoDB; CREATE TABLE IF NOT EXISTS poll_options ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, poll_id INT UNSIGNED NOT NULL, content VARCHAR(150) NOT NULL, active VARCHAR(1) NOT NULL DEFAULT 'Y', FOREIGN KEY(poll_id) REFERENCES polls(id) ON DELETE CASCADE ) Engine=InnoDB; CREATE TABLE IF NOT EXISTS poll_answers ( poll_option_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, FOREIGN KEY(poll_option_id) REFERENCES poll_options(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, PRIMARY KEY(poll_option_id,user_id) ) Engine=InnoDB; </code></pre> <p>As the schema would indicate, this is a really basic web-poll application. Each poll has multiple options and each option can have multiple answers by different users. Now, the odd part perhaps looking at it is the <code>content_*</code> tables. The best way I can explain this is probably by describing it as an <code>abstract</code> table. I've never really done anything like that before, usually the relationships are between two or more explicit tables and I'll add the foreign keys as required. In this case, however, I may end up with multiple different types of <code>content</code>, all of which require hashtagging/mentioning/commenting. I don't know in advance what table the <code>content_id</code> refers to (the code will deal with the data it receives appropriately) so I've just <code>indexed</code> the column for now. I need to adjust the <code>content_*</code> tables to add a <code>type</code> column at some stage as once more than one <code>content</code> table is present, there may be duplicate <code>content_id</code> entries if both tables use an auto-incrementing primary key, I think that's a bit out of the scope of the question though.</p> <p>On to the structuring of the ActiveRecord classes. The first part is handling the parsing of mentions/hashtags. I wrote an abstract <code>Content</code> class to handle the "abstract" side of the tables. It goes something like this (some of the parsing has been removed for brevity).</p> <pre><code>class Content &lt; ActiveRecord::Base self.abstract_class = true; # relationships belongs_to :user has_many :content_mentions; has_many :content_hashtags; has_many :mentions, { :through =&gt; :content_mentions, :source =&gt; :user, :as =&gt; :content }; has_many :hashtags, { :through =&gt; :content_hashtags, :as =&gt; :content }; # available columns (in the abstract side of things) attr_accessible :text_body, :date_created; # database hooks around_save :around_save_hook # parsing ENTITY_PATTERN = /removed_for_brevity/iox; def render_html() # parsing of the text_body field for hashtags and mentions and replacing them with HTML # goes in here, but unrelated to the data so removed. end protected # Is this the best way to do this? def around_save_hook() # save the main record first (so we have a content_id to pass to the join tables) yield # parse the content and build associations, raise a rollback if anything fails text_body.scan(ENTITY_PATTERN) do |boundary,token,value| m = $~; if m[:token] == '@' # mention unless mentions.where(:name =&gt; m[:value]).first mention = User::where(:name =&gt; m[:value]).first; next unless mention; raise ActiveRecord::Rollback unless content_mentions.create({ :content_id =&gt; id, :user_id =&gt; mention.id }); end else # hashtag unless hashtags.where(:tag =&gt; m[:value]).first hashtag = Hashtag.where(:tag =&gt; m[:value]).first; unless hashtag hashtag = Hashtag.new({ :tag =&gt; m[:value] }); raise ActiveRecord::Rollback unless hashtag.save(); end raise ActiveRecord::Rollback unless content_hashtags.create({ :content_id =&gt; id, :hashtag_id =&gt; hashtag.id }); end end end end end </code></pre> <p>The main problem I have here is with the <code>around_save_hook</code>, is this the best place to parse and save the associations? How would I do it so that if the <code>text_body</code> was updated and some of the hashtags/mentions removed from the original, these changes would be reflected in the <code>content_*</code> associations, rather than just the new hashtags/mentions added without checking for removals?</p> <p>The rest of the <code>ActiveRecord</code> classes are defined as follows:</p> <pre><code>class Poll &lt; Content has_many :poll_options; has_many :poll_answers, { :through =&gt; :poll_options } attr_accessible :user_id, :question; validates :text_body, :presence =&gt; true, :length =&gt; { :maximum =&gt; 1000 }; end class PollOption &lt; ActiveRecord::Base belongs_to :poll; has_many :poll_answers; attr_accessible :content, :active, :poll_id; end class PollAnswer &lt; ActiveRecord::Base belongs_to :poll_option; belongs_to :user; attr_accessible :user_id, :poll_option_id; end class User &lt; ActiveRecord::Base attr_accessible :name; validates :name, :presence =&gt; true, :length =&gt; { :maximum =&gt; 20 }; end class Hashtag &lt; ActiveRecord::Base attr_accessible :tag; validates :tag, :presence =&gt; true, :length =&gt; { :maximum =&gt; 30 }; end # Join table for content-&gt;users class ContentMention &lt; ActiveRecord::Base belongs_to :user; belongs_to :content, { :polymorphic =&gt; true }; attr_accessible :content_id, :user_id; end # Join table for content-&gt;hashtags class ContentHashtag &lt; ActiveRecord::Base belongs_to :hashtag; belongs_to :content, { :polymorphic =&gt; true }; attr_accessible :content_id, :hashtag_id; end </code></pre> <p>So I guess my questions are as follows:</p> <ol> <li>Is the schema itself corrent (i.e is it grossly inefficient and poorly designed to use with rails? (if so, suggestions on how to correct it would be fantastic)</li> <li>Is <code>Around Save</code> the right place to parse and update the associations?</li> <li>Are my ActiveRecords set up correctly based on the current schema structure? (specifically I'm not sure if I'm using the <code>polymorphic</code> attribute correctly)</li> <li>How would I add options/answers to a <code>Poll</code> instance, without re-saving the entire content of the Poll (and thus triggering another redundant parsing of the content) while still retaining an <code>OOP</code> approach to this? (i.e options/answers are created through a public API from the <code>Poll</code> model)</li> </ol> <p>It'd be really great if someone who's really comfortable with <code>Rails</code>, <code>Ruby</code> and <code>ActiveRecord</code> could run me up a quick copy of how they would implement the bare-bones of this. As I said I've never used the <code>ActiveRecord</code> class before so I'm not even sure how many raw SQL queries this simple code will be triggering on a single <code>save()</code> call already.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJason Larke
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POStructuring using rails ActiveRecord
      1. This table or related slice is empty.
      1. VTModeratorReview
    2. VO
      singulars
      1. POStructuring using rails ActiveRecord
      1. USJason Larke
      1. VTBountyStart
    3. VO
      singulars
      1. POStructuring using rails ActiveRecord
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think this question is a bit too big - it almost asking for consultancy on your app. I would say that if you're starting with Rails, just go ahead and try it, and as you make mistakes and learn then you can go back and improve incrementally. However, one tip is to use `after_save` instead of `around_save` and `yield`.
      singulars
      1. POStructuring using rails ActiveRecord
      1. USJon Cairns
    2. COI understand it's a fairly large question, but what I'm asking would only be a trivial amount of code, most of it is just some advice on correctly using ActiveRecords in nested join-tables, avoiding the need to redundantly update records. The hashtag/mention/comment side of the app is irrelevant to the question (other than a general question about structuring "abstract" tables), so essentially it's just asking how to implement a `Poll` using ActiveRecords, not really 'big'. As for the `after_save` method, I couldn't find a clear piece of documentation as to whether you can rollback inside it.
      singulars
      1. POStructuring using rails ActiveRecord
      1. USJason Larke
    3. COYou're probably right that it's a trivial amount of code, but it's the understanding of the context that takes a huge amount of time investment for someone to be able to provide that trivial code. It would be better to break it down into smaller questions where possible.
      singulars
      1. POStructuring using rails ActiveRecord
      1. USJon Cairns
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload