Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Aliased attributes cause various relation functions to output malformed sql #370

Closed
abrthel opened this issue Mar 3, 2020 · 0 comments · Fixed by #373
Closed

Aliased attributes cause various relation functions to output malformed sql #370

abrthel opened this issue Mar 3, 2020 · 0 comments · Fixed by #373
Labels
Milestone

Comments

@abrthel
Copy link
Contributor

abrthel commented Mar 3, 2020

Describe the bug

When functions like where, join, order on relation are given aliased attribute instances they
produce malformed sql. This can happen with the method signature api and block level DSL api.

For example:
user_relation.order(user_relation[:name]) would output -> ORDER BY "users"."name" AS 'user_name'

To Reproduce
The following gist reproduces most some of the issues I've found with aliases

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'rom', '5.2.1'
  gem 'rom-sql, '3.2.0'
  gem 'sqlite3'
  gem 'rspec'

  # gem 'pry'
  # gem 'pry-byebug'
end

RSpec.configure do |config|
  config.disable_monkey_patching!
  config.warnings = true
  config.filter_run_when_matching :focus
end

RSpec.describe "With aliased attributes return correct results when" do

  let(:users) { rom.relations[:users] }
  let(:tasks) { rom.relations[:tasks] }

  let(:rom) do
    rom = ROM.container(:sql, 'sqlite::memory') do |conf|
      conf.default.create_table(:users) do
        primary_key :id
        column :name, String, null: false
      end

      conf.default.create_table(:tasks) do
        primary_key :id
        column :name, String, null: false
        foreign_key :user_id, :users, null: false
      end

      conf.relation(:users) do
        schema(infer: true) do
          attribute :name, alias: :user_name
        end
      end

      conf.relation(:tasks) do
        schema(:tasks, infer: true) do
          attribute :user_id, alias: :task_key

          associations do
            belongs_to(:user)
          end
        end
      end
    end
  end

  before do
    rom.relations[:users].insert(id: 1, name: 'Jane')
    rom.relations[:users].insert(id: 2, name: 'John')
    rom.relations[:users].insert(id: 3, name: 'Shelly')

    rom.relations[:tasks].insert(id: 1, name: 'Pick up milk', user_id: 2)
    rom.relations[:tasks].insert(id: 2, name: 'Call Insurance', user_id: 3)
    rom.relations[:tasks].insert(id: 3, name: 'Cancel Phone Plan', user_id: 3)
  end

  describe '#order' do
    let(:result) { [{id: 1, user_name: "Jane"}, {id: 2, user_name: "John"}, {id: 3, user_name: "Shelly"}] }

    it 'given block dsl' do
      relation = users.order { name }
      expect(relation.to_a).to eq(result)
    end

    it 'given attribute instances' do
      relation = users.order(users[:name])

      expect(relation.to_a).to eq(result)
    end
  end

  describe '#select' do
    let(:result) { [{user_name: "Jane"}, {user_name: "John"}, {user_name: "Shelly"}] }

    it 'given attribute instances' do
      relation = users.select(users[:name])

      expect(relation.to_a).to eq(result)
    end
  end

  describe '#join' do
    let(:result) { [{:id=>2, :user_name=>"John", :task=>"Pick up milk"}, {:id=>3, :user_name=>"Shelly", :task=>"Call Insurance"}, {:id=>3, :user_name=>"Shelly", :task=>"Cancel Phone Plan"}] }

    it 'given attribute instances' do
      relation = users.select_append(tasks[:name].as(:task)).join(:tasks, users[:id] => tasks[:user_id])
      expect(relation.to_a).to eq(result)
    end
  end

  describe '#where' do
    let(:result) { [{id: 2, user_name: "John"}] }

    it 'given block dsl' do
      relation = users.where { name.is('John') }

      expect(relation.to_a).to eq(result)
    end

    it 'given attribute instances' do
      relation = users.where(users[:name] => 'John')

      expect(relation.to_a).to eq(result)
    end

    it 'given block dsl and attribute instances' do
      relation = users.where(users[:name] => 'John') { id.is(2) }

      expect(relation.to_a).to eq(result)
    end
  end
end

Expected behavior

I would expect rom to output correct sql when given aliased attributes which can be set during
schema creation using the public api.

Your environment

  • Affects my production application: NO (but is halting development)
  • Ruby version: ruby 2.6.3p62 (2019-04-16 revision 67580) [x64-mingw32]
  • OS: Windows 10 Pro x64
  • rom & rom-sql are latest versions
@abrthel abrthel added the bug label Mar 3, 2020
solnic added a commit that referenced this issue Apr 14, 2020
Add qualifed projections to schemas & update `dataset#select` calls

[changelog]

fixed: "Attributes aliases are properly handled when constructing queries with SQL functions (issue #370 fixed via #373) (@abrthel)"
@solnic solnic added this to the 3.2.1 milestone Apr 14, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
2 participants