rzane / baby_squeel Goto Github PK

View Code? Open in Web Editor NEW

500.0 10.0 50.0 400 KB

:pig: An expressive query DSL for Active Record

License: MIT License

Ruby 99.88% Shell 0.12%

squeel arel query dsl activerecord sql orm

baby_squeel's People

Contributors

Stargazers

Watchers

baby_squeel's Issues

[Question] Join a subquery

First of all, that's an awesome gem and I'm super happy I found it 🔥 🔥 🔥

The "parent" gem (squeel) has an ability of joining a subquery, example from their readme:

subquery = OrderItem.group(:orderable_id).select { [orderable_id, sum(quantity * unit_price).as(amount)] }
Seat.joins { [payment.outer, subquery.as('seat_order_items').on { id == seat_order_items.orderable_id}.outer] }.
              select { [seat_order_items.amount, "seats.*"] }
# => SELECT "seat_order_items"."amount", seats.*
#    FROM "seats"
#    LEFT OUTER JOIN "payments" ON "payments"."id" = "seats"."payment_id"
#    LEFT OUTER JOIN (
#      SELECT "order_items"."orderable_id",
#             sum("order_items"."quantity" * "order_items"."unit_price") AS amount
#      FROM "order_items"
#      GROUP BY "order_items"."orderable_id"
#    ) seat_order_items ON "seats"."id" = "seat_order_items"."orderable_id"

I wonder if there's an ability to achieve the same thing with baby squeel?

empty hash in where.has causes bad where clause

Issue

When using a hash of filters from a where.has block, everything works fine unless that hash happens to be empty. In the case of an empty hash, it's adding a where 1=0 clause to the generated SQL query.

Reproduction

With squeel (working):

>> filter = {}
>> Device.where { filter }.to_sql
=> "SELECT `devices`.* FROM `devices` "

With baby_squeel (not working):

 >> filter = {}
 >> Device.where.has { filter }.to_sql
 => "SELECT `devices`.* FROM `devices` WHERE (1=0)"

Gem breaking with Rails 5.2.1

I have a join query that started breaking after upgrading to Rails 5.2.1

joins(:order).where.has { order.customer_id == customer.id }

Reordering without explicit direction doesn't work with last in Rails 4

This might be an arel bug rather than a baby_squeel bug, apologies if so!

Issue

When using Active Record 4,

Foo.reorder(:x).last

appears to not be equivalent to

Foo.reordering { x }.last

but it is with Active Record 5.

Specifically, it appears that we don't get a DESC in the resulting query in AR 4:

Foo Load (0.1ms)  SELECT  "foos".* FROM "foos"  ORDER BY "foos"."x" LIMIT 1

whereas we do in AR 5:

 Foo Load (0.1ms)  SELECT  "foos".* FROM "foos" ORDER BY "foos"."x" DESC LIMIT ?  [["LIMIT", 1]]

N.B. adding an explicit direction does work in both AR versions:

Foo.reorder('x ASC').last == Foo.reordering { x.asc }.last

Reproduction

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'activerecord', '= 4.2.10' # breaks
  # gem 'activerecord', '= 5.1.4' # works
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

require 'active_record'
require 'minitest/autorun'
require 'logger'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :foos do |t|
    t.integer :x
  end
end

class Foo < ActiveRecord::Base
end

ActiveRecord::Base.logger = Logger.new(STDOUT)

class BabySqueelTest < Minitest::Spec
  it 'works' do
    one = Foo.create(x: 1)
    two = Foo.create(x: 2)

    puts "reorder"
    assert_equal one, Foo.reorder('x ASC').first
    assert_equal one, Foo.reorder('x DESC').last
    assert_equal one, Foo.reorder('x').first
    assert_equal two, Foo.reorder('x ASC').last
    assert_equal two, Foo.reorder('x DESC').first
    assert_equal two, Foo.reorder('x').last

    puts "reordering"
    assert_equal one, Foo.reordering { x.asc }.first
    assert_equal one, Foo.reordering { x.desc }.last
    assert_equal one, Foo.reordering { x }.first
    assert_equal two, Foo.reordering { x.asc }.last
    assert_equal two, Foo.reordering { x.desc }.first
    assert_equal two, Foo.reordering { x }.last # Doesn't work with AR 4
  end
end

baby_squeel demands fully qualified path

Squeel was a bit more layed back when specifying a where condition. Take the following:

Owner.joins{:dogs}.where{ breed = 'mutt' }

Squeel is ok with this since breed is only on the dogs table but baby_squeel demands that the query looks like:

Owner.joins{:dogs}.where{ dogs.breed = 'mutt' }

Not that baby_squeel is incorrect, just makes changing over to baby_squeel a bit more difficult.

problems with where.has on associations

With squeel I am able to say user.posts.where {posted == 1}

With baby_squeel it looks like I have to say User.where.has {|p| (p.posted == 1) & (p.user_id = user.id) }

Is there any other way to do a query using an association relationship?

Thank you!

[Error] When using `joining` with `on`, DSL refers the association to wrong table name or undefined table alias

Issue

Course.joining{ enrollments.on(id == enrollments.course_id) }.where.has{ enrollments.progress == 100 }

