Question 285212 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 'where' is overriding my default 'order' in rails

i am trying to make a one qyery based on the result i am applying another query for some tasks.

here i have one method filter_based_on_ticket_creation in which i am getting some data based on descending order which is one of the my filter ,based on that data i am making another query which will give some additional details which i want to display.

here is the snippest,

  def fetch_deallocations_based_on_department
    department = params[:department]
    deallocations = []
    filter_based_on_ticket_creation(params).joins(:resource_info).where('resource_infos.department_name = ?',department).order(due_date: :asc).limit(5).each do |details|
      deallocations.push([details.developer.name,details.due_date.present? ? details.due_date&.strftime('%d / %m / %Y') : 'Date not assigned'])
    end
    render json: deallocations
  end


  def filter_based_on_ticket_creation(params)
    if (params[:q].present? && params[:q][:updated_at_lteq].present?)
      @q.result.order(updated_at: :desc).limit(params[:q][:updated_at_lteq].to_i)
    else
      @q.result
    end
  end

here i have total 32 deallocation record,

irb(main):002:0> Deallocation.all.pluck(:id)
  Deallocation Pluck (0.5ms)  SELECT "deallocations"."id" FROM "deallocations"
=> [75, 24, 25, 26, 82, 27, 28, 74, 36, 41, 78, 58, 76, 53, 29, 40, 81, 30, 77, 23, 38, 39, 54, 68, 57, 70, 71, 72, 79, 73, 80, 83]

so now I want to make a filter that gives the latest 5 or 10 tickets for that I have made one method (filter_based_on_ticket_creation(params)) that gives me the latest data ,based on updated_at means it gives the latest 5 records or 10 records
here filter_based_on_ticket_creation(params) return some data

  Deallocation Load (0.4ms)  SELECT "deallocations".* FROM "deallocations" ORDER BY "deallocations"."updated_at" DESC LIMIT $1 
pry(#<DeallocationsController>)> @filterd_deallocation.pluck(:id)
=> [75, 83, 82, 81, 40]

so after applying this filter i have 2 use cases one is that i want to display those 5 records and for another use case i am applying some where and joins condition on filtered data.

and when i am applying where condition which is on my fetch_deallocations_based_on_department method so it will override query and give different output

 @filterd_deallocation.joins(:resource_info).where('resource_infos.department_name = ?','ROR')
  Deallocation Load (0.6ms)  SELECT "deallocations".* FROM "deallocations" INNER JOIN "cims"."resource_infos" ON "cims"."resource_infos"."id" = "deallocations"."resource_info_id" WHERE (resource_infos.department_name = 'ROR') ORDER BY "deallocations"."updated_at" DESC LIMIT $1  [["LIMIT", 5]]

@filterd_deallocation.joins(:resource_info).where('resource_infos.department_name = ?','ROR').pluck(:id)
  Deallocation Pluck (1.3ms)  SELECT "deallocations"."id" FROM "deallocations" INNER JOIN "cims"."resource_infos" ON "cims"."resource_infos"."id" = "deallocations"."resource_info_id" WHERE (resource_infos.department_name = 'ROR') ORDER BY "deallocations"."updated_at" DESC LIMIT $1  [["LIMIT", 5]]
  ↳ (pry):4:in `deallocation_dashboard'
=> [75, 83, 40, 29, 53] # here additional 40,29,53 ids are coming

so here you can see the in SQL that in filter_based_on_ticket_params method makes a this query

ELECT "deallocations".* FROM "deallocations" ORDER BY "deallocations"."updated_at" DESC LIMIT $1

so when i am applying this query

filter_based_on_ticket_creation(params).joins(:resource_info).where('resource_infos.department_name = ?',department).order(due_date: :asc).limit(5)

it will override the default query because of SQL query order of execution in which ‘where’ has more priority than ‘order’ so it will make a ‘where’ query first and based on that data it will make ‘order’

SELECT "deallocations".* FROM "deallocations" INNER JOIN "cims"."resource_infos" ON "cims"."resource_infos"."id" = "deallocations"."resource_info_id" WHERE (resource_infos.department_name = 'ROR') ORDER BY "deallocations"."updated_at" DESC LIMIT $1

so I want a persistent query,what should I do means the second query will not change the execution of SQL flow and I get the result based on my first query data only.

Tags:

2

Answers


  1. 0

    The additional 2 ids are coming because you told the database that you want 5 results.

    With the previous query, there were 5 results. And after you apply your filter, only 3 (75, 83, 40) of those match that, but you’re still telling the database you want 5 results (.limit(5)), so the database keeps looking and finds more records that were not included in the 5 initially.

    Without more context regarding the use case (why exactly 5 results at first and then less?), it’s difficult to say how you should resolve this.

    But limit is probably not the right tool for this.

    Login or Signup to reply.
  2. 0

    I can’t give you a complete answer as I don’t have knowledge about ruby on rails, but this should help you move forward.

    As I understand your question:
    You have a result of the newest deallocation-ids [Query1] and want to filter these according to a related department value. [Query2]

    You don’t want a new result, which shows all latest deallocation-ids of the department.

    The easiest way would be to supply the desired ids from query1.

    # No idea if this is the way to write this in ruby
Deallocation.joins(:resource_info).where(deallaction.id: filterd_deallocation).where('resource_infos.department_name = ?',department)

    This should result in a query like

    SELECT "deallocations".* 
FROM "deallocations" 
INNER JOIN "cims"."resource_infos" ON "cims"."resource_infos"."id" = "deallocations"."resource_info_id" 
WHERE 
 (resource_infos.department_name = 'ROR') 
AND
 (deallocations.id IN (75,83))

    As you’ve already noticed is that there is strict order to sql execution and it works as intended.
    Order only applies to the result set, which was already filtered by where.
    I urge you to rethink your approach how you want to obtain and filter the data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search