Squeel

ZOMG CELEBRITY ENDORSEMENT

“Some of this stuff is definitely gonna blow your mind.” –Gregg Pollack, Ruby5

Getting started

In your Gemfile:

gem "squeel"  # Last officially released gem
# gem "squeel", :git => "git://github.com/ernie/squeel.git" # Track git repo

In an intitializer:

Squeel.configure do |config|
  # To load hash extensions (to allow for AND (&), OR (|), and NOT (-) against
  # hashes of conditions)
  config.load_core_extensions :hash
 
  # To load symbol extensions (for a subset of the old MetaWhere functionality,
  # via ARel predicate methods on Symbols: :name.matches, etc)
  # config.load_core_extensions :symbol
 
  # To load both hash and symbol extensions
  # config.load_core_extensions :hash, :symbol
end

The Squeel Query DSL

Squeel enhances the normal ActiveRecord query methods by enabling them to accept
blocks. Inside a block, the Squeel query DSL can be used. Note the use of curly braces
in these examples instead of parentheses. {} denotes a Squeel DSL query.

Stubs and keypaths are the two primary building blocks used in a Squeel DSL query, so
before we go on, let’s take a look at them. Most of the other examples that follow will
be based on this “symbol-less” syntax, so it might look a bit foreign otherwise.

Stubs

Stubs are, for most intents and purposes, just like Symbols in a normal call to
Relation#where (note the need for doubling up on the curly braces here, the first ones
start the block, the second are the hash braces):

Person.where{{name => 'Ernie'}}
=> SELECT "people".* FROM "people"  WHERE "people"."name" = 'Ernie'

You normally wouldn’t bother using the DSL in this case, as a simple hash would
suffice. However, stubs serve as a building block for keypaths, and keypaths are
very handy.

KeyPaths

A Squeel keypath is essentially a more concise and readable alternative to a
deeply nested hash. For instance, in standard ActiveRecord, you might join several
associations like this to perform a query:

Person.joins(:articles => {:comments => :person})
=> SELECT "people".* FROM "people"
     INNER JOIN "articles" ON "articles"."person_id" = "people"."id"
     INNER JOIN "comments" ON "comments"."article_id" = "articles"."id"
     INNER JOIN "people" "people_comments" ON "people_comments"."id" = "comments"."person_id"

With a keypath, this would look like:

Person.joins{articles.comments.person}

A keypath can exist in the context of a hash, and is normally interpreted relative to
the current level of nesting. It can be forced into an “absolute” path by anchoring it with
a ~, like:

~articles.comments.person

This isn’t quite so useful in the typical hash context, but can be very useful when it comes
to interpreting functions and the like. We’ll cover those later.

Predicates

All of the ARel “predication” methods can be accessed inside the Squeel DSL, via
their method name, an alias, or an an operator, to create ARel predicates, which are
used in WHERE or HAVING clauses.

SQL	Predication	Operator	Alias
=	eq	==
!=	not_eq	!=
LIKE	matches	=~	like
NOT LIKE	does_not_match	!~	not_like
<	lt	<
<=	lteq	<=	lte
>	gt	>
>=	gteq	>=	gte
IN	in	>>
NOT IN	not_in	<<

Let’s say we want to generate this simple query:

SELECT "people".* FROM people WHERE "people"."name" = 'Joe Blow'

All of the following will generate the above SQL:

Person.where(:name => 'Joe Blow')
Person.where{{name => 'Joe Blow'}}
Person.where{{name.eq => 'Joe Blow'}}
Person.where{name.eq 'Joe Blow'}
Person.where{name == 'Joe Blow'}

Not a very exciting example since equality is handled just fine via the
first example in standard ActiveRecord. But consider the following query:

SELECT "people".* FROM people
WHERE ("people"."name" LIKE 'Ernie%' AND "people"."salary" < 50000)
  OR  ("people"."name" LIKE 'Joe%' AND "people"."salary" > 100000)

To do this with standard ActiveRecord, we’d do something like:

Person.where(
  '(name LIKE ? AND salary < ?) OR (name LIKE ? AND salary > ?)',
  'Ernie%', 50000, 'Joe%', 100000
)

