1

When I do Question.first it returns a record with id=7, however....there is an ID=1 in my db and it doesn't return it.

 > Question.first
  Question Load (1.8ms)  SELECT "questions".* FROM "questions" LIMIT 1
 => #<Question id: 7, so_id: 1642028, creation_date: "2009-10-29 06:57:45", score: 1812, accepted_answer_so_id: 1642035, title: "What is the name of this operator: &quot;--&gt;&quo...", view_count: 124958, link: "http://stackoverflow.com/questions/1642028/what-is-...", body: "<p>After reading \"<a href=\"http://groups.google.com...", answer_count: 17, is_answered: true, owner: "GManNickG", created_at: "2013-03-23 07:02:48", updated_at: "2013-03-23 07:02:48", accepted_answer_id: nil> 
1.9.3p392 :026 > Question.find(1)
  Question Load (1.9ms)  SELECT "questions".* FROM "questions" WHERE "questions"."id" = $1 LIMIT 1  [["id", 1]]
 => #<Question id: 1, so_id: 11227809, creation_date: "2012-06-27 13:51:36", score: 4072, accepted_answer_so_id: 11227902, title: "Why is processing a sorted array faster than an uns...", view_count: 216266, link: "http://stackoverflow.com/questions/11227809/why-is-...", body: "<p>Here is a piece of C++ code that shows some very...", answer_count: 9, is_answered: true, owner: "GManNickG", created_at: "2013-03-23 07:02:10", updated_at: "2013-03-23 10:27:17", accepted_answer_id: nil>

Both are different questions, but the real question is why doesn't .first return the record with id=1?

Edit 1

This is the schema for my Question table:

  create_table "questions", :force => true do |t|
    t.integer  "so_id"
    t.datetime "creation_date"
    t.integer  "score"
    t.integer  "accepted_answer_so_id"
    t.string   "title"
    t.integer  "view_count"
    t.string   "link"
    t.text     "body"
    t.integer  "answer_count"
    t.boolean  "is_answered"
    t.string   "owner"
    t.datetime "created_at",            :null => false
    t.datetime "updated_at",            :null => false
    t.integer  "accepted_answer_id"
  end

  add_index "questions", ["accepted_answer_so_id"], :name => "index_questions_on_accepted_answer_so_id"
  add_index "questions", ["so_id"], :name => "index_questions_on_so_id"
  add_index "questions", ["title"], :name => "index_questions_on_title"

Edit 2

Just confirmed that it also happens in another model Answer as well (so something funky is happening here):

> Answer.first
  Answer Load (1.0ms)  SELECT "answers".* FROM "answers" LIMIT 1
 => #<Answer id: 6629, so_id: 1048093, creation_date: "2009-06-26 08:58:19", is_accepted: false, question_id: 400, owner: "Christian Hayter", score: 10, created_at: "2013-03-23 09:21:33", updated_at: "2013-03-23 09:23:32", body: "<p>I'd prefer <code>INFORMATION_SCHEMA.COLUMNS</cod..."> 
1.9.3p392 :008 > Answer.find(1)
  Answer Load (44.7ms)  SELECT "answers".* FROM "answers" WHERE "answers"."id" = $1 LIMIT 1  [["id", 1]]
 => #<Answer id: 1, so_id: 11227902, creation_date: "2012-06-27 13:56:42", is_accepted: true, question_id: 1, owner: "Mysticial", score: 6423, created_at: "2013-03-23 07:02:10", updated_at: "2013-03-23 09:43:12", body: "<p><strong>You are the victim of <a href=\"http://en...">

Although, it doesn't do that in another model:

> Tag.first
  Tag Load (24.5ms)  SELECT "tags".* FROM "tags" LIMIT 1
 => #<Tag id: 1, name: "java", num_questions: 388310, created_at: "2013-03-23 07:02:09", updated_at: "2013-03-23 07:02:09">
marcamillion
  • 32,933
  • 55
  • 189
  • 380
  • How did you create your table in the migration and what is the primary index, die `id` column? The sort order for the column is automatically set in the correct order if you setup a clustered index. – YvesR Mar 23 '13 at 10:57
  • The primary index was left at default. I used the scaffold generator to generate my model an everything. – marcamillion Mar 23 '13 at 10:58
  • Check your table design in the database, if it is a unique index and not a clustered one that you need to order your results, if it is a clustered index it should give you the results you want this to be. – YvesR Mar 23 '13 at 10:59
  • I updated my question to reflect the schema of my questions table. – marcamillion Mar 23 '13 at 11:03
  • Was searching the web to check if you can setup migrations to set a clustered index but did not found any results yet :S But try to change the table design and check again if you get the intended results. Check http://dev.mysql.com/doc/refman/5.5/en/innodb-index-types.html – YvesR Mar 23 '13 at 11:08
  • I just posted more results in my question. It is happening in another model too...not just my `Question` table - so there must be something else wrong. – marcamillion Mar 23 '13 at 11:10
  • Well check your development log and seek for the created SQL statement for `Answer.first` and it is probably something like `SELECT TOP 1 * FROM answers`. So there is not sort order by default and you get the first record in the way the DB wants to deliver it. Did you check what type of index on the id column is created? – YvesR Mar 23 '13 at 11:14
  • The SQL produced by `Answer.first` is in my question (right below the command)...which is `Answer Load (1.0ms) SELECT "answers".* FROM "answers" LIMIT 1`. Not quite sure how to check the type of index. – marcamillion Mar 23 '13 at 11:17
  • Everything that I have looked up says "default sort order is unpredictable". I don't know what kind of answer you want here. – mraaroncruz Mar 23 '13 at 11:19
  • @marcamillion See this post how to handle indexes http://stackoverflow.com/questions/9766661/sql-server-how-to-write-an-alter-index-statement-to-add-a-column-to-the-unique – YvesR Mar 23 '13 at 11:24

1 Answers1

2

You get your results in the order they are read from disk. They are not ordered by your primary key.

You can do this in your question.rb file

default_scope order(:id)

and it will order by id when you do first or all

or just

 Question.order(:id).first
mraaroncruz
  • 3,780
  • 2
  • 32
  • 31
  • I stole that knowledge from these SO answers http://stackoverflow.com/questions/6585574/postgres-default-sort-by-id-worldship – mraaroncruz Mar 23 '13 at 10:52
  • Hrmm...your explanation would make sense - for sure - but....at the same time, I don't understand why that would make sense - from a Rails perspective. As in...why would they make a method called `first` that doesn't scope or order by the created_at date? Surely this can't be right. – marcamillion Mar 23 '13 at 10:52
  • I think the answer to your question is in the find(:first) docs http://api.rubyonrails.org/classes/ActiveRecord/FinderMethods.html#method-i-find You can throw in conditions which make `first` make more sense in a Rails way. – mraaroncruz Mar 23 '13 at 10:55
  • On second thought...the explanation doesn't make sense. It would seem to me that the logical way to search for the 'first' result is according to the `created_at` timestamp and nothing else. It so happens that AR increments the ID per the order of created_at, but....if I were writing that method, I would look at created_at rather than anything else. – marcamillion Mar 23 '13 at 10:55
  • This is the SQL you get back from `Widget.first` `SELECT "widgets".* FROM "widgets" LIMIT 1`. The database decides. – mraaroncruz Mar 23 '13 at 10:57
  • Right...I know that.....you can see the SQL statement in my question. It still doesn't explain why it returns some seemingly 'random' record. It also does it over and over. i.e. I quit the console and restarted it, still pulling the same record with `id=7` not `id=1`. I have never had to question the `.first` method before....so I am not sure what's happening. – marcamillion Mar 23 '13 at 10:59
  • This is the code that executes that `LIMIT 1` query - https://github.com/rails/rails/blob/da5e5c5f779355a2e99e63a90612cbeaeb0fc986/activerecord/lib/active_record/relation/finder_methods.rb#L376-L382 - which seems fine to me. This tells me it SHOULD be returning the first record in my table (i.e. `id=1`). – marcamillion Mar 23 '13 at 11:06
  • So it seems it may be a Postgres thing - which means you are right. This is so strange though. – marcamillion Mar 23 '13 at 11:36