Post.where() causing 500 error

Development

AI-generated summary

Issue with Post.where() causing 500 error

Andrew_Byrne is experiencing an issue with a Postgres query using Post.where() that results in a 500 error. The original query was Post.where('user_id = ? and post_number = ?', params["uid"], params["tid"]).

After examining the logs, Andrew_Byrne found that the issue was with the input value. The query fails when passing multiple values as a string, e.g., Post.where('user_id = ? or topic_id = ?', '1,2', '4, 5').

tgxworld asked for the error message, which was ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR: invalid input syntax for integer: "1,2".

tgxworld suggested using Post.where(user_id IN (?) OR topic_id IN (?), [1,2], [4,5]), which helped resolve the issue.

The final solution, as posted by Andrew_Byrne, was to split the string into an array and use the IN operator: posts = Post.where('user_id IN (?) OR topic_id IN (?)', uid, tid).

1

Noooob question (2 days ruby experience :wink:
I have a plugin almost doing what I want but have a hang up here:

 posts = Post.where('user_id = ? and post_number = ?', params["uid"], params["tid"])
		      .order(created_at: :desc)
		      .limit(params["total"])

I’ve tried a number of versions. but they all case errors .
It feels more like a query issues than syntax but I bow before the experts edu-mecate me.

2

What does the logs say? :slight_smile:

3

so good of you to ask, and admittedly that helped… :smiley:

BUT perhaps you can assist further with my query! The issue was the input value
while I can do

ok
Post.where('user_id: '1,2') #works

Post.where('topic_id: '4,5')  #works

Fails
Post.where('user_id = ? or topic_id = ?', '1,2', '4, 5') #fails!

So again I assume! I’ll need to iterate over each user id, but… NOOOOB (assistance greatly appreciated!

4

So why did it fail? What is the error that was raised when you ran the code? :slight_smile:

5

Boom! (thanks BTW )

ActiveRecord::StatementInvalid (PG::InvalidTextRepresentation: ERROR:  invalid input syntax for integer: "1,2"
LINE 1: ...ERE ("posts"."deleted_at" IS NULL) AND (user_id = '1,2' or t...
                                                             ^
: SELECT  "posts".* FROM "posts" WHERE ("posts"."deleted_at" IS NULL) AND (user_id = '1,2' or topic_id = '4')  ORDER BY "posts"."created_at" DESC LIMIT 30)
/var/lib/gems/2.2.0/gems/rack-mini-profiler-0.9.9.2/lib/patches/db/pg.rb:93:in `exec'
6

Try Post.where(user_id IN (?) OR topic_id IN (?), [1,2], [4,5])

7

So if I say post.where() it causes a error?

8

@tgxworld Thanks that helped a bunch.

For the record I was doing a bunch of stuff that I SHOULD have known better…

  1. not splitting the string to an actual array (duh facepalm )
  2. your request seems to have fix the syntax issues (woot)

final code

uid = params["uid"].split(",")
tid =  params["tid"].split(",")

posts = Post.where('user_id IN (?) OR topic_id IN (?)', uid, tid )
			.order(created_at: :desc)
			.limit(params["total"])
			.offset(params["start"])

obj = {follow_posts: posts }
render json: obj

Massive thanks!! :smiley: