Databases
StackOverflow2013
Postsdbo
PORails 3 - list the groups that the current user is not already a member of
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORails 3 - list the groups that the current user is not already a member of
    text
    copied!<p>I'm converting an app from php to rails, and I'm still learning my way around rails and AR. </p> <p><strong>Simply</strong>: I want to list the groups that the current user is not already a member of. </p> <p><strong>Failed Approach</strong>:</p> <ol> <li><p><code>Cause.select('causes.*').joins(:users).group('causes.id').where("cause_user_memberships.user_id NOT IN (?)", current_user.id)</code></p></li> <li><p><code>Cause.select('causes.*').joins(:users).group('causes.id').where("cause_user_memberships.user_id NOT IN (SELECT cause_user_memberships.cause_id FROM cause_user_memberships WHERE cause_user_memberships.user_id =(?))", current_user.id)</code></p></li> <li>Many more...</li> </ol> <p>Thanks for your help!</p> <h1>Some info about models</h1> <p><strong>User.rb (snippet)</strong></p> <pre><code>has_many :cause_user_memberships has_many :causes, :through =&gt; :cause_user_memberships </code></pre> <p><strong>Cause.rb</strong></p> <pre><code>attr_accessible :title, :location, :description,... has_many :cause_user_memberships has_many :users, :through =&gt; :cause_user_memberships </code></pre> <p><strong>Cause_User_Membership.rb</strong> (&lt;--probably not my best model name)</p> <pre><code> # == Schema Information # # Table name: cause_user_memberships # # id :integer not null, primary key # user_id :integer not null # cause_id :integer not null # created_at :datetime not null # updated_at :datetime not null # class CauseUserMembership &lt; ActiveRecord::Base attr_accessible :cause_id, :user_id belongs_to :user belongs_to :cause, :counter_cache =&gt; :users_count accepts_nested_attributes_for :cause validates_uniqueness_of :user_id, :scope =&gt;[:cause_id] end </code></pre> <h1>Update: Follow up</h1> <p>Derp, you're right it worked! Thanks!</p> <p><strong>Small follow up</strong>, the query times seem pretty long. Does this indicate a problem? I have less than 20 records in each table. (Below are 2 query results, one that includes the geocoder gem I intend to use and another that doesn't. Sorry if it's a little messy.)</p> <p><strong>In Rails Console:</strong> </p> <pre><code>Cause Load (1003.0ms) SELECT "causes".* FROM "causes" LEFT JOIN cause_user_memberships ON cause_user_memberships.cause_id = causes.id AND cause_user_memberships.user_id = 1 WHERE (cause_user_memberships.id IS NULL) EXPLAIN (34.3ms) EXPLAIN SELECT "causes".* FROM "causes" LEFT JOIN cause_user_memberships ON cause_user_memberships.cause_id = causes.id AND cause_user_memberships.user_id = 1 WHERE (cause_user_memberships.id IS NULL) </code></pre> <p><strong>QUERY PLAN</strong></p> <pre><code> Hash Right Join (cost=10.45..37.99 rows=1 width=3168) Hash Cond: (cause_user_memberships.cause_id = causes.id) Filter: (cause_user_memberships.id IS NULL) -&gt; Seq Scan on cause_user_memberships (cost=0.00..27.50 rows=7 width=8) Filter: (user_id = 1) -&gt; Hash (cost=10.20..10.20 rows=20 width=3168) -&gt; Seq Scan on causes (cost=0.00..10.20 rows=20 width=3168) (7 rows) </code></pre> <p><strong>With Geocoder on local host:</strong> </p> <pre><code>User Load (18.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1 Cause Load (49.6ms) SELECT causes.*, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((40.714269 - causes.lat) * PI() / 180 / 2), 2) + COS(40.714269 * PI() / 180) * COS(causes.lat * PI() / 180) * POWER(SIN((-74.005972 - causes.lng) * PI() / 180 / 2), 2))) AS distance, CAST(DEGREES(ATAN2( RADIANS(causes.lng - -74.005972), RADIANS(causes.lat - 40.714269))) + 360 AS decimal) % 360 AS bearing FROM "causes" LEFT JOIN cause_user_memberships ON cause_user_memberships.cause_id = causes.id AND cause_user_memberships.user_id = 1 WHERE (causes.lat BETWEEN 36.37231550667456 AND 45.05622249332544 AND causes.lng BETWEEN -79.73435509229111 AND -68.27758890770889 AND 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((40.714269 - causes.lat) * PI() / 180 / 2), 2) + COS(40.714269 * PI() / 180) * COS(causes.lat * PI() / 180) * POWER(SIN((-74.005972 - causes.lng) * PI() / 180 / 2), 2))) &lt;= 300) AND (cause_user_memberships.id IS NULL) ORDER BY distance ASC </code></pre> <p><code>Completed 200 OK in 1068ms (Views: 49.1ms | ActiveRecord: 791.4ms)</code></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