How to pass a variable in Rails find_by_sql and group_by

Question

I have 2 tables. due_loans and payments. Payments table has due_loan_id. Now I want to find all payments belonging to a due loan and perform the calculations in a below SQL. This sql query is working perfectly in MySQL workbench. However, I am unable to make work in Rails. I don't know how to pass variables and group in find_by_sql.

select d.id dueid, d.issue_loan_id, d.duedate,d.dueamount, sum(ifnull(amount, 0))paid, dueamount - sum(ifnull(amount, 0))balance from due_loans d
left join payments p
on d.id = p.due_loan_id
where d.issue_loan_id = 15
group by dueid

If there is no payment for a corresponding due loan then I am assigning a 0 for calculations. Among other solutions, this is what I tried.

  def get_due_loans_results
   @dueloans = DueLoan.find_by_sql(["select d.id dueid, d.issue_loan_id, d.duedate, d.dueamount, sum(ifnull(amount, 0))paid, dueamount - sum(ifnull(amount, 0))balance from due_loans d
                                left join payments p
                                on d.id = p.due_loan_id
                                where d.issue_loan_id =?", params[:issue_loan_id]].group_by => 'dueid')
   respond_to do |format|
   format.json  {render json: @dueloans}
  end
end

daniyel · Accepted Answer · 2019-02-03 11:50:10Z

1

Did you try like:

DueLoan.find_by_sql(["select d.id dueid, d.issue_loan_id, d.duedate, d.dueamount, sum(ifnull(amount, 0))paid, dueamount - sum(ifnull(amount, 0))balance from due_loans d
                                left join payments p
                                on d.id = p.due_loan_id
                                where d.issue_loan_id = ? group by dueid", params[:issue_loan_id]])

answered Feb 3, 2019 at 11:50

daniyel

68211 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JohnMilo Over a year ago

Now I have and it works. I thought group is always a last argument. Thanks a lot.

Collectives™ on Stack Overflow

How to pass a variable in Rails find_by_sql and group_by

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related