CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-4sum
Co2
Co2

Reputation: 353

ROR: Help for simple sum sql query

I have a table that hold values for school_user_id and homework associated with each school_user as follows:

create_table "homework_students", force: :cascade do |t|
    t.integer  "school_user_id", limit: 4, null: false
    t.integer  "homework_id",    limit: 4, null: false
    t.datetime "completed_on"
    t.datetime "created_at",               null: false
    t.datetime "updated_at",

Each school_user is associated with an individual homework and homework has many school_users.

I am trying to print the total school_users connected to an individual piece of homework on my view.

In my controller:

@total = HomeworkStudent.where("SELECT homework_id FROM homework_students WHERE 'school_user_id = (?)'").sum(:school_user_id)

Models

homework
has_many :homework_students, :class_name => 'HomeworkStudent'

homework_students
belongs_to :homework, :class_name => 'Homework', :foreign_key => :homework_id

I don't think this is right in my syntax - it keeps printing 0.

Any ideas and also nicer ways one might do this?

EDIT

Got it with this.

@total = HomeworkStudent.find_by_sql("SELECT homework_id FROM homework_students WHERE school_user_id GROUP BY homework_id").count

Still would like to know if better way.

Upvotes: 0

Views: 65

Answers (2)

Foram
Foram

Reputation: 623

In Rails 5 you can use only key value with where.

Meanwhile you dose not need to write full sql query code into where.

For count you use simple count method.

For Example,

@users = User.where(age: 20).count

Upvotes: 0

Timo Schilling
Timo Schilling

Reputation: 3073

You do some mistakes:

  1. the where(...) should not contain a WHERE statement and not a full query
  2. your WHERE statement contains a placeholder which didn't fill up with data, school_user_id = (?)
  3. your WHERE statement is wrapped into ' which results in an interpretation as string in the DB.

Try this:

@total = HomeworkStudent.where(school_user_id: 123).count
# or
@total = HomeworkStudent.where("school_user_id = (?)", 123).count

Upvotes: 2

Related Questions