With Squeel:

Person.where{(name =~ 'Ernie%') & (salary < 50000) | (name =~ 'Joe%') & (salary > 100000)}

Here, we’re using & and | to generate AND and OR, respectively.

There are two obvious but important differences between these two code samples, and
both of them have to do with context.

To read code with SQL interpolation, the structure of the SQL query must
first be considered, then we must cross-reference the values to be substituted
with their placeholders. This carries with it a small but perceptible (and
annoying!) context shift during which we stop thinking about the comparison being
performed, and instead play “count the arguments”, or, in the case of
named/hash interpolations, “find the word”. The Squeel syntax places
both sides of each comparison in proximity to one another, allowing us to
focus on what our code is doing.
In the first example, we’re starting off with Ruby, switching context to SQL,
and then back to Ruby, and while we spend time in SQL-land, we’re stuck with
SQL syntax, whether or not it’s the best way to express what we’re trying to do.
With Squeel, we’re writing Ruby from start to finish. And with Ruby syntax comes
flexibility to express the query in the way we see fit.

Predicate aliases

That last bit is important. We can mix and match predicate methods with operators
and take advantage of Ruby’s operator precedence or parenthetical grouping to make
our intentions more clear, on the first read-through. And if we don’t like the
way that the existing predications read, we can create our own aliases in a Squeel
configure block:

Squeel.configure do |config|
  config.alias_predicate :is_less_than, :lt
end
 
Person.where{salary.is_less_than 50000}.to_sql
# => SELECT "people".* FROM "people"  WHERE "people"."salary" < 50000

And while we’re on the topic of helping you make your code more expressive…

Compound conditions

Let’s say you want to check if a Person has a name like one of several possibilities.

names = ['Ernie%', 'Joe%', 'Mary%']
Person.where('name LIKE ? OR name LIKE ? OR name LIKE ?', *names)

But you’re smart, and you know that you might want to check more or less than
3 names, so you make your query flexible:

Person.where((['name LIKE ?'] * names.size).join(' OR '), *names)

Yeah… that’s readable, all right. How about:

Person.where{name.like_any names}
# => SELECT "people".* FROM "people"  
     WHERE (("people"."name" LIKE 'Ernie%' OR "people"."name" LIKE 'Joe%' OR "people"."name" LIKE 'Mary%'))

I’m not sure about you, but I much prefer the latter. In short, you can add _any or
_all to any predicate method, and it would do what you expect, when given an array of
possibilities to compare against.

Subqueries

You can supply an ActiveRecord::Relation as a value for a predicate in order to use
a subquery. So, for example:

awesome_people = Person.where{awesome == true}
Article.where{author_id.in(awesome_people.select{id})}
# => SELECT "articles".* FROM "articles"  
     WHERE "articles"."author_id" IN (SELECT "people"."id" FROM "people"  WHERE "people"."awesome" = 't')

Joins

Squeel adds a couple of enhancements to joins. First, keypaths can be used as shorthand for
nested association joins. Second, you can specify join types (inner and outer), and a class
in the case of a polymorphic belongs_to relationship.

Person.joins{articles.outer}
=> SELECT "people".* FROM "people"
   LEFT OUTER JOIN "articles" ON "articles"."person_id" = "people"."id"
Note.joins{notable(Person).outer}
=> SELECT "notes".* FROM "notes"
   LEFT OUTER JOIN "people"
     ON "people"."id" = "notes"."notable_id"
     AND "notes"."notable_type" = 'Person'

These can also be used inside keypaths:

Note.joins{notable(Person).articles}
=> SELECT "notes".* FROM "notes"
   INNER JOIN "people" ON "people"."id" = "notes"."notable_id"
     AND "notes"."notable_type" = 'Person'
   INNER JOIN "articles" ON "articles"."person_id" = "people"."id"

You can refer to these associations when constructing other parts of your query, and
they’ll be automatically mapped to the proper table or table alias This is most noticeable
when using self-referential associations:

Person.joins{children.parent.children}.
       where{
         (children.name.like 'Ernie%') |
         (children.parent.name.like 'Ernie%') |
         (children.parent.children.name.like 'Ernie%')
       }
=> SELECT "people".* FROM "people" 
   INNER JOIN "people" "children_people" ON "children_people"."parent_id" = "people"."id" 
   INNER JOIN "people" "parents_people" ON "parents_people"."id" = "children_people"."parent_id" 
   INNER JOIN "people" "children_people_2" ON "children_people_2"."parent_id" = "parents_people"."id" 
   WHERE ((("children_people"."name" LIKE 'Ernie%' 
         OR "parents_people"."name" LIKE 'Ernie%') 
         OR "children_people_2"."name" LIKE 'Ernie%'))

Keypaths were used here for clarity, but nested hashes would work just as well.

Functions

You can call SQL functions just like you would call a method in Ruby…

Person.select{coalesce(name, '<no name given>')}
=> SELECT coalesce("people"."name", '<no name given>') FROM "people"

…and you can easily give it an alias:

person = Person.select{
  coalesce(name, '<no name given>').as(name_with_default)
}.first
person.name_with_default # name or <no name given>, depending on data

When you use a stub, symbol, or keypath inside a function call, it’ll be interpreted relative to
its place inside any nested associations:

Person.joins{articles}.group{articles.title}.having{{articles => {max(id) => id}}}
=> SELECT "people".* FROM "people" 
   INNER JOIN "articles" ON "articles"."person_id" = "people"."id" 
   GROUP BY "articles"."title" 
   HAVING max("articles"."id") = "articles"."id"

If you want to use an attribute from a different branch of the hierarchy, use an absolute
keypath (~) as done here:

Person.joins{articles}.group{articles.title}.having{{articles => {max(~id) => id}}}
=> SELECT "people".* FROM "people" 
   INNER JOIN "articles" ON "articles"."person_id" = "people"."id" 
   GROUP BY "articles"."title" 
   HAVING max("people"."id") = "articles"."id"

SQL Operators

You can use the standard mathematical operators (+, -, *, /) inside the Squeel DSL to
specify operators in the resulting SQL, or the op method to specify another
custom operator, such as the standard SQL concatenation operator, ||:

p = Person.select{name.op('||', '-diddly').as(flanderized_name)}.first
p.flanderized_name
=> "Aric Smith-diddly"

As you can see, just like functions, these operations can be given aliases.

Legacy compatibility

While the Squeel DSL is the preferred way to access advanced query functionality, you can
still enable methods on symbols to access ARel predications in a similar manner to MetaWhere:

Squeel.configure do |config|
  config.load_core_extensions :symbol
end
 
Person.joins(:articles => :comments).
       where(:articles => {:comments => {:body.matches => 'Hello!'}})
SELECT "people".* FROM "people" 
INNER JOIN "articles" ON "articles"."person_id" = "people"."id" 
INNER JOIN "comments" ON "comments"."article_id" = "articles"."id" 
WHERE "comments"."body" LIKE 'Hello!'

This should help to smooth over the transition to the new DSL.

Contributions

If you’d like to support the continued development of Squeel, please consider
making a donation.

To support the project in other ways:

Use Squeel in your apps, and let me know if you encounter anything that’s broken or missing.
A failing spec is awesome. A pull request is even better!
Spread the word on Twitter, Facebook, and elsewhere if Squeel’s been useful to you. The more
people who are using the project, the quicker we can find and fix bugs!

Copyright

http://twitter.com/rossta Ross Kaffenberger

Hey Ernie, thanks for the great write-up. I’m looking forward to trying this out.

For uses involving SQL interpolation, do you get sanitization? Taking one for your examples, if we substitute user input for ‘Ernie’, I want to ensure nothing malicious gets through:

