Forum: Ruby sequel: executing an arbitrary query on multiple tables

54404bcac0f45bf1c8e8b827cd9bb709?d=identicon&s=25 7stud -- (7stud)
on 2014-06-28 01:22
> I'm trying to write a class that performs a similar set of
> calculations across a group of SQLite database files for an arbitrary
> query. The query will always perform a series of numbers on which to
> perform statistical calculations. The main loop in the class will look
> like:

    run_ids.each.with_index do |run_id, idx|
      db_filename = run_id_to_filename(run_id)
      db = Sequel.connect(db_filename)
      result[idx] = db[<:table>].<query>
      db.close
    end

> Including <:table> in the constructor is easy enough, but I can't
> figure out how to inject <query> since it's a method call like
> select(:Time, :Delay) but could be more complex (tack on multiple
> exclude() statements), so I can't just do select(<:param1>, <:param2>)
> and include :param1 and :param2 in the constructor.

I think that question boils down to:

> How can I execute an arbitrary sequel query (made up of select()'s and
> exclude()'s) on multiple tables, in order to perform some
> calculations on the results.

Both select() and exclude() can be supplied with a block when calling
them, for example:

    require 'sequel'
    require 'sqlite3'

    DB = Sequel.sqlite('my_db1.db')
    dataset = DB[:numbers]

    dataset = dataset.select { [:x, :y] }   #SELECT x, y from numbers

So if you create a proc:

    my_select = Proc.new {[:x, :y]}

You can use & to turn that into a block for the select() call:

    dataset = dataset.select &my_select


If you create several select procs and several exclude procs, you can
apply them all to a dataset, like this:


    require 'sequel'
    require 'sqlite3'

    DB = Sequel.sqlite('my_db1.db')

    class Calculator
      attr_accessor :results

      def initialize(tables)
        @tables = tables
      end

      def calculate(selects, excludes)
        @results = []

        @tables.each do |table|

          dataset = DB[table]
          total = 0

          selects.each_with_index do |proc, i|
            if i == 0
              dataset = dataset.select &proc
            else
              dataset = dataset.select_append &proc
            end
          end

          excludes.each do |proc|
            dataset = dataset.exclude &proc
          end

          puts "Data selected from table #{table}:"

          dataset.each do |row|
            puts "\t #{row.inspect}"
            row.values.each do |val|
              total += val
            end
          end
          puts

          results << total
        end
      end
    end

    c = Calculator.new(
      [
        :numbers,
        :other_numbers,
      ]
    )

    #select columns x and y:
    selects = [
      Proc.new{[:x]},
      Proc.new{[:y]},
    ]

    #exclude rows where y==3 OR z==2:
    excludes = [
      Proc.new { {:y => 3} },
      Proc.new { {:z => 2} },
    ]


    =begin
    #exclude rows where y==3 AND z==2
    excludes = [
      Proc.new do |row|
        row.&(
          {:y => 3},
          {:z => 2},
        )
      end
    ]
    =end

    c.calculate selects, excludes
    puts "Results: #{c.results.inspect}"

    --output:--
    Data selected from table numbers:
       {:x=>1, :y=>1}

    Data selected from table other_numbers:
       {:x=>1, :y=>2}
       {:x=>4, :y=>5}

    Results: [2, 12]



table numbers:

         x    y    z
      +--------------
    1 |  1    1    1
    2 |  1    2    2
    3 |  1    3    3


table other_numbers:

         x    y    z
      +--------------
    1 |  1    2    3
    2 |  4    5    6
54404bcac0f45bf1c8e8b827cd9bb709?d=identicon&s=25 7stud -- (7stud)
on 2014-06-28 23:24
Some improvements:

require 'sequel'
require 'sqlite3'

DB = Sequel.sqlite('mydb1.db')

class Calculator
  attr_accessor :results

  def initialize(tables)
    @tables = tables
  end

  def calculate(selects: nil, excludes: nil)
    @results = []

    @tables.each do |table|

      dataset = DB[table]
      total = 0

      if selects
        selects.each_with_index do |proc, i|
          if i == 0
            dataset = dataset.select &proc
          else
            dataset = dataset.select_append &proc
          end
        end
      end

      if excludes
        excludes.each do |proc|
          dataset = dataset.exclude &proc
        end
      end

      puts "Data selected from table #{table}:"

      dataset.each do |row|
        puts "\t #{row.inspect}"
        row.values.each do |val|
          total += val
        end
      end
      puts

      results << total
    end
  end
end
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.