will produce the following query:

SELECT "courses".*
FROM "courses"
    INNER JOIN "enrollments" ON "courses"."id" = "enrollments"."course_id"
WHERE "enrollments_courses"."progress" = 100.0 // <-- Error here

Notice the WHERE condition, it's enrollments_courses instead of enrollments, or the table alias enrollments_courses is not defined.

On the other hand, this code generates query correctly:

Course.joining{ enrollments.on(id == enrollments.course_id) }.where(Enrollment.arel_table[:progress].eq(100.0))

The expected query is:

SELECT "courses".*
FROM "courses"
    INNER JOIN "enrollments" ON "courses"."id" = "enrollments"."course_id"
WHERE "enrollments"."progress" = 100.0

I'm using Rails version 4.2.7.1, Ruby version 2.3.3, Baby Squeel version 1.1.4.

Grouping function

In squeel you have the _ function in the DSL, which lets you convert certain native objects to a usable object in squeel. It also adds brackets around the item in the SQL.

Hard to explain, here's examples:

# Subquery in SELECT
User.select{_(Post.where{user_id == users.id}.select{COUNT(id)}).as('post_count')}
# => SELECT (SELECT COUNT(posts.id) FROM posts WHERE posts.user_id = users.id) AS post_count FROM users

# Array IN (subquery)
User.where{_([x, y]).in(Post.select{[x, y]})}
# => SELECT users.* FROM users WHERE (users.x, users.y) IN (SELECT posts.x, posts.y FROM posts)

As you can see, this is useful in certain occasions, and I cannot find a way to work around it.

You can find squeel's implementation here: https://github.com/activerecord-hackery/squeel/blob/master/lib/squeel/dsl.rb#L79

Arel Deprecation Warning with Rails 5.2

Issue

I am encountering Arel Deprecation warning with Rails 5.2. and baby_squeel 1.2.1. when using #plucking method with multiple attributes to fetch. The message is a follows:

DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): [#<struct Arel::Attributes::Attribute relation=#<Arel::Table:0x000055f549e86428 @name="teams", @type_caster=#<ActiveRecord::TypeCaster::Map:0x000055f549e86630 @types=Team(id: integer, identifier: string, phone: string, email: string, contact: string, user_comment: string, old_id: integer, mobile: string, ticket_email: string, ticket_queue: string, assigned_location: string, description: text, active: boolean, assign_permission: integer, allowed_non_methadon_team: boolean)>, @table_alias=nil>, name="id">]. Non-attribute arguments will be disallowed in Rails 6.0. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql().

I tried to figure out where exactly this happens, but I got stuck somewher in the DSL...

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 5.2.0' # which Active Record version?
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :dogs, force: true do |t|
    t.string :name
  end
end

class Dog < ActiveRecord::Base
end

class BabySqueelTest < Minitest::Spec
  it 'works without warning' do
    Dog.all.plucking { id }
  end

  it 'prints out deprecation warning' do
    Dog.all.plucking { [id] }
  end

end

Where.has uses incorrect table alias

When using joining to join a table multiple times, baby_squeel doesn't use the correct table aliases if they're being referenced using where.has.

For example, in this test case, I have villages, people and cars. People can all have a father. I want to find all people in the village who either own a blue car, or whose father owns a blue car.

This is what I believe should work, but doesn't.

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 5.0.0' # which Active Record version?
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :villages do |t|
  end

  create_table :people do |t|
    t.integer :village_id
    t.integer :father_id
  end

  create_table :cars do |t|
    t.integer :owner_id
    t.string :color
  end
end

class Village < ActiveRecord::Base
  has_many :people
end

class Person < ActiveRecord::Base
  belongs_to :village
  belongs_to :father, class_name: Person
  has_one :car
end

class Car < ActiveRecord::Base
  belongs_to :person
end


class BabySqueelTest < Minitest::Spec
  it 'works' do
    # Find all people in a village who either have a blue car, or whose fathers have a blue car
    scope = Village.create.people.joining { [
      car.outer,
      father.outer.car.outer
    ]}.where.has {
      (car.color == 'blue') |
      (father.car.color == 'blue')
    }

    scope.to_sql.must_equal %{
      SELECT "people".* FROM "people" LEFT OUTER JOIN "cars" ON "cars"."person_id" = "people"."id" LEFT OUTER JOIN "people" "fathers_people" ON "fathers_people"."id" = "people"."father_id" LEFT OUTER JOIN "cars" "cars_people" ON "cars_people"."person_id" = "fathers_people"."id" WHERE "people"."village_id" = 1 AND ("cars"."color" = 'blue' OR "cars_people"."color" = 'blue')
    }.squish
  end
end

OP doesn't work on where.has

Issue

I need to use op function on my where clause like below:

Reproduction

Profile.where.has { |t| t.first_name.op('||', ' ').op('||', t.last_name).like('Annie Moore') }.explain
RuntimeError: unsupported: String
from /Users/rapide/.rvm/gems/ruby-2.3.0@bttf/gems/arel-6.0.4/lib/arel/visitors/to_sql.rb:736:in `unsupported'

Is it possible to add that functionality?

Improper casting when joined tables share common column name but different column type

Issue

If you have two tables with a common column name, but different column types, which you're joining via an association, baby_squeel improperly casts the column based on the column type of the primary table instead of the joining table. We setup two specs demonstrating the issue on our fork: https://github.com/boltthreads/baby_squeel

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 5.0.0' # which Active Record version?
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :stations, force: true do |t|
    t.float :frequency
  end

  create_table :shows, force: true do |t|
    t.string :frequency
    t.belongs_to :station
  end
end

class Station < ActiveRecord::Base
  has_many :shows
end

class Show < ActiveRecord::Base
  belongs_to :station
end

class BabySqueelTest < Minitest::Spec

  describe "joining tables with matching attributes"  do
    describe "where the parent table column_type is float and the child table column_type is string" do
      describe "wheres based on the child model's attribute" do
        it 'uses the column_type of the child' do
          relation = Station.joins(:shows).where.has { shows.frequency == 'daily' }

          relation.to_sql.must_equal %{
            SELECT "stations".* FROM "stations"
            INNER JOIN "shows" ON "shows"."station_id" = "stations"."id"
            WHERE "shows"."frequency" = "daily"
          }.squish
        end
      end

      describe "wheres based on the parent model's attribute" do
        it 'uses the column_type of the parent' do
          relation = Show.joins(:station).where.has { station.frequency == 4.2 }

          relation.to_sql.must_equal %{
            SELECT "shows".* FROM "shows"
            INNER JOIN "stations" ON "stations"."id" = "shows"."station_id"
            WHERE "stations"."frequency" = 4.2
          }.squish
        end
      end
    end
  end

end

Nulls last

Post.ordering { title.asc.nulls_last }

Joining with scopes

Issue

I cannot join with scopes. As far as I know I am able to do so with Squeel.

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 4.2.7.1' # which Active Record version?
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :dogs, force: true do |t|
    t.integer :weight
  end
end

class Dog < ActiveRecord::Base
  scope :double_weight, -> { select(:id).select('2 * weight AS double_weight') }
end

class BabySqueelTest < Minitest::Spec
  it 'works' do
    scope = Dog.joining { Dog.double_weight.as(double_weight).on { id = double_weight.id } }

    scope.to_sql.must_equal %{
      SELECT "dogs".* FROM "dogs" INNER JOIN (SELECT "dogs"."id", 2 * weight as double_weight FROM "dogs") double_weight ON "dogs"."id" = "double_weight"."id"
    }.squish
  end
end

This returns:
BabySqueel::NotFoundError: There is no column or association named 'double_weight' for Dog.

If I use Squeel instead:

rails _4.2.7.1_ new try
cd try
echo "gem 'squeel'" >> Gemfile
bundle
bin/rails g model Dog weight:integer
bin/rake db:migrate

Then add scope :double_weight, -> { select(:id).select('(2 * weight) AS double_weight') } into app/models/dog.rb.

Afterwards, run bin/rails c. Running
Dog.joins { Dog.double_weight.as(double_weight).on { id = double_weight.id } } works.

Attribute #in returning an actual Arel::Attributes::Attribute

Issue

There is error when subquery is empty.

Reproduction

[4] pry(main)> User.where.has { id.in(User.none.select(:id)) | id.eq(5) }
NoMethodError: undefined method `|' for #<Arel::Nodes::In:0x007f99635f5fb0>
from (pry):6:in `block in <main>'

[2] pry(main)> User.where.has { id.in(User.none.select(:id)) }
  User Load (2.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN ()
=> #<User::ActiveRecord_Relation:0x3fccb1f90084>

Better support for EXISTS subqueries

Hello! In Squeel, I used to be able to do this.

Customer.where{NOT EXISTS PageAction.where{customer_id == customers.id}}

Now I've had to change it to this.

Customer.where.has{NOT EXISTS PageAction.where.has{customer_id == Customer.arel_table[:id]}.arel.ast}

The arel_table bit probably only works thanks to a patch I've made against ActiveRecord though I haven't tried without. The Squeel syntax no longer works because Baby Squeel complains that customers is not a valid column or association. I wonder if there's a way for Baby Squeel to realise this is a subquery. Probably not but worth considering!

The arel.ast bit was not necessary in Squeel thanks to activerecord-hackery/squeel@1ce3601. I'm not sure if it's possible to do this in Baby Squeel due to its non-invasive approach but it would be nice. Note that arel is sufficient for finds but arel.ast is needed for counts.

P.S. I had a fix for bind values in joins but by the time I hit the Pull Request button, you'd already pushed a fix to master! 😆

Exception on search through has_and_belongs_to_many relationship

Issue

When searching through a has_and_belongs_to_many relationship, it raises a noMethodError, undefined method 'table' for nil:NilClass. Passes this test in v1.01, but not v1.02

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 5.0.0' # which Active Record version?
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :authors, force: true do |t|
    t.string :name
    t.boolean :ugly
    t.timestamps null: false
  end

  create_table :research_papers, force: true do |t|
    t.string :title
  end

  create_table :author_research_papers, force: true do |t|
    t.integer :author_id
    t.integer :research_paper_id
  end

end

class Author < ActiveRecord::Base
end

class ResearchPaper < ActiveRecord::Base
  has_and_belongs_to_many :authors, join_table: :author_research_papers
end

class BabySqueelTest < Minitest::Spec
    it 'wheres on a has_and_belongs_to_many association' do
      relation = ResearchPaper.joins(:authors)
      .where.has { authors.name == "Alex" }

      expect(relation).to produce_sql(<<-EOSQL)
        SELECT "research_papers".* FROM "research_papers"
        INNER JOIN "author_research_papers" ON "author_research_papers"."research_paper_id" = "research_papers"."id"
        INNER JOIN "authors" ON "authors"."id" = "author_research_papers"."author_id"
        WHERE "authors"."name" = 'Alex'
      EOSQL
    end
end

support for my{some_method}

Trying to drop in baby_squeel as a replacement for squeel to a large rails project but we make extensive use of the my{some_method} where some_method is a method in the class where we are building the query. Is there an equivalent for that within baby_squeel?

thx!

Unable to perform math operations on the result of a function

Issue

I'd like to be able to write something like Dog.selecting { (coalesce(id, 0) / 1).as('value') }, but it appears I cannot use / on a Function:

NoMethodError: undefined method `/' for #<BabySqueel::Nodes::Function:0x007fe670ca9a28>
    ~/.rvm/gems/ruby-2.3.1/bundler/gems/baby_squeel-4b2017075776/lib/baby_squeel/nodes/proxy.rb:31:in `method_missing'

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 4.2.5.1'
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :dogs, force: true do |t|
    t.string :name
  end
end

class Dog < ActiveRecord::Base
end

class BabySqueelTest < Minitest::Spec
  it 'works' do
    scope = Dog.selecting { (coalesce(id, 0) / 1).as('value') }

    scope.to_sql.must_equal %{
      SELECT coalesce("dogs"."id", 0) / 1 AS value FROM "dogs"
    }.squish
  end
end

[Question] SubQueries capabilities

Issue

Is it possible to use baby_squeel to create subqueries similar to the ones we have on Squeel?

I cannot find any documentation on this and we are trying to move from Squeel to Baby Squeel due to several bugs related to polymorphic relations and bind values on subqueries

Thanks

Reproduction

# Query 1
            notes_relation = Person.first.notes
            klazz_name = "Person"
            scope = Person.joins { notes_relation.as("notes").on { (~id == notes.notable_id) & (notes.notable_type == klazz_name) }.outer }.where { notes.note != nil }
# Query 2
subquery = OrderItem.group(:orderable_id).select { [orderable_id, sum(quantity * unit_price).as(amount)] }
Seat.joins { [payment.outer, subquery.as('seat_order_items').on { id == seat_order_items.orderable_id}.outer] }.
              select { [seat_order_items.amount, "seats.*"] }

Arel converts SQL literal to 0 when compared with a numeric column

Issue

Arel doesn't seem to like having baby squeel's SQL literal proxy on the right side of an operator when the left side is a numeric column (grouping the literal with the new grouping method fixes it, one of the reasons I wanted it here is because it 'fixed' a lot of similar issues on the original Squeel).

Sometimes infras case classes, which proxies don't handle well (case X when Arel::Nodes::SqlLiteral then ...), and it seems like it uses to_i of the literal because it translates to '0' on the SQL.

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 5.0.0' # which Active Record version?
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :dogs, force: true do |t|
  end
end

class Dog < ActiveRecord::Base
end

class BabySqueelTest < Minitest::Spec
  it 'works' do
    scope = Dog.where.has { id == sql('Fido') }

    scope.to_sql.must_equal %{
      SELECT `dogs`.* FROM `dogs` WHERE `dogs`.`id` = Fido
    }.squish
  end
end

[Error] Cannot use `joining` with `alias` on PostgreSQL

Issue

I followed the joining with alias example

Course.joining{ enrollments.alias('e').on(id == enrollments.course_id) }

and received the following error:

  Course Load (2.4ms)  SELECT "courses".* FROM "courses" INNER JOIN "enrollments" "e" ON "courses"."id" = "enrollments"."course_id"
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  invalid reference to FROM-clause entry for table "enrollments"
LINE 1: ... INNER JOIN "enrollments" "e" ON "courses"."id" = "enrollmen...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "e".
: SELECT "courses".* FROM "courses" INNER JOIN "enrollments" "e" ON "courses"."id" = "enrollments"."course_id"
from /var/lib/gems/2.3.0/gems/rack-mini-profiler-0.10.2/lib/patches/db/pg.rb:90:in `exec'

It looks like PostgreSQL requires that column of table inON condition must refer to that table alias if it's defined.

Multiple on conditions

I need to write something like this:

Assignment.joining{student_assignments.outer.on(student_assignments.assignment_id == id).
  on(student && student_assignments.student_id == student.id)}

where the first on is the normal association condition, and the second on is an optional condition, which should be added only when local variable student is defined.

It almost works but not quite. If student is nil then the first on condition is generated as expected. If student is defined, then the second on condition replaces the first one rather than being added to it. I tried to write both conditions in a single on like:

Assignment.joining{student_assignments.outer.on((student_assignments.assignment_id == id) & 
  (student && student_assignments.student_id == student.id))}

and then it works fine when student is defined, but I get an error about an 'Unsupported argument type: NilClass' when student is nil (not surprisingly). Is there a way to make it work? Thanks.

Reference parent columns from subquery

Issue

Is there a way to reference tables from outer scopes inside subquery?

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 5.0.0' # which Active Record version?
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :accounts, force: true do |t|
    t.string :name
  end
  create_table :songs, force: true do |t|
    t.string :name
  end
  create_table :account_songs, force: true do |t|
    t.integer :account_id
    t.integer :song_id
  end
  create_table :playlists, force: true do |t|
    t.integer :account_id
    t.integer :song_id
  end
end

class Song < ActiveRecord::Base
  has_many :account_songs, :dependent => :destroy, :inverse_of => :song
  has_many :accounts, :through => :account_songs
  has_many :playlists, :dependent => :nullify
end

class Account < ActiveRecord::Base
  has_many :account_songs, :dependent => :destroy, :inverse_of => :account
  has_many :songs, :through => :account_songs
  has_many :playlists, :dependent => :nullify
end

class AccountSong < ActiveRecord::Base
  belongs_to :account, :inverse_of => :account_songs
  belongs_to :song, :inverse_of => :account_songs
end

class Playlist < ActiveRecord::Base
  belongs_to :account
  belongs_to :song
end

class BabySqueelTest < Minitest::Spec
  it 'works' do
    # global scope
    scope = AccountSong.all
    # filters defined down the road, in filtering classes, in this case: was this song already played:
    # `account_songs` here is coming from the global scope
    subquery = Playlist.where.has { |t| (t.account_id = t.account_songs.account_id) & (t.song_id == t.account_songs.song_id) }
    scope = scope.where.has { exists(subquery) }

    # result after applying all filters
    scope.to_sql.must_equal %{
      SELECT "account_songs".* FROM "account_songs"
      WHERE EXISTS (
        SELECT "playlists".* FROM "playlists"
        WHERE "playlists"."account_id" = "account_songs"."account_id"
          AND "playlists"."song_id" = "account_songs"."song_id"
      )
    }.squish
  end
end

Here, account_songs is undefined when creating subquery.

EDIT: Fixed singular table names to be plural again.

Ordering function cannot be used with 'as'

I'm migrating from squeel to baby-squeel and found a case which worked fine in squeel but not in baby-squeel. selecting an virtual column with as syntax cannot be used for ordering.

Example:

SomeTable.selecting { ['*', (some_date < 8.days.ago).as('long_standing')] }.ordering { [title.desc, long_standing.desc] }

This works in squeel:

SomeTable.select { ['*', (some_date < 8.days.ago).as(long_standing)] }.order { [title.desc, `long_standing`.desc] }

This produces:

There is no column or association named 'long_standing' for SomeTable

Would be great to have this feature :-)

