Rails 5 - How to use postgresql query?

Question

In rails 5, I am using pg(postgresql) for a back-end database. Now I want to query through rails and get the data. How can I use IN and ORDER(:created_at, :desc) conditions in a query.

In controller,

PAGE_LIMIT = 5
posts = Post.where("user_id IN (?)", [1,2,3]).order(created_at: :desc)
posts = posts.paginate(params[:page], PAGE_LIMIT)

I am writing a custom method like,

def paginate(page, limit = 5)
  page = page ? page.to_i : 1
  limit = limit.to_i
  offset = (page - 1) * limit
  self.offset(offset).limit(limit)
end

I am new to postgresql. Please help me to solve this issue?

Shruthi R in any case you can share your code so that i can help you to get what exactly you want, i think the answer given below will help you. :) — Anand
– Anand, Commented Dec 15, 2017 at 7:53

Anand · Accepted Answer · 2017-12-15 12:46:07Z

2

suppose you have User model and you want to get user which has id in [1,2,3]

User.where("id IN (?)", [1,2,3]).order(created_at: :desc)

for more dynamic

x = [1,2,3] 
name = "testing123"
User.where("name = ? AND id IN (?)", name, x).order(created_at: :desc)

for more details you can see Active Record Query

to make it working with pagination for array changes to be done here

modified answer

PAGE_LIMIT = 5
posts_arr = Post.where("user_id IN (?)", [1,2,3]).order(created_at: :desc)
posts = Post.where(id: posts_arr.map(&:id)) #here to get active records of posts to make it working for custom pagination
posts = posts.paginate(params[:page], PAGE_LIMIT)

edited Dec 15, 2017 at 12:46

answered Dec 15, 2017 at 7:47

Anand

6,5414 gold badges14 silver badges27 bronze badges

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

13 Comments

jvillian Over a year ago

How about just User.where(id: x).order(created_at: :desc) or User.where(id: x, name: name).order(created_at: :desc)? Seems cleaner and more idiomatic, IMO.

Anand Over a year ago

Yeah its cleaner code but i was just explaning how to use IN thats why i used id in ..

jvillian Over a year ago

Fair enough, and nicely done. Do you mind if I edit it into your answer?

Shruthi R Over a year ago

This query will not take paginate(params[:page] and it throws an error like undefined method 'paginate' for #<Array:0x007f4d41f95598> excluded from capture: DSN not set. How can I paginate for the same?

Anand Over a year ago

@ShruthiR for array you will need to include this require 'will_paginate/array' to your controller or in application controller.

|

Collectives™ on Stack Overflow

Rails 5 - How to use postgresql query?

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related