5

Given I have some dataset methods foo, bar and baz

class User < Sequel::Model
  dataset_module do
    def foo
      # Some complicated dataset here
      where(:c => 42, :d => 23)
    end

    def bar
      # Even more complicated dataset here
      where(:a => 5, :b => 23).or(:a => 23, :b => 5)
    end

    def baz
      where(:d => 17)
    end
  end
end

I want to query the database for (foo || bar) && (bar || baz) (or some other complicated dataset). So I tried

User.where{|u| (u.foo | u.bar) & (u.bar | u.baz)}

EDIT: Clarification:

What I got

SELECT * FROM users WHERE ((`foo` OR `bar`) AND (`bar` OR `baz`))

What I wanted

SELECT * FROM users WHERE ((<dataset foo> OR <dataset bar>) AND (<dataset bar> OR <dataset baz>))

where <dataset xyz> means my defined datasets. So

<dataset foo> is defined as (c = 42 AND d = 23)

<dataset bar> is defined as ((a = 5 AND b = 23) OR (a = 23, b = 5))

<dataset baz> is defined as (d = 17)

How do I chain dataset methods with AND, OR and (most important) brackets?

iblue
  • 29,609
  • 19
  • 89
  • 128
  • Isn't, in effect, what you're trying to accomplish an inner join? – mralexlau Mar 27 '14 at 19:17
  • No, I am trying to combine different datasets on the same table. No join is required. – iblue Mar 28 '14 at 12:44
  • In your example, all of the data will be from the Users table, right? Just because you're only working with one table [doesn't mean you can't do a join](http://stackoverflow.com/questions/3362038/what-is-self-join-and-when-would-you-use-it). – mralexlau Mar 28 '14 at 16:08
  • But I don't want to do a join. I just want to combine datasets. – iblue Mar 28 '14 at 16:53
  • At least in terms of Ruby, User.where{|u| (u.foo | u.bar) & (u.bar | u.baz)} won't get you far. Double bar (||) and double ampersand (&&) are the boolean OR/AND. Could just be a typo, though. – Sady Mar 28 '14 at 19:27
  • This is Sequel syntax, because `&&` and `||` cannot be overloaded. – iblue Mar 28 '14 at 19:28
  • 1
    Please fix your code, it isn't valid: `:a => 23, b: => 5`. Moreover, where the method `dataset_methods` comes from? Running the fixed code on Ruby 2.1.0 and Sequel 4.8.0, it raises a `NoMethodError`: `undefined method \`dataset_methods' for User:Class`. – toro2k Mar 29 '14 at 10:39
  • Sorry. The method is called `dataset_module` (my mistake). It defines methods on the classes dataset. – iblue Mar 29 '14 at 13:14

2 Answers2

1

Are you not looking for union and intersection methods. These work on datasets and can be used as replacement for 'AND' and 'OR' in set theory.

Wouldn't something like this work for you?

fooOrbar = User.foo.union(User.bar)
barOrbaz = User.bar.union(User.baz)
result = fooOrbar.intersect(barOrbaz)
System123
  • 523
  • 5
  • 14
  • Yes, this would return the given data, but it would generate a query including `UNION` and `INTERSECT`. I would like to get a query including just `AND` and `OR`. – iblue Apr 02 '14 at 18:39
0

I believe your methods should return sequel expressions, look at: http://sequel.jeremyevans.net/rdoc/classes/Sequel/SQL.html

Alex
  • 1,210
  • 8
  • 15
  • No, they don't, as dataset modules return datasets. I am looking for a way to combine datasets (not just chaining with `AND` like `Model.dataset1.dataset2`, but with `AND`, `OR` and brackets - like in the question). – iblue Mar 27 '14 at 16:32