how to join a subquery with conditions in Squeel -

prologue

i've embraced squeel – , enjoying every step! thank sharing, ernie miller!

i'm developing ruby 1.9.2 , squeel 1.0.2 , rails 3.2.5

(i'll confess having restructured question entirely - hoping increase readability , better chances of getting answer) <:)

use case

i'd (super)user able assign authorizations , permissions this

• a user_group should able have multiple authorizations
• an authorization should able have multiple permissions
• a permission should able control access (manipulating) data
  • via controller (the request path)
  • on instances of class
  • on particular instance

the acl system should lazy – ie if no roles/authorizations given, users not concern acl @ all.

migrations

i identified role , (a polymorphic) roleable entities use case have

a role right out of ordinary

create_table :roles |t| t.references :ox t.string :name t.boolean :active, default: true t.timestamps end 

and roleable bit more descriptive

create_table :roleables |t| t.references :ox t.references :role t.references :roleable, polymorphic: true t.string :authorization t.string :controller t.boolean :active, default: true t.timestamps end 

classes

the system has generic class - abstractactionbase - inherits activerecord:base, , classes inherits (allowing me add systemwide attributes , methods in 1 place)

so - in part - abstractactionbase looks like

class abstractactionbase < activerecord::base self.abstract_class=true require 'tempfile' belongs_to :ox has_many :roleables, as: :roleable attr_accessible :ox_id validates_presence_of :ox_id # # models inheriting have versions has_paper_trail # # # # class method providing index select's being married roleables (permissions) # used abstraction_actions_controller build_collection calls method # result 'should' activerelation - used kamanari 'result' call readying pagination # def self.with_authorizations # # select * any_table @ # left join ( # select r.roleable_id, r.roleable_type, group_concat( r.authorization ) # roleables r # r.authorization not null # , r.roleable_id=at.id # , r.roleable_type=at.base_class # , r.role_id not in (1,2,3) <--- id's current_user.roles # ) rm on rm.roleable_id=at.id , rm.roleable_type=at.base_class # # provide this: # # |.......| last column in table 'at' | roleable_id | roleable_type | authorizations | # |.......| value | 1 | 'usergroup' | 'insert,create'| # |.......| yet value | 92 | 'usergroup' | 'read' | # # self.where{ active==true } end # compile collection of records - regard search using ransack def base.collection( params, resource_set ) # # kaminari (and continous scrolling) # params[:page] ||= 1 params[:per_page] ||= self.per_page params[:o] ||= self.resource_order_by distinct = params[:distinct].nil? ? false : params[:distinct].to_i.zero? resource_set = (resource_set.respond_to?( "result")) ? resource_set.result(:distinct => distinct) : resource_set (resource_set.respond_to?( "page")) ? resource_set.order(params[:o]).page( params[:page] ).per( params[:per_page] ) : resource_set.order(params[:o]) end end 

part of role class looks this

class role < abstractactionbase has_many :roleables scope :active, where{ active.eq true } # # role allow def permissions roleables.permissions.scoped end # # whom role allow def authorizations roleables.authorizations.scoped end # returns true if roleables (permissions) authorizes options # options { controller: "", action: "", record: instance, is_class: boolean } def authorizes?( options={} ) coll = permissions coll = coll.on_action(options.delete(:action)) if options.keys.include? :action coll = coll.on_entity( options.delete(:record), options.delete(:is_class) || false ) if options.keys.include? :record coll = coll.on_controller(options.delete(:controller)) if options.keys.include? :controller (coll.count>0) === true end end 

the roleable class looks this

class roleable < abstractactionbase belongs_to :role belongs_to :roleable, polymorphic: true # roleables authorizes users through user_groups # (in case authorization "-") # providing them permissions on controllers, actions , instances scope :authorizations, where{ authorization == nil } scope :permissions, where{ authorization != nil } # using squeel, find roleables on particular controller or controller def self.on_controller(ctrl) where{ (controller==ctrl) | (controller==nil) } end # using squeel, find roleables on particular authorization or allowed 'all' def self.on_action(action) where{ (authorization=~ "%#{action}%") | (authorization=="all") } end # using squeel, find roleables on particular instance/record or class def self.on_entity(entity, is_class=false) if is_class where{ ((roleable_type==entity.base_class.to_s ) & ( roleable_id==nil)) | ((roleable_type==nil) & (roleable_id==nil)) } else where{ ((roleable_type==entity.class.to_s ) & ( roleable_id==entity.id)) | ((roleable_type==nil) & (roleable_id==nil)) } end end end 

logic

creating

this allows me authorizations - assigning roles someone/something - in case authorization string nil, like

the user_group sales assigned role sales roleable.create({ role: @sales, roleable: @user_group })

at same time can permissions - describing particulars of role - like

the role sales has index, create, edit , delete permissions on orderhead , orderdetail tables with

• roleable.create({ role: @sales, authorization: "index,create,edit,delete", roleable: @user_group, controller: "order_heads" })
• roleable.create({ role: @sales, authorization: "index,create,edit,delete", roleable: @user_group, controller: "order_details" })

these 'particulars' can ethereal like

roleable.create({ role: @sales, authorization: "index" })

somewhat real

roleable.create({ role: @sales, authorization: "index", roleable_type: 'orderhead' })

or expressed

roleable.create({ role: @sales, authorization: "index", roleable: orderhead.first })

selecting

most every controller inherits abstractactionscontroller index (and other actions) defined. controller self inherits inheritedresources:base

class abstractactionscontroller < inheritedresources::base # < applicationcontroller append_view_path viewtemplate::resolver.instance respond_to :html, :xml, :json, :js, :pdf belongs_to :ox, :optional => true before_filter :authorize! before_filter :authenticate! before_filter :warn_unless_confirmed! before_filter :fix_money_params, :only => [:create,:update] # /collection - printers def index # session[:params] = params # # preparing ransack unless params[:q].nil? params[:q]= { :"#{params[:q_fields]}" => params[:q] } end super |format| format.html format.js { render layout: false } format.pdf{ render :pdf => generate_pdf(false) , return } format.xml { render layout: false } format.json # field lookup request? unless params[:lookup].nil? render layout: false, :json => collection.map(&:select_mapping) else render json: collection.map { |p| view_context.grow_mustache_for_index(p, collection, (parent? ? collection : resource_class.order(:id)), @selected ) } end end end end # collection method on inherited_resources # gets overloaded ransack search , kaminari pagination (on model) def collection # @collection ||= build_collection # todo - test whether caching collection possible build_collection end def build_collection unless params[:belongs].nil? # debugger parent = params[:belongs].constantize.find(params[:belongs_id]) @selected = parent.nil? ? [] : parent.send( rewrite_association(params[:assoc],parent) ) @search_resource = core_entity(params[:assoc].constantize) @search_resource = @search_resource.search(params[:q]) unless params[:q].nil? else @search_resource = rewrite_end_of_association_chain(resource_class) @search_resource = core_entity(@search_resource) @search_resource = @search_resource.search(params[:q]) unless params[:q].nil? end # authorize rows @search_resource = @search_resource.with_authorizations # left joins roleables coalescing "authorization" field roles id's not owned current_user through user_groups @resources ||= resource_class.collection( params, @search_resource ) end end 

challenge

what long story presenting short question <:)

how write with_authorizations method returning activerelation (and preferably using squeel)

walt,

you may making more complicated necessary. if i'm reading right, primary purpose of subquery concatenated list of authorizations available in results. if case, can eager_load authorizations , expose names via method on role model concatenation you. has secondary upside of being compatible dbs other mysql.