Hi all, Back in the Rails 3.0 days, there was thought and discussion about how the AR query API might be improved to support more advanced queries, and in general make better use of some of the capabilities of ARel. I''d had some brief discussions with Pratik at the time, during which he mentioned that the plan was to step back, allow some plugins to tackle the problem, then select a reasonable way to proceed from there. So, in April of 2010, I released MetaWhere, inspired by that discussion with Pratik and his sharing of https://gist.github.com/265308 with me. I''ve maintained MetaWhere and its successor, Squeel<http://github.com/ernie/squeel>, for almost 3 years now. Combined, they have around 1600 watchers/stars/whatever-they''re-called-now on GitHub (around 10% of the watchers of Rails itself). On last week''s Ruby Rogues<http://rubyrogues.com/081-rr-rails-4-with-aaron-patterson/>, Avdi Grimm named Squeel his "pick", stating: [...] there is a gem called Squeel (S-Q-U-E-E-L) and it puts… it adds a lot> of niceties in doing SQL queries to Active Record. So, basically it adds > stuff where instead of passing just parameters to "where" you can pass a > block – and a block can basically just have Ruby code in it that compares > things to each other using equals or lesser than or various other operators > and Squeel takes that and turns that into SQL. And it works really, really, > well.> It’s so nice for building up queries. It goes well beyond just that little > bit of turning Ruby into SQL, but also makes it a lot easier to deal with > joins and subqueries, and basically makes some things possible that are if > not impossible, then really hard in just base Active Record and ARel. Like > doing non… doing less common types of joins, ORing together two different > result sets and stuff like that — without actually getting the results and > putting them together in memory -- actually turning it into a composite > query that does the right thing in SQL. I’ve been getting a lot of use out > of it on one project I’m working on for a client and it’s been a huge, huge > help.So, with the background and requisite testimonial out of the way, I''ve been hoping that perhaps Squeel, or something like it would see a merge into core at some point, based on the previously-mentioned discussion with Pratik. Seeing that AR 4.0 has accepted Akira Matsuda''s PR for some enhancements to the query API, I''m hoping this means that the core team is open to considering some serious enhancements to the query API for 4.0. A response on Twitter from Santiago got me thinking that I should really do more than hope, based on previous experiences<https://github.com/rails/rails/pull/1915#commitcomment-770892> . While the most common usage of Squeel is via instance_eval, and I know core is traditionally not in favor of such dark arts, it''s perfectly possible to use Squeel syntax without instance_eval, as in: Post.where { |post| post.title.like(''zomg%'') | post.title.like(''bbq%'') } # => SELECT "posts".* FROM "posts" WHERE (("posts"."title" LIKE ''zomg%'' OR "posts"."title" LIKE ''bbq%'')) It supports function calls: Post.where { |q| q.concat(q.title, q.description).like ''%something%'' } # => SELECT "posts".* FROM "posts" WHERE concat("posts"."title", "posts"."description") LIKE ''%something%'' ...and keypaths, allowing traversal of associations, and even polymorphic belongs_to joins: Note.joins { |join| join.notable(Person) }.where { |note| note.notable(Person).name == ''Ernie'' } # => SELECT "notes".* FROM "notes" INNER JOIN "people" ON "people"."id" = "notes"."notable_id" AND "notes"."notable_type" = ''Person'' WHERE "people"."name" = ''Ernie'' It resolves table aliases automatically (shown with instance_eval syntax for clarity): 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%'')) I''d go on, but to say that I (and others) think it''s a useful extension to the existing AR syntax would be an understatement. Some of the examples shown above illustrate that while the instance_eval is more concise, it''s also possible to create perfectly readable and expressive queries using the less magic yielded syntax. I''m not sure if it''s the case now, but early in development, I ran through the AR test suite with Squeel enabled, and it didn''t break any of the existing tests, either. Not making that claim at this point, but I''ve worked very hard to ensure solid compatibility with typical AR query syntax, the only real difference being the behavior of symbols when used as values in queries (symbol, being an identifier, maps to a field identifier, or column name, allowing "Post.where(:title => :description)" to search for posts with identical titles and descriptions). I''d welcome input as to whether there''s any interest in seeing Squeel (or some subset of it) being merged to core. Thanks in advance for your consideration! -Ernie Miller -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/PGFLjX9ZeNkJ. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
Em 07-12-2012 11:44, Ernie Miller escreveu:> ... > While the most common usage of Squeel is via instance_eval, and I know > core is traditionally not in favor of such dark arts, it''s perfectly > possible to use Squeel syntax without instance_eval, as in: > > Post.where { |post| > post.title.like(''zomg%'') | post.title.like(''bbq%'') > } > # => SELECT "posts".* FROM "posts" WHERE (("posts"."title" LIKE > ''zomg%'' OR "posts"."title" LIKE ''bbq%'')) >What if a column is named "class"? I''d rather prefer something like that: Post.where { |post| post[:title].like(''zomg%'') | post[:class].like(''bbq%'') } -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
On Monday, December 10, 2012 4:08:06 PM UTC-5, Rodrigo Rosenfeld Rosas wrote:> What if a column is named "class"? I''d rather prefer something like that: > > Post.where { |post| > post[:title].like(''zomg%'') | post[:class].like(''bbq%'') > } > >In Squeel''s specific case, the DSL object is a blankslate, so it works fine. Hash-style syntax would be another option. That would avoid method_missing overhead for simple column access, which would be a good thing, but make things less readable, as well. Not my personally-preferred tradeoff, but certainly could see a case for it. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/Als2_nNq-08J. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
Em 10-12-2012 19:40, Ernie Miller escreveu:> On Monday, December 10, 2012 4:08:06 PM UTC-5, Rodrigo Rosenfeld Rosas > wrote: > > What if a column is named "class"? I''d rather prefer something > like that: > > Post.where { |post| > post[:title].like(''zomg%'') | post[:class].like(''bbq%'') > } > > > In Squeel''s specific case, the DSL object is a blankslate, so it works > fine. Hash-style syntax would be another option. That would avoid > method_missing overhead for simple column access, which would be a > good thing, but make things less readable, as well. Not my > personally-preferred tradeoff, but certainly could see a case for it.There is a recent (scattered) discussion about this topic on ruby-core mailing list: https://bugs.ruby-lang.org/issues/4085#note-249 As noticed in that scattered thread, the odd-named columns (such as "dotted.column") aren''t my only concern with Squeel approach (when the block has zero arity) as there may be conflicts with local methods and variables/arguments names. And I don''t see much difference (as you state it is less readable) between: post[:title].like(''zomg%'') and post.title.like(''zomg%'') But I find this more consisent: post[:"dotted.column"].like(''zomg%'') than this: post.__send__(:"dotted.column").like(''zomg%'') You could actually support both syntaxes, but if you do so you wouldn''t be able to support things like this in the future: post.like(:some_column, ''some value'') as an alternative to post[:some_column].like(''some value'') Not that I think that would be wanted at some point but maybe we could find some useful methods to define on the dsl object in the future. With regards to GitHub stars, I wouldn''t count much on that. I use GH stars as a global "favorites" thing so that I can quickly find some project I want to cite or take a closer look in the future. Most GH projects I starred I don''t actually use. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
On Tuesday, December 11, 2012 6:35:48 AM UTC-5, Rodrigo Rosenfeld Rosas wrote:> > But I find this more consisent: > > post[:"dotted.column"].like(''zomg%'') > > than this: > > post.__send__(:"dotted.column").like(''zomg%'') > > You could actually support both syntaxes, but if you do so you wouldn''t be > able to support things like this in the future: > >I guess I don''t follow, here -- do you mean a column with an actual dot in its name? I''ve been working with SQL for over 15 years, and while I know it''s possible to do, I''ve never encountered a good reason for it. I agree that the first is more consistent in this case, but this seems like the edgiest of edge cases -- and, as you said, could be supported with alternative syntax. Not worried about supporting "like(:column, value)", personally. I like left to right readability when possible.> > With regards to GitHub stars, I wouldn''t count much on that. I use GH > stars as a global "favorites" thing so that I can quickly find some project > I want to cite or take a closer look in the future. Most GH projects I > starred I don''t actually use. >Didn''t mean to imply much importance for stars, but as a relative metric, it seemed moderately useful -- they are as important/unimportant everywhere. ;) -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/yvIB9X00NfgJ. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
At the risk of returning to the original broader topic, I thought I''d put in a couple cents. Rails applications have long since moved past simple CRUD and are embracing more complex applications and more sophisticated database integration. The proliferation of a variety of strong NoSQL integrations, and the embracing of advanced PostgreSQL data types in the default postgresql adapter in Rails 4, are indicative of this. It is high time for ActiveRecord''s query API to catch up. As an advocate of effective use of database features, I''ve been a long time supporter of Squeel because of how well it supports advanced queries. For example, I do a lot of GIS work, and for such applications, queries tend to include many SQL function calls, often nested. These are very natural with Squeel; with the base AR, I have to drop down to string interpolation. This is why I tend to recommend Squeel to developers doing GIS applications in Rails. There will always be debate on the merits of API design, especially when a DSL is involved, but Squeel is mature and has done an excellent job of navigating the issues as well as providing fallbacks to more traditional syntax. It is more feature-complete than the status quo, and pretty much has no rival when it comes to constructing non-trivial queries. It is already in widespread use, and it is backward compatible with the existing query API. I think the decision should be a no-brainer. If we consider Rails 4 feature-locked at this point, then Squeel should be at the top of the consideration list for 4.1. Daniel Azuma Author of rgeo and activerecord-postgis-adapter -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-core/-/KFF-OlKXU6kJ. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.
Rodrigo Rosenfeld Rosas
2012-Dec-12 09:06 UTC
Re: Re: Improving the ActiveRecord query API
On Tue, Dec 11, 2012 at 9:20 PM, Daniel Azuma <dazuma@gmail.com> wrote:> ... > There will always be debate on the merits of API design, especially when a > DSL is involved, but Squeel is mature and has done an excellent job of > navigating the issues as well as providing fallbacks to more traditional > syntax. It is more feature-complete than the status quo, and pretty much > has no rival when it comes to constructing non-trivial queries. > >You''re not aware of Sequel, I guess... Or you''re only comparing alternative plugins to current AR implementation, right? -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Core" group. To post to this group, send email to rubyonrails-core@googlegroups.com. To unsubscribe from this group, send email to rubyonrails-core+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-core?hl=en.