Person.where{(name =~ “#{user_input}%”) & (salary 100000)}
http://erniemiller.org Ernie Miller

Yep, we’re just passing that string to the various ARel methods (#matches, #lt and #gt in the case above), so normal quoting rules as handled by the ARel visitors still apply. If you’re passing in a string, it should be handled as you’d hope. If you’d like to experiment, pull down the git repo and run rake console, and you can play around.
diego

Hi Ernie, impressive job! I have a question, how would you make a join like the one below with KeyPaths?

Supply.
joins(:prices => [:commercial_unit, {:supply_lines => {:supplies_control_document => :contract}}]).
where(‘contracts.project_id’ => id)
http://erniemiller.org Ernie Miller

Diego,

KeyPaths are for a single path through a tree — so you’d still need to do something like this:

joins{{prices => [commercial_unit, supply_lines.supplies_control_document.contract]}}

Note the double curly braces, one for block, one for hash. The keypath at the end will pick up from the previous parent — so prices => supply_lines.supplies_control_document.contract is equivalent to prices => {supply_lines => {supplies_control_document => contract}}
diego

thanks!, that still is much more clean and readable, installing right now!. Really awesome job, I hope this gets included in rails eventually.
Russen Guggemos

This is an amazing progression from metawhere!

My only question, is how do you use the Order node to specify ascending or descending sorting?
Russen Guggemos

Answering my own question!
Person.order{created_at.desc}
with many: Person.order{[created_at.desc, updated_at.desc]}
I couldn’t find any succinct way to add a count to left-join, but I got pretty close:

Person.select{[Person.arel_table.columns,count(id).as(number_of_cars)]}.
joins(:cars).
group(Person.arel_table.columns)

(the group() statement is necessary, since Postgres requires all columns to be grouped when selected)
John

Hi!

Is it (or will it be) possible to add conditions to JOIN statements (not to WHERE statements!), like this:
SELECT * FROM articles LEFT OUTER JOIN comments ON comments.article_id = articles.id AND comments.published;
Arturo

I would like some help with this, when i interpolate to gput some values from the params variable, it insert in the sql sintax this: Squeel::Nodes::Stub, so my sql string is this SELECT `products`.* FROM `products` WHERE `products`.`name` LIKE ‘params.#’. Im doing this Product.where{name =~ “#{params[:name]}”}
Thanks… :)
Gernot

Nice gem! I tried it in a rather large project. I had troubles with the following:

- Seemingly no support for ranges: People.where(:date => Date.new(2011,1,1)..Date.new(2011,12,31) does not work
- Different scope inside a Squeel block; cannot access member variables inside the block when defining a scope
http://erniemiller.org Ernie Miller

Can you create an issue in GitHub with details?
Emmanuel Gomez

Ernie –

Looks great. I had occasion to use MetaWhere on my last two projects and loved it (also MetaSearch… now looking forward to trying out Ransack). This is clearly a step forward from MetaWhere (and a pretty big one, at that).

It’s a shame that Rails core didn’t try to pick this up for 3.1: the lack of a native OR (disjunction) syntax for ActiveRecord::Relation is a big shortcoming in my opinion. But hey, AR3 is such a huge step forward from AR2 I shouldn’t complain…

On my current project I’m using DataMapper, and I’ve got to say, your Keypaths are basically an ActiveRecord implementation of Query::Path from DataMapper. Don’t know if you were aware of the prior art. Just saying… good artists copy, great ones steal :D.
Emmanuel Gomez

To be fair, you’ve gone ~10 steps further with the keypath concept than is present in DM, but the core concept is the same.

BTW, I fully plan to steal some of this an implement it in DM, once I get the infrastructure built up enough to support some of the extended stuff you’re doing. Fair warning :D
http://erniemiller.org Ernie Miller

TBH I think I did see that path syntax at some point, and like it. May well have been the inspiration behind KeyPath. Happy to have KeyPaths “stolen.” Steal away! :)
Emmanuel Gomez

Ernie — is it possible to build up a correlated subquery using a Relation as the basis for the subquery? If so, how would I reference a value from the outer query within the subquery?

Will post again if I wrangle the answer for myself.
Emmanuel Gomez

John — I might be wrong, but a decent query optimizer should push down the restriction from a where clause into the join (technically a theta join, ie., restricted cartesian product). By decent query optimizer, I mean Postgres and SQLite, and *maybe* MySQL.

Run EXPLAIN on your query with the restriction in the JOIN clause vs in the WHERE clause, and see if the output differs.
Emmanuel Gomez

Oops, got my terminology wrong. A theta join is not a restricted cartesian product, it’s a comparator-based join, and this is a common special case called an equi-join. So says Wikipedia, anywhow: http://en.wikipedia.org/wiki/Join_(SQL)
Gernot Kogler

I have now converted all my code to squeel and wrote some new, quite complex queries. Squeel is awesome! I’m not sure if it’s possible, but it would be great if one could use scopes on includes like so:

Person.where { name == ‘Meier’ }.includes { addresses }.in_europe

in_europe would be a scope on Address and the query would return all People named ‘Meier’ and the addresses collection populated with addresses from europe only
http://twitter.com/Lephyrius Jonas Innala

How do I translate this to Squeel: ”Manager.joins(:employees.outer).group(‘managers.id’).having(:employees => (:count.func(:id) < 3))" ?
Maurizio De Santis

Hello ernie,

thanks for your gems! I follow your projects since the meta_(where|search) gems, they are wonderful projects.

I would ask you: I use PostgreSQL, and I make large use of its powerful regex operators (SIMILAR TO,
~, ~*, !~, !~* -> see http://www.postgresql.org/docs/8.3/static/functions-matching.html ); seen that Squeel translates =~ and !~ respectively in LIKE and NOT LIKE, the other pattern matching operators are not usable with squeel (and now I am noticing that ILIKE and ILIKE NOT are excluded, too).

And so I ask you: can you consider a support to other regex operators?

Cheers!
http://erniemiller.org Ernie Miller

Re: ILIKE, ARel “matches” predicate already translate to ILIKE in PostgreSQL, for uniformity with other DB engines’ case-insensitive matching. As for the other ones, you can create them with the “op” method.
Gernot Kogler

Hi Ernie!

I have many models that live on a timeline. I need a lot of where-conditions like ‘date_field is null OR date_field >= ‘.

I’d like to implement this pattern as a predicate so I could write ‘Model.where { date_field.null_or_gte value }. Could you please give me a hint where I should start?

Thanks!
Gernot
Cyprian Kowalczyk

Hi!
First of all: this is one great gem you developed!
Quick question: are bitwise operators supported by squeel syntax?
Like: User.where(‘permissions & ? > 0′, perm)
Thanks!
http://erniemiller.org Ernie Miller

Yup. Try User.where{permissions.op(‘&’, perm) > 0}
http://twitter.com/fivetwentysix fivetwentysix

So what if you want to use Ruby keywords as column names?

Eg scope :use_keyword, lambda{ where { return > Time.year – 365.days } }
Иван Егоров

Hi!
Thanks for your great MetaWhere gem)

But I have a question.
In MetaWhere I did:

sql = {}email_starts_with = “vany%”sql["growth"] = 0..200sql = sql & (:rating > 50)sql = sql & (:email =~ email_starts_with)……….User.where(sql).to_sql
=> ”SELECT “users”.* FROM “users” WHERE “users”.”growth” BETWEEN 0 AND 200 AND “users”.”rating” > 50 AND “users”.”email” ILIKE ‘vany%’”
user = User.where(sql).first
=> #

How can I do the same using Squeel?

Thanks for any help)
http://erniemiller.org Ernie Miller

That wasn’t metawhere, it was metasearch. You may want to have a look at Ransack.
Иван Егоров

My Gemfile:

gem ‘rails’, ’3.0.9′
………
gem “meta_where”
………
http://erniemiller.org Ernie Miller

Sorry, the disqus email screwed up the formatting. I saw

email_starts_with

and thought it was a metasearch query.

The Squeel dsl blocks are like any other block. Just build up your query terms inside the block for access to DSL stuff like stubs and keypaths.
Bradphelan

Emmanual. You don’t quite understand the use of left outer join. It is not the same to put the ON conditions into the WHERE conditions for a left outer join. For example

SELECT * FROM articles LEFT OUTER JOIN comments ON comments.article_id = articles.id AND comments.published WHERE comments.id is NULL

The above query selects all articles that have no articles published. If you push the condition into the WHERE

SELECT * FROM articles LEFT OUTER JOIN comments ON comments.article_id = articles.id WHERE comments.published AND comments.id is NULL

it doesn’t work. It’s a totally different query.
Jerome

Definitely agree. I think including the conditions in the WHERE clause rather than the ON clause, by default, is a mistake.
Emmanuel Gomez

Hi Brad,

You didn’t quite understand my comment :). Your misunderstanding is understandable though, as I was less than clear overall.

