sql problems with has_many :through

[Jonathan Bramble]

I have a user and coshhform models

class Coshhform < ActiveRecord::Base

using_access_control

has_many :coshhform_users

has_many :users, :through => :coshhform_users

end

and the other end of the association on user. I'm using Devise for Authentication.

I have successfully set up declarative authorization for my other models and it has really cleaned up my code.

I have tested the user association with rspec. 

My read rule for this model on user looks like:

has_permission_on :coshhforms, :to => :read, :join_by => :or do

if_attribute :workflow => 7

if_attribute :users => contains {user}

if_attribute :originator => is {user}

end

I have a problem when attempting to get permissions on read with the contains method.

Without the ':users => contains {user} ' line I get:

> Coshhform.with_permissions_to(:read).order(:id)

SELECT "coshhforms".* FROM "coshhforms" WHERE (("coshhforms"."workflow" = 7) OR ("coshhforms"."originator_id" = 308)) ORDER BY id

With the ':users => contains {user} ' line I get :

SELECT "coshhforms"."id" AS t0_r0, "coshhforms"."title" AS t0_r1, "coshhforms"."description" AS t0_r2, ..(other columns removed)... "users"."group_id" AS t1_r21, "coshhform_users_coshhforms"."id" AS t2_r0, "coshhform_users_coshhforms"."coshhform_id" AS t2_r1, "coshhform_users_coshhforms"."user_id" AS t2_r2, "coshhform_users_coshhforms"."created_at" AS t2_r3, "coshhform_users_coshhforms"."updated_at" AS t2_r4 FROM "coshhforms" LEFT OUTER JOIN "coshhform_users" ON "coshhform_users"."coshhform_id" = "coshhforms"."id" LEFT OUTER JOIN "users" ON "users"."id" = "coshhform_users"."user_id" LEFT OUTER JOIN "coshhform_users" "coshhform_users_coshhforms" ON "coshhform_users_coshhforms"."coshhform_id" = "coshhforms"."id" WHERE (("coshhforms"."workflow" = 7) OR ("users"."id" = 308) OR ("coshhforms"."originator_id" = 308))

which results in “ActiveRecord::StatementInvalid: PG::Error: ERROR: column reference "id" is ambiguous” and also breaks my views.

Moving the ':users => contains {user} ' to the end of the rule block gives me a different error:

SELECT "coshhforms"."id" AS t0_r0, "coshhforms"."title" AS t0_r1, "coshhforms"."description" AS t0_r2, "coshhforms"."workplace" AS t0_r3, "coshhforms"."supervisor_id" AS t0_r4, "coshhforms"."authoriser_id" AS t0_r5, "coshhforms"."supervised" AS t0_r6, "coshhforms"."supervisor_rejection_text" AS t0_r7, "coshhforms"."authorised" AS t0_r8, "coshhforms"."authoriser_rejection_text" AS t0_r9, "coshhforms"."expired" AS t0_r10, "coshhforms"."inreview" AS t0_r11, "coshhforms"."review_date" AS t0_r12, "coshhforms"."supervised_date" AS t0_r13, "coshhforms"."authorised_date" AS t0_r14, "coshhforms"."originator_id" AS t0_r15, "coshhforms"."complete" AS t0_r16, "coshhforms"."submission_text" AS t0_r17, "coshhforms"."complete_date" AS t0_r18, "coshhforms"."qr_token" AS t0_r19, "coshhforms"."created_at" AS t0_r20, "coshhforms"."updated_at" AS t0_r21, "coshhforms"."workflow" AS t0_r22, "users"."id" AS t1_r0, "users"."email" AS t1_r1, "users"."encrypted_password" AS t1_r2, "users"."reset_password_token" AS t1_r3, "users"."reset_password_sent_at" AS t1_r4, "users"."remember_created_at" AS t1_r5, "users"."sign_in_count" AS t1_r6, "users"."current_sign_in_at" AS t1_r7, "users"."last_sign_in_at" AS t1_r8, "users"."current_sign_in_ip" AS t1_r9, "users"."last_sign_in_ip" AS t1_r10, "users"."created_at" AS t1_r11, "users"."updated_at" AS t1_r12, "users"."admin" AS t1_r13, "users"."supervisor" AS t1_r14, "users"."authoriser" AS t1_r15, "users"."phone" AS t1_r16, "users"."title" AS t1_r17, "users"."firstname" AS t1_r18, "users"."lastname" AS t1_r19, "users"."initials" AS t1_r20, "users"."group_id" AS t1_r21 FROM "coshhforms" LEFT OUTER JOIN "coshhform_users" ON "coshhform_users"."coshhform_id" = "coshhforms"."id" LEFT OUTER JOIN "users" ON "users"."id" = "coshhform_users"."user_id" WHERE (("coshhforms"."workflow" = 7) OR ("coshhforms"."originator_id" = 308) OR ("users_coshhforms"."id" = 308)) ORDER BY id

