nobilik nobilik - 1 year ago 31
SQL Question

Complex SQL query in Rails?

I need to select groups from all user's groups that do not belong to projects of the current user.

  1. groups habtm projects

  2. projects belongs_to user

  3. groups habtm users

The problem is that a group can exist without a project. I understand how to make separate simple queries:

@groups = current_user.groups.includes(:groups_projects).where.('groups_projects.project_id' => nil)

@groups = current_user.groups.includes(:groups_projects).where.not('groups_projects.project_id' => current_user.projects.ids)

How to make it in one query?


According the answer I make the query:

@groups = current_user.groups.includes(:groups_projects).where("'groups_projects.project_id' = ? AND 'groups_projects.project_id' NOT IN (?)", nil, current_user.project_ids)

But now here is an error:

PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "groups_projects.project_id"


Answer below is good, but it doesn't do the job for me, so I make it by different way:

@groups = current_user.groups - current_user.groups.joins(:projects).merge(current_user.projects)

Maybe it's no right way, but it works.

Answer Source

You can use SQL in where clauses representing the WHERE part of the underlying SQL statement. In your case it might look like

.includes(:groups_projects).where('groups_projects.project_id = ? OR groups_projects.project_id NOT IN (?)', nil, current_user.projects.ids).references(:groups_projects)