I actually was proposing that conditions should be migrated from WHERE to JOIN … ON when possible (“push down the restriction from a where clause into the join”).

What I meant by ‘a decent query optimizer should push down the restriction from a where clause into the join’ is that a desirable capability of a query optimizer is to apply restrictions (eg., predicates/conditions of a WHERE or ON clause) as early as possible, in order that the minimal set of tuples (records) is considered in subsequent operations, such as during a JOIN.

One of the tricky things about that is to apply restrictions as early as possible without changing the meaning of the query: moving predicates between WHERE and OUTER JOIN … ON clauses can easily change the meaning of the query (and thus the results obtained), as you point out.

A transformation I find useful for thinking about these things is to restate OUTER JOIN as a set operation, eg from your:

SELECT * FROM articles LEFT OUTER JOIN comments ON comments.article_id = articles.id AND comments.published WHERE comments.id is NULL

To:

SELECT * FROM articles WHERE id NOT IN (SELECT article_id FROM comments WHERE published = TRUE)

Now, this is a query that is logically equivalent, and is a much clearer example for demonstrating what I meant by pushing down the restriction (ie., the `published = TRUE` predicate).

However, I believe the subselect-based transformation will not perform nearly as well as the OUTER JOIN version on MySQL, as its query optimizer handles the OUTER JOIN better than NOT IN. There’s a brief mention of this here (although there’s a rather defensive note that this is ‘not specific to MySQL Server alone’): http://dev.mysql.com/doc/refman/5.0/en/rewriting-subqueries.html . There are also documented shortcomings in MySQL which cause NOT IN to perform *much* worse than equivalent OUTER JOIN, but now I’m really digressing.
Gabe Odess

what happend to all those nice helpers from the days of MetaWhere and Searchlogic. I loved User.login_like(‘gabeodess’)
http://metautonomo.us Ernie Miller

You’re thinking of MetaSearch, not MetaWhere. MetaWhere never had that.
Nicolas PAPON

hi, how do you test your custom scopes based on squeel, where_values_hash send back and empty hash,

i’m currently testing the sql output doing something like : scope.debug_sql.should include(“SELECT…”)

but that’s not optimal does anybody have the “good” way to proceed ?
Jorgeramos

Hello, I have the following Model association: categories have_and_belong_to_many :words and words have_and_belong_to_many :categories. I am interested in finding all words matching a list of categories and i defined a search method in the Phrase model as follows:

def self.find_in_categories(list)
    (Phrase.joins{categories.inner}.
      where{
        (categories.name >> list)
      }).order{view_count.desc}
end

This is successfully finding phrases matching any category in the list parameter but if any phrase is found in more than one category it is returned multiple times. How can I get modify the search to avoid the duplicates?
Jorgeramos

Sorry, I recently changed the model name from Word to Phrase so the above code is confusing.

phrases have_and_belong_to_many :categories
categories have_and_belong_to_many:phrases

Within Phrase model i have:
def self.find_in_categories(list)
    (Phrase.joins{categories.inner}.
      where{
        (categories.name >> list)
      }).order{view_count.desc}
end
http://www.facebook.com/mhuggins Matt Huggins

This comment is from 6 months ago. Is there a way to add “on” conditions to a join in Squeel yet?
Nikhil Gupte

Is it possible to do an “OR” on two scopes?

For example, consider a simple example where I have a class User with 2 scopes – professors and lecturers

Then, can I re-use the 2 scopes to get users who may be either professors or lecturers?
Some thing like User.professors.or.lecturers
http://twitter.com/guillaume_nm Guillaume Nourry-Mar

Hi Ernie,

