Databases
StackOverflow2013
Postsdbo
POhow do I build an association between 2 fields that do not follow any naming convention?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow do I build an association between 2 fields that do not follow any naming convention?
    primarykey
    data
    text
    <p>My company has an old website that was built many years ago in PHP. I believe the database schema represents the slow accumulation of requests that were never thought about in a strategic way. Now we are throwing out the PHP code and building the site using Rails. The database schema remains, mostly unchanged. </p> <p>I'm just getting my head around Rails, so this will be perhaps a simple question. </p> <p>I have an article table where the data looks like this:</p> <pre><code>id: 1102129 article_type_id: 5 old_article_id: 0 title: Zombie Film Locations subtitle: Hollywood-inspired survival tips for destinations teeming with the undead display_date: 0000-00-00 author_id: 29 thumbnail_image_id: 12033473 index_image_id: 12033473 article_image_id: 12033473 is_archived: 0 is_featured: 1 is_published: 1 date_to_show: 2011-10-04 00:00:00 off_the_path: 0 created: 2011-10-04 12:45:07 modified: 2011-10-11 15:33:59 fact_check_date: 0000-00-00 00:00:00 unpublish_date: 0000-00-00 is_qsfeatured: 1 featured_order: 0 </code></pre> <p>Second row:</p> <pre><code>id: 1102128 article_type_id: 14 old_article_id: 0 title: Hotel Adagio [id]35677 subtitle: Hotel Adagio display_date: 2011-09-29 author_id: 0 thumbnail_image_id: NULL index_image_id: NULL article_image_id: NULL is_archived: 0 is_featured: 0 is_published: 1 date_to_show: 2011-09-29 12:50:38 off_the_path: 0 created: 2011-09-29 12:50:38 modified: 2011-09-29 12:50:38 fact_check_date: NULL unpublish_date: NULL is_qsfeatured: 0 featured_order: NULL </code></pre> <p>The image data looks like:</p> <pre><code>id: 6722 old_id: 0 type: Index association_type: TopTen association_id: 1102129 position: 0 copyright: AMC caption: The Walking Dead portrays an abandoned, corpse-ridden downtown Atlanta, one of 10 zombie film locations we've scouted out to help you survive (and hide) should the dead rise. alt_text: Zombie film locations url: photos/index-TopTens/walkingdeadindex.jpg _url: name: index published: 0 description: </code></pre> <p>Second row:</p> <pre><code>id: 6723 old_id: 0 type: Thumbnail association_type: TopTen association_id: 1102129 position: 0 copyright: AMC caption: The Walking Dead portrays an abandoned, corpse-ridden downtown Atlanta, one of 10 zombie film locations we've scouted out to help you survive (and hide) should the dead rise. alt_text: Zombie film locations url: photos/thumb-TopTens/walkingdeadthumb.jpg _url: name: thumbnail published: 0 description: </code></pre> <p>The association_id in the images table is the id of the article, though there is no foreign key specified in the database.</p> <p>How do I get all the images when I get the article?</p> <p>Right now in the controller I do:</p> <pre><code>@articles = Article.where(:article_type_id =&gt; 5).order("id DESC").limit(5) </code></pre> <p>In the view I do stuff like:</p> <pre><code>&lt;%= article.title %&gt; </code></pre> <p>But how can I reference the images, from the article model?</p> <p>To make this more complicated, the images are polymorphic, so I can not simply create a foreign key from images to articles. Images also sometimes get mapped to "travel_guides" and "slideshows", which are 2 other tables we have.</p> <p>UPDATE:</p> <p>Changed Article so now it has:</p> <p>has_many :images, :foreign_key => 'association_id'</p> <p>Changed my controller code to:</p> <pre><code>@articles = Article.where(:article_type_id =&gt; 5).order("id DESC").limit(5).joins(:images) </code></pre> <p>But in my view I do:</p> <p>&lt;%= article.images.url %></p> <p>and I get "no method url" errors. </p> <p>I was doing: </p> <p>&lt;%= article.image.url %></p> <p>without the "s" on images, and then I got "no method 'image'". </p> <p>UPDATE:</p> <p>So, okay, so, on the command line, I run "rails c" to get into the console, and then I run the code that I'm using in my controller:</p> <p>@articles = Article.where(:article_type_id => 5).order("id DESC").limit(5).joins(:image)</p> <p>Which gives me: </p> <pre><code> @articles = Article.where(:article_type_id =&gt; 5).order("id DESC").limit(5).joins(:image) Article Load (11.5ms) SELECT `articles`.* FROM `articles` INNER JOIN `images` ON `images`.`association_id` = `articles`.`id` WHERE `articles`.`article_type_id` = 5 ORDER BY id DESC LIMIT 5 =&gt; [#&lt;Article id: 1102129, article_type_id: 5, old_article_id: 0, title: "Zombie Film Locations", subtitle: "Hollywood-inspired survival tips for destinations t...", display_date: nil, author_id: 29, thumbnail_image_id: 12033473, index_image_id: 12033473, article_image_id: 12033473, is_archived: false, is_featured: true, is_published: true, date_to_show: "2011-10-04 00:00:00", off_the_path: false, created: "2011-10-04 12:45:07", modified: "2011-10-18 10:44:15", fact_check_date: nil, unpublish_date: nil, is_qsfeatured: true, featured_order: 1, slug: "zombie-film-locations"&gt;, #&lt;Article id: 1102129, article_type_id: 5, old_article_id: 0, title: "Zombie Film Locations", subtitle: "Hollywood-inspired survival tips for destinations t...", display_date: nil, author_id: 29, thumbnail_image_id: 12033473, index_image_id: 12033473, article_image_id: 12033473, is_archived: false, is_featured: true, is_published: true, date_to_show: "2011-10-04 00:00:00", off_the_path: false, created: "2011-10-04 12:45:07", modified: "2011-10-18 10:44:15", fact_check_date: nil, unpublish_date: nil, is_qsfeatured: true, featured_order: 1, slug: "zombie-film-locations"&gt;, #&lt;Article id: 1102129, article_type_id: 5, old_article_id: 0, title: "Zombie Film Locations", subtitle: "Hollywood-inspired survival tips for destinations t...", display_date: nil, author_id: 29, thumbnail_image_id: 12033473, index_image_id: 12033473, article_image_id: 12033473, is_archived: false, is_featured: true, is_published: true, date_to_show: "2011-10-04 00:00:00", off_the_path: false, created: "2011-10-04 12:45:07", modified: "2011-10-18 10:44:15", fact_check_date: nil, unpublish_date: nil, is_qsfeatured: true, featured_order: 1, slug: "zombie-film-locations"&gt;, #&lt;Article id: 1102122, article_type_id: 5, old_article_id: 0, title: "Nude Vacations", subtitle: "These places to get naked around the world make for...", display_date: nil, author_id: 574, thumbnail_image_id: 12024629, index_image_id: 12024629, article_image_id: 12024629, is_archived: false, is_featured: true, is_published: true, date_to_show: "2011-09-23 00:00:00", off_the_path: false, created: "2011-09-23 13:13:41", modified: "2011-10-18 10:44:15", fact_check_date: nil, unpublish_date: nil, is_qsfeatured: true, featured_order: 2, slug: "nude-vacations"&gt;, #&lt;Article id: 1102122, article_type_id: 5, old_article_id: 0, title: "Nude Vacations", subtitle: "These places to get naked around the world make for...", display_date: nil, author_id: 574, thumbnail_image_id: 12024629, index_image_id: 12024629, article_image_id: 12024629, is_archived: false, is_featured: true, is_published: true, date_to_show: "2011-09-23 00:00:00", off_the_path: false, created: "2011-09-23 13:13:41", modified: "2011-10-18 10:44:15", fact_check_date: nil, unpublish_date: nil, is_qsfeatured: true, featured_order: 2, slug: "nude-vacations"&gt;] </code></pre> <p>So, despite the join, none of the image data shows up. </p> <p>UPDATE:</p> <p>Huh, now I see that in that block that I'm getting on the rails console, I see that each article is returning 3 times, I assume because of the JOIN: </p> <p>Article id: 1102129, article_type_id: 5, old_article_id: 0, title: "Zombie Film Locations", subtitle: "Hollywood-inspired survival tips for destinations t...", display_date: nil, author_id: 29, thumbnail_image_id: 12033473, index_image_id: 12033473, article_image_id: 12033473, is_archived: false, is_featured: true, is_published: true, date_to_show: "2011-10-04 00:00:00", off_the_path: false, created: "2011-10-04 12:45:07", modified: "2011-10-18 10:44:15", fact_check_date: nil, unpublish_date: nil, is_qsfeatured: true, featured_order: 1, slug: "zombie-film-locations">, </p> <h1>,</h1> <h1>,</h1> <p>So I get 3 articles because of the 3 associations with images, yet I do not get the images. This is the worst of both worlds. </p> <p>It would be easier to do a separate query for the images, really. </p> <p>UPDATE:</p> <p>This SQL gives me what I want but I don't seem able to turn this into a Rails query:</p> <pre><code>SELECT articles.title, articles.subtitle, images.url, images.caption FROM articles JOIN images ON articles.id = images.association_id WHERE images.type='Thumbnail' ORDER BY articles.id desc LIMIT 5 \G </code></pre> <p>UPDATE:</p> <p>I have not been able to get this to work. I think a Ruby tutor to teach me some basic Ruby and Rails stuff. I am happy to pay. I'm reposting my questions here: <a href="http://tutorruby.com/question/show?id=3235" rel="nofollow">http://tutorruby.com/question/show?id=3235</a></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1000155
    1. USuser1000155
    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
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POhow do I build an association between 2 fields that do not follow any naming convention?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POhow do I build an association between 2 fields that do not follow any naming convention?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POhow do I build an association between 2 fields that do not follow any naming convention?
      1. USZabba
      1. VTFavorite
    1. COWhile you're taking the time to rebuild the site in Rails, have you considered taking the time to rebuild the database schema as well? Just changing column names to be more consistent would go a long way here.
      singulars
      1. POhow do I build an association between 2 fields that do not follow any naming convention?
      1. USJordan Running
    2. COYes, there has been a lot of talk about rebuilding the database schema. However, we've been given a deadline of November 30th, which is very ambitious for a website of this scale. The database contains 150 gigs of data, so re-arranging the data is bound to be slow. The management does not want to pay for the extra hosting that would be necessary to give us the capacity to move around the data. And we are short on time. Personally, I'd like to re-do the schema, but I don't think we will be able to.
      singulars
      1. POhow do I build an association between 2 fields that do not follow any naming convention?
      1. USuser1000155
    3. COThe other reality we face is that there is some complex admin software, written in PHP, which will remain untouched, and that admin software expects the database schema to remain the same, so we really are stuck with it. There is no way 2 programmers can undo 6 years of bad decisions in 6 weeks.
      singulars
      1. POhow do I build an association between 2 fields that do not follow any naming convention?
      1. USuser1000155
 

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