Search code examples
rubysequel

How to get first value of a column using sequel and ruby?

Here is the database connection code

require 'sequel'
DB = Sequel.connect('jdbc:mysql://localhost/idemcon?user=root&password=root&zeroDateTimeBehavior=convertToNull', :max_connections => 10)

Below is a query to fetch the sum of a column from a MySQL table

Using first method

DB.fetch("SELECT SUM(bill) AS total FROM invoice WHERE provider = '#{provider}' AND invoicedate BETWEEN '#{fromdate}' AND '#{thrudate}';").first[:total] || 0

OR

Using map method and selecting element at zeroth index

DB.fetch("SELECT SUM(bill) AS total FROM invoice WHERE provider = '#{provider}' AND invoicedate BETWEEN '#{fromdate}' AND '#{thrudate}';").map(:total)[0] || 0

Note: I may get null in some cases so that's why appended || 0 to the end of the query

Is there a better way? If not Which one should I use?

Solution

  • This is probably the best way to do what you want:

    DB[:invoices].
  where(:provider=>provider, :invoice_date=>fromdate..thrudate).
  sum(:bill)

    Note that the code you provided may have up to three SQL injection vulnerabilities, depending on whether the variable values are provided by the user.