Thanks for the great work. I was able to synthetize my code by 50%, while doing more, and in a more performant manner.

G
Guy Silva

Hi,
great job with squeel!

I think I found a bug :)
I have this scope working fine

scope :on_period, ->(start_period, end_period) {
where{ ((end_date > start_period ) | (end_date == nil) ) & ( start_date (start_period, end_period) {

where{ (( start_period < end_date ) | (end_date == nil) ) & ( start_date < end_period) }

}

it stops working resulting in a where conditions like: WHERE (1)

is that suppose to behave like that?
Ciantainc

Can some clarify this question? How to add ‘on’ condition to joins in Squeel?
Ross

Good stuff.
It looks like it might be meant for has_many relations as opposed to belongs_to
because where table names are pluralized joining on a :belongs_to (-eg- belongs_to :role), will result in a table name of ‘role’ when it should be ‘roles’
Is this intended or have I got it wrong.
Ross
Wendel Schultz

I may or may not understand what you’re trying to do. I think I’ve gotten around something similar by creating a Squeel expression string then evaluating it:

… // whatever nonsense I do

case operator
when ‘contains’
squeel_exp = “proxy.where{ #{filter.attribute_name}.#{sql_operation} ( ‘%#{value}%’ ) }”
when ‘not contains’
squeel_exp = “proxy.where{ ( #{filter.attribute_name}.eq nil ) | ( #{filter.attribute_name}.#{sql_operation}( ‘%#{value}%’ ) ) }”
…
end

return eval( squeel_exp )

Let me disclaim that I don’t have any good data for performance impact, but right now I’m in the “get it correct” phase, and performance will come later if necessary.
Walther Diechmann

Hi Ernie!

First off – thanx a bunch for sharing this wonderful ‘toy’ :)

Second – I’ve got this vanilla polymorphic wage_periodables which allows me to add Teams and Employees to wage_periods (amongst others) – but then I’d like to be able to query any wage_period for team.employees and employees (some employees are added to a wage_period outside their team, others through their team):

class WagePeriod
has_many :wage_periodables
has_many :teams, through: :wage_periodables, :source => :wage_periodable, :source_type => ‘Team’
has_many :single_employees, through: :wage_periodables, :source => :wage_periodable, :source_type => ‘Employee’
has_many :team_employees, through: :teams, source: :employees

# honoring the /wage_periods/12/employees
def employees
team_employees + single_employees
end
end

The employees method “sort-a-works” offering me an Array – but using it further (I like to include a Ransack query option) is hopeless.

I’ve tried with team_employees.joins{:single_employees}, team_employees.union{:single_employees} and all sort’a tricks but I just cannot seem to get it right?

Is there any way around collecting employee records through two different polymorphic HMT’s? And I do not even dare thinking about how to add the DISTINCT option (excluding the employees possibly added both through the team and directly)?

Cheers,
Walther
Walther Diechmann

ehh – here’s the (short-cut) SQL that “solves for X”

“SELECT e.* FROM employees e, wage_periodables w, wage_periods wp WHERE wp.id=12 AND w.wage_period_id=wp.id AND ( (w.wage_periodable_type=’Team’ AND w.wage_periodable_id=e.team_id) OR (w.wage_periodable_type=’Employee’ AND w.wage_periodable_id=e.id) )”
http://twitter.com/wdiechmann Walther H. Diechmann

Well – had to give up on the SQUEEL way :(

so I had to do it this way:

Employee
.select(“DISTINCT employees.*”)
.from(“employees, wage_periodables as w, wage_periods as wp”)
.where(["
wp.id=? AND
w.wage_period_id=wp.id AND
(
(w.wage_periodable_type='Team' AND w.wage_periodable_id=employees.team_id) OR
(w.wage_periodable_type='Employee' AND w.wage_periodable_id=employees.id)
)
", id])

About

My ugly mug I'm Ernie Miller. But then, you probably knew that by looking at the page title, or the URL. I'm a Ruby programmer in Louisville, Kentucky. This blog used to be called "metautonomo.us", which I thought was kind of clever, but nobody, including me, could type it. Lesson learned.

Follow @erniemiller