Databases
StackOverflow2013
Postsdbo
POhow to join a subquery with conditions in Squeel
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to join a subquery with conditions in Squeel
    text
    copied!<h1>Prologue</h1> <p>I've embraced Squeel – and enjoying every step! Thank you so much for sharing, Ernie Miller!</p> <p>I'm developing with ruby 1.9.2 and Squeel 1.0.2 and Rails 3.2.5</p> <p>(I'll confess to having restructured the question entirely - hoping to increase the readability and better my chances of getting an answer) &lt;:)</p> <h1>Use case</h1> <p>I'd like a (super)user to be able to assign authorizations and permissions like this</p> <ul> <li>a user_group should be able to have multiple authorizations</li> <li>an authorization should be able to have multiple permissions</li> <li>a permission should be able to control access to (manipulating) data <ul> <li>via the controller (the request path)</li> <li>on instances of a Class</li> <li>on any particular instance</li> </ul></li> </ul> <p>The ACL system should be <em>lazy</em> – ie if no roles/authorizations are given, the users obviously does not concern themselves with ACL at all.</p> <h1>Migrations</h1> <p>I identified <strong>role</strong> and (a polymorphic) <strong>roleable</strong> entities from the use case an thus I have</p> <p>a Role right out of the ordinary</p> <pre><code>create_table :roles do |t| t.references :ox t.string :name t.boolean :active, default: true t.timestamps end </code></pre> <p>and a Roleable a bit more descriptive</p> <pre><code>create_table :roleables do |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 </code></pre> <h1>Classes</h1> <p>The system has a generic class - AbstractActionBase - which inherits from ActiveRecord:Base, and which all classes inherits from (allowing me to add systemwide attributes and methods in one place)</p> <p>So - in part - my AbstractActionBase looks like</p> <pre><code>class AbstractActionBase &lt; 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 # # all models inheriting from this will have versions has_paper_trail # # # # Class method to providing for index SELECT's being married with roleables (permissions) # used from abstraction_actions_controller where build_collection calls this method # the result 'should' be an ActiveRelation - used for the Kamanari 'result' call to readying pagination # def self.with_authorizations # # SELECT * FROM any_table at # left join ( # select r.roleable_id, r.roleable_type, group_concat( r.authorization ) # from roleables r # where r.authorization is not null # and r.roleable_id=at.id # and r.roleable_type=at.base_class # and r.role_id not in (1,2,3) &lt;--- ID's are current_user.roles # ) rm on rm.roleable_id=at.id and rm.roleable_type=at.base_class # # which will provide for this: # # |.......| last column in table 'at' | roleable_id | roleable_type | authorizations | # |.......| some value | 1 | 'UserGroup' | 'insert,create'| # |.......| yet another value | 92 | 'UserGroup' | 'read' | # # self.where{ active==true } end # compile a 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 =&gt; 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 </code></pre> <p>Part of the Role class looks like this</p> <pre><code>class Role &lt; AbstractActionBase has_many :roleables scope :active, where{ active.eq true } # # what does this role allow def permissions roleables.permissions.scoped end # # to whom does this role allow def authorizations roleables.authorizations.scoped end # returns true if the roleables (permissions) authorizes the options # options are { 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&gt;0) === true end end </code></pre> <p>The Roleable class looks like this</p> <pre><code>class Roleable &lt; AbstractActionBase belongs_to :role belongs_to :roleable, polymorphic: true # roleables authorizes users through user_groups # (in which case the authorization is "-") # providing them permissions on controllers, actions and instances scope :authorizations, where{ authorization == nil } scope :permissions, where{ authorization != nil } # using Squeel, find roleables on a particular controller or any controller def self.on_controller(ctrl) where{ (controller==ctrl) | (controller==nil) } end # using Squeel, find roleables on a particular authorization or allowed 'all' def self.on_action(action) where{ (authorization=~ "%#{action}%") | (authorization=="all") } end # using Squeel, find roleables on a particular instance/record or class def self.on_entity(entity, is_class=false) if is_class where{ ((roleable_type==entity.base_class.to_s ) &amp; ( roleable_id==nil)) | ((roleable_type==nil) &amp; (roleable_id==nil)) } else where{ ((roleable_type==entity.class.to_s ) &amp; ( roleable_id==entity.id)) | ((roleable_type==nil) &amp; (roleable_id==nil)) } end end end </code></pre> <h1>Logic</h1> <h2>Creating</h2> <p>This allows me <em>authorizations</em> - assigning roles to someone/something - in which case the authorization string is nil, like</p> <blockquote> <p>The user_group <strong>sales</strong> is assigned the role <strong>sales</strong> with Roleable.create({ role: @sales, roleable: @user_group })</p> </blockquote> <p>At the same time I can do <em>permissions</em> - describing the particulars of any role - like</p> <blockquote> <p>The role <strong>sales</strong> has <em>index</em>, <em>create</em>, <em>edit</em> and <em>delete</em> permissions on the OrderHead and OrderDetail tables with</p> </blockquote> <ul> <li>Roleable.create({ role: @sales, authorization: "index,create,edit,delete", roleable: @user_group, controller: "order_heads" })</li> <li>Roleable.create({ role: @sales, authorization: "index,create,edit,delete", roleable: @user_group, controller: "order_details" })</li> </ul> <p>these 'particulars' can be ethereal like</p> <blockquote> <p>Roleable.create({ role: @sales, authorization: "index" })</p> </blockquote> <p>somewhat real</p> <blockquote> <p>Roleable.create({ role: @sales, authorization: "index", roleable_type: 'OrderHead' })</p> </blockquote> <p>or very expressed</p> <blockquote> <p>Roleable.create({ role: @sales, authorization: "index", roleable: OrderHead.first })</p> </blockquote> <h2>Selecting</h2> <p>Most every controller inherits from AbstractActionsController where the index (and other actions) are defined. That controller it self inherits from InheritedResources:Base like this </p> <pre><code>class AbstractActionsController &lt; InheritedResources::Base # &lt; ApplicationController append_view_path ViewTemplate::Resolver.instance respond_to :html, :xml, :json, :js, :pdf belongs_to :ox, :optional =&gt; true before_filter :authorize! before_filter :authenticate! before_filter :warn_unless_confirmed! before_filter :fix_money_params, :only =&gt; [:create,:update] # GET /collection - printers def index # session[:params] = params # # preparing for Ransack unless params[:q].nil? params[:q]= { :"#{params[:q_fields]}" =&gt; params[:q] } end super do |format| format.html format.js { render layout: false } format.pdf{ render :pdf =&gt; generate_pdf(false) and return } format.xml { render layout: false } format.json do # field lookup request? unless params[:lookup].nil? render layout: false, :json =&gt; collection.map(&amp;: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 # the collection method on inherited_resources # gets overloaded with Ransack search and Kaminari pagination (on the model) def collection # @collection ||= build_collection # TODO - test whether caching the collection is 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 a "authorization" field from roles ID's not owned by current_user through his user_groups @resources ||= resource_class.collection( params, @search_resource ) end end </code></pre> <h1>Challenge</h1> <p>What a long story to presenting a short question &lt;:)</p> <p>How do I write the <code>with_authorizations</code> method to returning a ActiveRelation (and preferably using Squeel)</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload