mardi 13 juin 2017

Include select scope in outer join includes

Rails 3, I want to include a select scope in a join clause. Here's the code I have:

require 'logger'
require 'active_record'
require 'pg'

ActiveRecord::Base.establish_connection(:adapter => "sqlite3", :database => ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Schema.define do
  create_table :users, :force => true do |t|
    t.string :name
  end

  create_table :properties, :force => true do |t|
    t.integer :user_id, unique: true
    t.string :line1
    t.string :line2
  end
end

class User < ActiveRecord::Base
  attr_accessible :name
  has_one :property
end

class Property < ActiveRecord::Base
  belongs_to :user
  attr_accessible :address, :line1, :line2
  default_scope :select => "#{table_name}.*, #{table_name}.line1 || ', ' || #{table_name}.line2 AS address"
end

u = User.new(name: :a)
p = Property.new(line1: :y, line2: :z, user: u)
p.user = u
p.save

This line generates the following sql:

User.first.property.address

User Load (0.1ms)  SELECT "users".* FROM "users" LIMIT 1
Property Load (0.1ms)  SELECT properties.*, properties.line1 || ', ' || properties.line2 AS address FROM "properties" WHERE "properties"."user_id" = 1 LIMIT 1

But this line doesn't include the custom select scope with the computed column:

User.includes(:property).where('properties.address=?', 'y, z').first.property.address

SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "properties"."id" AS t1_r0, "properties"."user_id" AS t1_r1, "properties"."line1" AS t1_r2, "properties"."line2" AS t1_r3 
FROM "users" 
LEFT OUTER JOIN "properties" ON "properties"."user_id" = "users"."id" 
WHERE (properties.address='y, z') 
LIMIT 1

Any way to get it to do something like this?

LEFT OUTER JOIN (SELECT properties.*, properties.line1 || ', ' || properties.line2 AS address FROM "properties") AS "properties" ON "properties"."user_id" = "users"."id" 
WHERE (properties.address='y, z') 

Aucun commentaire:

Enregistrer un commentaire