Created
June 23, 2014 23:00
-
-
Save adamaig/ebb01481f4f5bd3a334f to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # this query set returns the classmates of a specific user | |
| u1 = Arel::Table.new( :users ) | |
| r1 = Arel::Table.new( :roles ) | |
| ur1 = Arel::Table.new( :users_roles ) | |
| u2 = Arel::Table.new( :users ) | |
| r2 = Arel::Table.new( :roles ) | |
| ur2 = Arel::Table.new( :users_roles ) | |
| # find all roles for user.id = 1 on resources of type UserClass | |
| u1c = r1.join( ur1 ).on( r1[:id].eq( ur1[:role_id]), r1[:resource_type].eq('UserClass')). | |
| where( ur1[:user_id].eq( 1 ) ). | |
| project( r1[:resource_id].as('class_id')).as('u1c') | |
| # find all users that have a role on any resources in u1c | |
| cm = u2.join(ur2).on( u2[:id].eq(ur2[:user_id]) ). | |
| join(r2).on( r2[:id].eq( ur2[:role_id])). | |
| join(u1c).on( r2[:resource_id].eq( u1c[:class_id]) ). | |
| project('*') | |
| # This is not as performant because it requires doing large scans for the subselect | |
| class User < ActiveRecord::Base | |
| def user_classes | |
| UserClass.joins(roles: :users).where(users: {id: self}) | |
| end | |
| def classmates | |
| User.joins(:roles). | |
| where(roles: {resource_type: 'UserClass', resource_id: user_classes}). | |
| order( 'username ASC' ) | |
| end | |
| end |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| create_table "roles", :force => true do |t| | |
| t.string "name" | |
| t.integer "resource_id" | |
| t.string "resource_type" | |
| t.datetime "created_at", :null => false | |
| t.datetime "updated_at", :null => false | |
| end | |
| create_table "users", :force => true do |t| | |
| t.string "username", :default => "", :null => false | |
| end | |
| create_table "users_roles", :id => false, :force => true do |t| | |
| t.integer "user_id" | |
| t.integer "role_id" | |
| end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment