DB Code Optimization

rubyonrails-talk
1

Hi, I have the following tables:

- users -- id -- username

- questions -- id -- asker_id -- question_text

- answers -- id -- answerer_id -- answer_text

And I wanna make a query to get all questions, with the username who asked, and all question answers. I made the code but please tell me if is a good practice:

class User < ActiveRecord::Base     has_many :questions end class Question < ActiveRecord::Base     validates :question_text, presence: true     belongs_to :user, foreign_key: "asker_id"     has_many :answers end

class Answer < ActiveRecord::Base     belongs_to :question     belongs_to :user, foreign_key: "answerer_id" end Controller: @questions = Question.joins(:user).joins('LEFT JOIN answers on answers.question_id = questions.id').order(id: 'desc').group(:id) A user can:

Have more questions

A question can:

Have more answers

2

I’m not sure I’m reading this right, but if you’re looking for the answers to the questions asked by a specific user wouldn’t it be

@user = User.find(params[:id] @user.questions = the questions the user asked @user.questions.answers = the answers to the questions the user asked.

THere’s something troubling to me about your model set up but I can’t think of what it is.

3

I’m not sure I’m reading this right, but if you’re looking for the answers to the questions asked by a specific user wouldn’t it be

@user = User.find(params[:id] @user.questions = the questions the user asked @user.questions.answers = the answers to the questions the user asked.

THere’s something troubling to me about your model set up but I can’t think of what it is.

Hi, I have the following tables:

  • users

– id

– username

  • questions

– id

– asker_id

– question_text

Even though you have the column as “asker_id” rather than the conventional “user_id”, your use of the foreign_key: on the belongs_to compensates.

  • answers

– id

– answerer_id

– answer_text

Again with the un-conventional foreign_key, but what’s missing is a “question_id” to go along with your Answer model’s belongs_to :question

And I wanna make a query to get all questions, with the username who

asked, and all question answers. I made the code but please tell me if

is a good practice:

class User < ActiveRecord::Base

has_many :questions

you need to declare the foreign_key here, too (foreign_key: :asker_id)

Also, you can add:

has_many :answers, through: :questions

has_many :responses, class_name: "Answer", foreign_key: :answerer_id

end

class Question < ActiveRecord::Base

validates :question_text, presence: true

belongs_to :user, foreign_key: "asker_id"

has_many :answers

end

class Answer < ActiveRecord::Base

belongs_to :question

belongs_to :user, foreign_key: "answerer_id"

def asker

self.question.try(:user)

end

end

Controller:

@questions = Question.joins(:user).joins('LEFT JOIN answers on

answers.question_id = questions.id’).order(id: ‘desc’).group(:id)

A user can:

Have more questions

A question can:

Have more answers

Then you can have any of the following:

@user = User.includes(questions: :answers).find_by username: “foo”

@user.questions # for all this user’s questions

@user.answers # for all the answers to all the questions asked by this user

@user.responses # for the answers that this user gave in response to a question

@questions = Question.includes(:user, :answers).order(id: :desc).all

@answer = Answer.includes(:user, :question).find_by id: some_id

@answer.user # the one who gave the answer

@answer.question # the question

@answer.asker # the one who asked the question

That should get you going.

-Rob