We've moved discussions to Discord

Join Discord

Rails Active Record Query - how to display projects to project creators/members [SOLVED]

Richard •
I have three tables: User through devise, Project, and ProjectUser.

A User can be part of many Project through ProjectUser's table. How can I query so Project's index action displays Projects that the current user has created and is a member of?

Let's say I have three projects and two team members:
  • User 1 created and is a member of Project A
  • User 2 created and is a member of Project B
  • User 2 created and is a member of Project C
  • User 1 is a member of Project C

When I'm signed in as:
  • User 1, I should be able to see both Project A and C.
  • User 2, I should be able to see both Project B and C.

Here are my models, controllers, and schema:

projects_controller.rb
@projects = current_user.projects.joins(:project_users).distinct.order("created_at DESC")

project.rb
class Project < ApplicationRecord
  has_many :project_users
  has_many :users, through: :project_users
end

user.rb
class User < ApplicationRecord
  has_many :project_users
  has_many :projects, through: :project_users
end

project_user.rb
class ProjectUser < ApplicationRecord
  belongs_to :user
  belongs_to :project
end

schema.rb
create_table "project_users", force: :cascade do |t|
    t.bigint "user_id", null: false
    t.bigint "project_id", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["project_id"], name: "index_project_users_on_project_id"
    t.index ["user_id"], name: "index_project_users_on_user_id"
end

projects/index.html.erb
<% @projects.each do |project| %>
  <%= project.title %>
  <% project.users.each do |user| %>
    <%= user.first_name %>
  <% end %>
<% end %>
William Kennedy •

Project.joins(:project_users).where(user_id: current_user.id, project_users: {user_id: current_user.id})


Without testing this what comes to the top of my head. You will have to dip into ariel to more advanced rails queries. Here I've done a join and then ran. 

Doing a quick google gave me this https://medium.com/swlh/getting-really-good-at-rails-joins-93fd5b33fa8e
Richard •
Hi  William Kennedy - thanks for the help.

Using the above joins query only displays the projects in which the current user created and is part of. Let me know if pasting DB data from Project and ProjectUser tables from the rails console would help.
William Kennedy •
Hi Richard, 

I thought that is what you wanted: 

Let's say I have three projects and two team members:
  • User 1 created and is a member of Project A
  • User 2 created and is a member of Project B
  • User 2 created and is a member of Project C
  • User 1 is a member of Project C

When I'm signed in as:
  • User 1, I should be able to see both Project A and C.
  • User 2, I should be able to see both Project B and C.

As User 1, do you now see Project A and C

Richard •
William Kennedy  as User 1, I only see Projects I've created and are part of through me creating the projects, but I don't see projects where User 2 created and am part of. Not sure if that made complete sense.
William Kennedy •
Ah I get ya. You need an OR to handle the case where project.user_id may not be the user

Project.joins(:project_users).where(project_users: {user_id: current_user.id}).or.where(user_id: current_user.id)

OR it might be

Project.joins(:project_users).where(project_users: {user_id: current_user.id}).or.where(projects: {user_id: current_user.id})


Richard •
Thanks William Kennedy ! Getting closer! 😁

I'm getting wrong number of arguments (given 0, expected 1) when using these queries. Where should I be passing this argument – ProjectsController or Project#Index view?

TIL: or query on Rails 🙂
William Kennedy •
Looks like the syntax has changed for rails 6. I was thinking of the old OR gem. My idea was to point you in the right direction not do the work for you ;) Normally, I would play around with queries in the console before putting them into the controller.

https://guides.rubyonrails.org/active_record_querying.html#or-conditions

Take a look at that link and use it for reference. 

You would assign the result to your instance variable in the controller action. 

@projects = Project.joins(:project_users).where(project_users: {user_id: current_user.id}).or(Project.where(projects: {user_id: current_user.id}).distinct

Basically, I'm trying to get the SQL to look like this so but not using ActiveRecord so much

SELECT * from projects INNER JOIN project_users ON projects_users.project_id = projects.id WHERE projects.user_id = USER_ID OR project_users.user_id = USER_ID

If you still have trouble, reach out to my personal email william.kennedy@hey.com and we can arrange some time to pair on it. 
Richard •
Hey  William Kennedy  appreciate the pointers! I figured it out :)
My Discussions Participating All Discussions
Notifications
You’re not receiving notifications from this thread.