ActiveRecord::StatementInvalid: PG::Error: ERROR: missing FROM-clause entry for table "users_coshhforms"

LINE 1: ... = 7) OR ("coshhforms"."originator_id" = 308) OR ("users_cos...

Any ideas on why the individual columns are listed in this way? or why the permission order causes a different error? 

Thanks,

Jonathan Bramble

[Edward Rudd]

On May 8, 2013, at 7:35 , Jonathan Bramble wrote:

[snip]

which results in “ActiveRecord::StatementInvalid: PG::Error: ERROR: column reference "id" is ambiguous” and also breaks my views.

This is caused by your order(:id).  ActiveRecord (stupidly) just puts whatever you place in the order raw.  And PG doesn't know which id you want to order by as there are now several tables joined (joined by decl auth).   So instead explicitly specify which table and change it to order('coshhforms.id')

Moving the ':users => contains {user} ' to the end of the rule block gives me a different error:

[sip]

ActiveRecord::StatementInvalid: PG::Error: ERROR: missing FROM-clause entry for table "users_coshhforms"

LINE 1: ... = 7) OR ("coshhforms"."originator_id" = 308) OR ("users_cos...

Now that one is interesting.. it looks like something got confused w/ the table name and.  as it should be coshhforms_users not users_coshhforms.  

Any ideas on why the individual columns are listed in this way? or why the permission order causes a different error?

The individual columns are added in I believe by decl auth, but it could be AR due to how all the tables are joined in..  the With_permissions_to method adds in the joins in order to properly filter based on the declared permissions.      the permission order shouldn't cause an issue and that looks like a bug..  Personally I never *trust* the has_many through i permission blocks..  instead I do this

if_attributes :coshhforms_users => { :user => is { user } }

Personally I wish all the extra fields wouldn't get added to the select statement as it really screws up other types of AR queries I try to use. (count, group, uniq, pluck, etc..)

Thanks,

Jonathan Bramble

--
You received this message because you are subscribed to the Google Groups "declarative_authorization" group.
To unsubscribe from this group and stop receiving emails from it, send an email to declarative_author...@googlegroups.com.
To post to this group, send email to declarative_...@googlegroups.com.
Visit this group at http://groups.google.com/group/declarative_authorization?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Edward Rudd

OutOfOrder.cc

Skype: outoforder_cc

317-674-3296

[Jonathan Bramble]

Hi Edward,

Thanks for looking at my question. Having tried to implement the AR query myself I realise that it is not so easy to construct OR conditions. It reminds me that AR does a lot of work for you which you forget about. 

As this part of my application has complex authorisation rules, I will implement it manually, but will use declarative_authorization for the rest of the app. 

Again, thanks for your help,

Dr Jonathan Bramble

University of Leeds

[Edward Rudd]

On May 20, 2013, at 10:09 , Jonathan Bramble wrote:

Hi Edward,

Thanks for looking at my question. Having tried to implement the AR query myself I realise that it is not so easy to construct OR conditions. It reminds me that AR does a lot of work for you which you forget about. 

Yeah, I wish the DSL for AR actually supported OR conditions better than it does, as well as a few other things (<, >=, etc..)   Usually what I end up doing though is popping down to Arel directly and building up pieces of the query and shoving that into AR.

As this part of my application has complex authorisation rules, I will implement it manually, but will use declarative_authorization for the rest of the app. 

Again, thanks for your help,

Dr Jonathan Bramble

University of Leeds

--
You received this message because you are subscribed to the Google Groups "declarative_authorization" group.
To unsubscribe from this group and stop receiving emails from it, send an email to declarative_author...@googlegroups.com.
To post to this group, send email to declarative_...@googlegroups.com.
Visit this group at http://groups.google.com/group/declarative_authorization?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.