Intelligent Update queries

It would be great to have the DSL available in UPDATE queries, so I can assign SQL values to fields.

Something like:

# Inherit all venue's cities and countries from their owners
Venue.joining { owner }.updating { { city: owner.city, country: owner.country } }

# or (less "nice" alternative IMO)
Venue.joining { owner }.updating { [city(owner.city), country(owner.country) }

Query is not consistent -- sometimes get an error if I change the parameter

Issue

Please explain this issue you're encountering to the best of your ability.

Reproduction

[47] pry(#<BotResponse>)> e = Event.where.has {(keyword == 'daily_check') & (recurring == 'true') & (time_of_day == '8am')}
=> [#<Event:0x00000003f3eca8
  id: 15,
  bot_client_id: "aiaas-1409611358153-user-0147",
  keyword: "daily_check",
  topic: nil,
  status: "active",
  channel: "telegram",
  created_date: 2017-05-08 00:24:32 UTC,
  tickle_expression: "daily",
  time_of_day: "8am",
  next_occurrence: 2017-05-14 15:00:00 UTC,
  time_zone: nil,
  recurring: true>]
[48] pry(#<BotResponse>)> e.count
=> 1

[49] pry(#<BotResponse>)> e = Event.where.has {(bot_client_id == 'daily_check') & (recurring == 'true') & (time_of_day == '8am')}
=> #<Event::ActiveRecord_Relation:0x1ec6034>
[50] pry(#<BotResponse>)> e.count
RuntimeError: unsupported: FalseClass
from /usr/local/rvm/gems/ruby-2.2.1/gems/arel-6.0.4/lib/arel/visitors/to_sql.rb:736:in `unsupported'

I'm not sure why there should be a difference:

ActiveRecord::Schema.define do
	  unless ActiveRecord::Base.connection.tables.include? 'events'
	    create_table :events do |table|

	    	table.column :bot_client_id, 			:string

	    	table.column :keyword, 					:string
	    	table.column :topic, 					:string
	    	table.column :status, 					:string
	    	table.column :channel, 					:string

	    	table.column :created_date,             :datetime
	      	table.column :tickle_expression,        :string
	      	table.column :time_of_day,              :string
	      	table.column :next_occurrence,			:datetime
	      	table.column :time_zone,				:string
	      	table.column :recurring,				:boolean
	    end
	  end

	end

Implement `selecting { table.* }`

Post.joins(:author).selecting { [post.id, author.*] }

Ignoring multiple joins

Squeel allowed for stuff like this:

scope = scope.joins{dog}.where{dog.name = my{name}} if name.present?
scope = scope.joins{dog}.where{dog.breed = my{breed}} if breed.present?

In the above example, if name and breed is present, dog will be joined twice. Squeel just ignores the second join of dog while baby_squeel raises an error.

Accessing the model table on associations

I have a model called Asset, which has an association that was expressed in squeel like:

has_many :using_problems,
    -> {where{assets.asset_locator =~ concat('%', ~problem_file)}},
    source: :problems

When I try something similar in baby_squeel, it complains:

BabySqueel::NotFoundError (There is no function or column or association named 'assets' for Problem.):

Is there any other way I can reference the model table in an association in baby_squeel? Thanks.

Support `.nil?` in DSL

Right now, in order to check if something IS NULL, I have to use field == nil in the DSL. Wouldn't it be more ruby-esque to be able to say field.nil? instead?

Rails 5.2 support

It looks like baby_squeel isn't working with joins in rails 5.2.0.beta2.

Where.has for polymorphic fields

Issue

Where.has does not work as smart as Arel with polymorphic fields. I've build a simplified case to demonstrate this. It seems something is simplified before it gets transported to Arel, as Arel itself does support these cases. For a workaround I use Arel directly, but the BabySqueel code is soo much nicer :)

I tried to create changes in BabySqueel to support it, but I did not succeed to find the place to intercept this behaviour.

Notice: Arel also does understand the case where the polymorphic relation test is a relation itself, like Dropping.where(animal: dogs). dogs is a relationship.

With some pointers how to solve it I can also contribute to this gem and create a pull request.

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 5.0.0' # which Active Record version?
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :dogs, force: true do |t|
    t.string :name
  end

  create_table :droppings, force: true do |t|
    t.string :animal_type
    t.integer :animal_id
  end
end

class Dog < ActiveRecord::Base
end

class Dropping < ActiveRecord::Base
  belongs_to :animal, polymorphic: true
end

class BabySqueelTest < Minitest::Spec
  it 'works' do
    localtext = 'Fido'
    scope = Dog.where.has { name == localtext }

    scope.to_sql.must_equal %{
      SELECT "dogs".* FROM "dogs" WHERE "dogs"."name" = 'Fido'
    }.squish
  end

  it 'works with Arel' do
    dog = Dog.create(name: 'Fido')

    scope = Dropping.where(animal: dog)

    scope.to_sql.must_equal %{
      SELECT "droppings".* FROM "droppings" WHERE "droppings"."animal_type" = 'Dog' AND "droppings"."animal_id" = #{dog.id}
    }.squish
  end

  it 'works for babysqueel polymorphic' do
    dog = Dog.create(name: 'Fido')

    scope = Dropping.where.has{animal == dog}

    scope.to_sql.must_equal %{
      SELECT "droppings".* FROM "droppings" WHERE "droppings"."animal_type" = 'Dog' AND "droppings"."animal_id" = #{dog.id}
    }.squish
  end
end

output

BabySqueelTest#test_0003_works for babysqueel polymorphic:
ArgumentError: Unsupported argument type: false (FalseClass)
    /Users/hlogmans/.rvm/gems/ruby-2.3.0/gems/activerecord-5.0.1/lib/active_record/relation/where_clause_factory.rb:27:in `build'
    /Users/hlogmans/.rvm/gems/ruby-2.3.0/gems/activerecord-5.0.1/lib/active_record/relation/query_methods.rb:632:in `where!'
    /Users/hlogmans/.rvm/gems/ruby-2.3.0/bundler/gems/baby_squeel-31e364f6644d/lib/baby_squeel/active_record/where_chain.rb:9:in `has'
    test.rb:55:in `block in <class:BabySqueelTest>'

Empty selecting doesn't combine with other selecting

For any model I try, a query like:

Package.selecting{}.selecting{name}.first

Package.selecting{nil}.selecting{name}.first

fails with an error:

Arel::Visitors::UnsupportedVisitError: Unsupported argument type: NilClass. Construct an Arel node instead.

This happens when the tests in the first selecting are conditioned by an expression that happens to be false. I don't see how I can construct an Arel node in this case. Each of the two selecting works fine by itself. Similar expressions work fine with other constructs like where.has and joining, and select also works fine in squeel. So I'm hoping it's just a bug. Thanks.

Sifters don't work on on-the-fly tables

Issue

BabySqueel[:table] returns an object that does not respond to sift.

Now that I'm writing this, I realize it makes some sense, BabySqueel doesn't know anything about which model belongs to a table. However, would it be possible to add a model's sifter to a BabySqueel-initiated table object?

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 5.0.0' # which Active Record version?
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :dogs, force: true do |t|
    t.string :name
  end

  create_table :people, force: true do |t|
    t.string :name
    t.integer :age
    t.string :dog_name
  end
end

class Dog < ActiveRecord::Base
end

class Person < ActiveRecord::Base
  sifter(:female) { name.in? %w(angela brittany carol) }
end

class BabySqueelTest < Minitest::Spec
  it 'does not raise an exception' do
    people = BabySqueel[:people]
    # Find all dogs that have a female adult boss
    scope = Dog.joining { people.on(people.dog_name == name) }.where.has { people.sift(:female) & (people.age >= 18) }

	# FIXME: This SQL may be off, the point is whether or not it raises an exception. I'm not an expert with minitest.
    scope.to_sql.must_equal %{
      SELECT "dogs".* FROM "dogs"
        INNER JOIN "people" ON "people"."dog_name" = "dogs"."name"
        WHERE "people"."name" IN ('angela', 'brittany', 'carol')
          AND "people"."age" >= 18;
    }.squish
  end
end

Help converting a function from non-baby squeel

I'm tired of battling squeel and was happy to find this! But I'm completely stumped on converting this function into baby_squeel.

In my model, I have this function (it uses some postgis stuff from the rgeo gem)

# latlon is a column on this model and addressable is something I'm going to pass into it
def self.contains(addressable)
  where{st_intersects(:latlon, addressable)}
end

I was hoping to just change where to where.has but that didn't seem to work. Thanks in advance for any help on this!

I'm running rails 4.2.7

Request for "all" in where.has { ... }

Issue

Request feature that we can use all and none in where.has query

example:

Author.where.has { |a| some_condition ? (a.id == x) : all }
#=> if "some_condition" is true, query a.id equal x, or return all authors

Post.where.has { |po| conds = none
  conds |= (po.id == x) if some_condition
  conds |= (po.author_id) if some_filter
  conds &= none if some_bad_things
  conds
}
#=> more complicated, but this is useful when building complicate query

I know Rails 5 has powerful .or query, but if we can use baby_squeel to build it, that will be awesome
(And Rails 4 doesn't has .or 😢 )

Jsonb Selectors

@rzane what do you think about supporting jsonb selectors?
https://www.postgresql.org/docs/9.4/static/functions-json.html

Some ideias:

select data->>'completed_at' ...

which translates to

scope.selecting { data.completed_at }

Related discussions:
rails/arel#288

Nested joins

Hi,

I'm trying to migrate from Squeel using a query that looks like this:

Foo.joining { bar.things }.
  where.has { bar.things.id.in [1, 2] }.
  joining { bar }.where.has { bar.baz == true }

Somehow, Baby Squeel generates a query where the baz column is on the things table, rather than on the bars table.

Migration from the Squeel Community

I am here after going through this thread: activerecord-hackery/squeel#412

I'm sure I'm not the only one maintaining an app that relied on Squeel for a long time and is now trying to upgrade to Rails 5, seeing Baby Squeel as the only option going forward. Would it be possible to get a chunk in the README to help us flag up what changes we will need to make to an application to switch from Squeel to Baby Squeel integration?

Happy to have a dialogue here on this, hope my issue makes sense. Thanks!

Join by adding conditions instead of replacing them

Feature request: In an ORM I used to use in my former life, there was an alternative to .on for joins that was called .with. Basically, what it did was take the original join conditions, and add another condition to it using AND. I found this to be extremely helpful, as part of the reasons why you use an ORM is that you don't have to bother with join conditions, and most of the time when you want to override them, it's because you want to restrict them by adding something to the original conditions.

Basically, it works like this:

# Implicitly follows schema
A.joining { b }
# SELECT * FROM a INNER JOIN b ON b.a_id = a.id

# Explicitly, it'd look like this:
A.joining { b.on(b.a_id == id) }
# SELECT * FROM a INNER JOIN b ON b.a_id = a.id

# Now, adding a condition
A.joining { b.on((b.a_id == id) & (b.active == true)) }
# SELECT * FROM a INNER JOIN b ON b.a_id = a.id AND b.active == true

# My suggestion:
A.joining { b.with(b.active == true) }
# SELECT * FROM a INNER JOIN b ON b.a_id = a.id AND b.active == true

Especially if your models are named MerchantIdentityOperatorExtension instead of A, this can save you a lot of code that would be the same in > 90% of the usages of .on

Aliases don't work in 'on' and 'select' conditions

You have an example showing use of an alias in a join:

Post.joining { author.alias('a').on((author.id == author_id) | (author.name == title)) }

but an alias is useful if you can use it in conditions, like:

Post.joining { author.alias('a').on((a.id == author_id) | (a.name == title)) }

Does this work for you? I'm trying something similar and I'm getting an error about no column a. I also tried something like:

Post.joining { author.alias('a').on((sql('a').id == author_id) | (sql('a').name == title)) }

but that doesn't work either, it complains about undefined method for id. Is there a way to make this work? Thanks.

Converting having

Not sure how to convert this clause from squeel:

having{`completion`.op(compl_string == 'complete' ? '=' : '<', 1.0)}

where 'compl_string is a local variable. Just replacing having with when_having and ticks with quotes doesn't work. Thanks.

Missing constructs

I'm trying to translate from squeel, I couldn't find the equivalent of a few constructs: reorder, preload, includes, eager_load. Have they been implemented? Thanks.

Unable to use floating point values in math expressions

Issue

I'd like to be able to write something like Dog.selecting { (coalesce(id, 0) / 42.42).as('value') }, but it appears I cannot use floating point numbers:

RuntimeError: unsupported: Float
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:735:in `unsupported'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/reduce.rb:13:in `visit'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:756:in `visit_Arel_Nodes_InfixOperation'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/reduce.rb:13:in `visit'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:699:in `visit_Arel_Nodes_As'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/reduce.rb:13:in `visit'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:241:in `block in visit_Arel_Nodes_SelectCore'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:240:in `each'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:240:in `each_with_index'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:240:in `visit_Arel_Nodes_SelectCore'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:210:in `block in visit_Arel_Nodes_SelectStatement'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:209:in `each'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:209:in `inject'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/to_sql.rb:209:in `visit_Arel_Nodes_SelectStatement'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/sqlite.rb:13:in `visit_Arel_Nodes_SelectStatement'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/reduce.rb:13:in `visit'
    ~/.rvm/gems/ruby-2.3.1/gems/arel-6.0.3/lib/arel/visitors/reduce.rb:7:in `accept'
    ~/.rvm/gems/ruby-2.3.1/gems/activerecord-4.2.5.2/lib/active_record/relation.rb:551:in `to_sql'

I note that the stacktrace doesn't mention baby_squeel at all, so it's possible that this issue is outside of the scope of this library?

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord', '~> 4.2.5.1'
  gem 'sqlite3'
  gem 'baby_squeel', github: 'rzane/baby_squeel'
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :dogs, force: true do |t|
    t.string :name
  end
end

class Dog < ActiveRecord::Base
end

class BabySqueelTest < Minitest::Spec
  it 'works' do
    scope = Dog.selecting { (coalesce(id, 0) / 42.42).as('value') }
  end
end

'as' keyword is not working anymore in some cases since release 1.0

Issue

There is a regression in version 1.0 with as in some cases (see reproduction part). Thanks for taking a look into this.

Reproduction

require 'bundler/inline'
require 'minitest/spec'
require 'minitest/autorun'

gemfile true do
  source 'https://rubygems.org'
  gem 'activerecord'
  gem 'sqlite3'
  gem 'baby_squeel', '>= 1' # '< 1' works
end

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :users, force: true do |t|
    t.string :name
    t.timestamps null: false
  end
end

class User < ActiveRecord::Base
end

class BabySqueelTest < Minitest::Spec
  it 'works' do
    User.selecting { (created_at + 1.day.ago).as('some_val') } # works
    User.selecting { (created_at == 1.day.ago).as('some_val') } # don't work with 1.x, works with 0.3.1
    User.selecting { (created_at < 1.day.ago).as('some_val') } # don't work with 1.x, works with 0.3.1
  end
end

subquery not_in returns NULL

Issue

When I use "not_in" the sql result is NULL. But if I use only "in", the result works fine

Reproduction

table1.joins(:table2)
.where.has{  id.in table3.select(:table1_id).where(table3_id: 162)    }

Result

SELECT "table1".* FROM "table1" INNER JOIN "table2" ON "table2"."table1_id" = "table1"."id"
WHERE  "table1"."id" IN (SELECT "table3"."table1_id" FROM "table3" WHERE  "table3"."table3_id" = 162)

But when I use not_in

table1.joins(:table2)
.where.has{  id.not_in table3.select(:table1_id).where(table3_id: 162)    }

Result

SELECT "table1".* FROM "table1" INNER JOIN "table2" ON "table2"."table1_id" = "table1"."id"
WHERE "table1"."id" NOT IN (NULL)

Migration from squeel

# Using squeel
City.where { state_id.in(State.all) }.to_sql
=> "SELECT `cities`.* FROM `cities`  WHERE `cities`.`state_id` IN (SELECT `states`.`id` FROM `states`)"

# Using baby_squeel
City.where { state_id.in(State.all) }.to_sql
=> "SELECT `cities`.* FROM `cities` WHERE `cities`.`state_id` IN (SELECT `states`.* FROM `states`)"

I have this kind of problem spread throughout my application, i am migrating and also using the compatibility mode .

Is there a way to get this same behavior back?

Using BETWEEN condition?

Is there anyway to write BETWEEN condition?

Support Arel.star

use case

Model.selecting { star & association.data == 'blah' }

See here Arel.star

rzane / baby_squeel Goto Github PK

baby_squeel's People

Contributors

Stargazers

Watchers

Forkers

baby_squeel's Issues

Issue

Reproduction

Issue

Reproduction

Issue

Issue

Reproduction

Issue

Reproduction

Issue

Reproduction

Issue

Reproduction

Issue

Reproduction

Issue

Reproduction

Issue

Reproduction

Issue

Reproduction

Issue

Reproduction

Issue

Issue

Reproduction

Issue

Reproduction

Issue

Reproduction

output

Issue

Reproduction

Issue

Issue

Reproduction

Issue

Reproduction

Issue

Reproduction

Recommend Projects

Recommend Topics

Recommend Org