Rails - PG::SyntaxError: ERROR: subquery has too many columns

Question

In my Rails 5 app, I'm receiving this error from a query, but I don't know how to fix it.

PG::SyntaxError: ERROR: subquery has too many columns

My controller:

def index
  @canvases = current_user.get_voted Canvas
  @activities = PublicActivity::Activity.order("created_at desc").where(owner_id: current_user.friend_ids, owner_type: "User").or(PublicActivity::Activity.order("created_at desc").where(recipient_id: current_user.id)).or(PublicActivity::Activity.order("created_at desc").where("key = ? AND recipient_id in (?)", "canvas_product.create", @canvases)).paginate page: params[:page], per_page: 30
end

The error is somewhere in this query:

(PublicActivity::Activity.order("created_at desc").where("key = ? AND recipient_id in (?)", "canvas_product.create", @canvases))

At a guess... @canvases is a set of actual canvases, and maybe Rails isn't interpreting these as ids properly... try changing that to: where("key = ? AND recipient_id in (?)", "canvas_product.create", @canvases.map(&:id)) - if that works, then you know that's what it was. — Taryn East
– Taryn East, Commented Feb 20, 2017 at 3:05
That was it! Thank you! Is there any adjustment that I should do to limit @canvases to just be id's before the query (to be more efficient)? Thank you! — yellowreign
– yellowreign, Commented Feb 20, 2017 at 3:50

Taryn East · Accepted Answer · 2017-02-20 04:06:36Z

2

Looks like @canvases is a set of actual canvases, and maybe Rails isn't interpreting these as ids properly...

You can convert that to just ids like this:

where("key = ? AND recipient_id in (?)", "canvas_product.create", @canvases.map(&:id))

Alternatively you can use pluck on the association to pluck out just the ids you need eg:

@canvas_ids = current_user.get_voted(Canvas).pluck(:id)

(though this depends on how get_voted is written)

answered Feb 20, 2017 at 4:06

Taryn East

27.8k9 gold badges89 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mu is too short Over a year ago

Better would be something like where(:recipient_id => @canvases.select(:id)) if @canvases is a relation. That would get AR to use a subquery and leave the work inside the database.

Collectives™ on Stack Overflow

Rails - PG::SyntaxError: ERROR: subquery has too many columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related