Rails order results with multiple joins to same table

Question

--Edit--

I wanted to simplify this question. With this model structure:

has_one :pickup_job, class_name: 'Job', source: :job
has_one :dropoff_job, class_name: 'Job', source: :job

What I want to do is:

Package.joins(:dropoff_job, :pickup_job).order(pickup_job: {name: :desc})

This is obviously not valid, the actual syntax that should be used is:

.order('jobs.name desc')

However the way that rails joins the tables means that I cannot ensure what the table alias name will be (if anything), and this will order by dropoff_job.name instead of pickup_job.name

irb(main):005:0> Package.joins(:dropoff_job, :pickup_job).order('jobs.name desc').to_sql
=> "SELECT \"packages\".* FROM \"packages\" INNER JOIN \"jobs\" ON \"jobs\".\"id\" = \"packages\".\"dropoff_job_id\" INNER JOIN \"jobs\" \"pickup_jobs_packages\" ON \"pickup_jobs_packages\".\"id\" = \"packages\".\"pickup_job_id\" ORDER BY jobs.name desc"

Also I am not in control of how the tables are joined, so I cannot define the table alias.

--UPDATE--

I have had a play with trying to extract the alias names from the current scope using something like this:

current_scope.join_sources.select { |j| j.left.table_name == 'locations' }

But am still a little stuck and really feel like there should be a MUCH simpler solution.

--UPDATE--

pan's answer works in some scenarios but I am looking for a solution that is a bit more dynamic.

Authoritative

Answer 1

Use the concatenation of association name in plural and current table name as a table alias name in the order method:

Package.joins(:dropoff_job, :pickup_job).order('pickup_jobs_packages.name desc')

Dallman

I understand how rails generates the alias, but assume that the order part is set up as a scope and I do not know which order the tables have been joined. I could use that static text 'pickup_jobs_packages.name desc' but that will fail if the pickup_job was joined first. I could change the order text to'jobs.name desc', but this will always order on the first join. – Authoritative

Answer 2

You can try something like this

Package.joins("INNER JOIN locations as dropoff_location ON dropoff_location.id = packages.dropoff_location_id INNER JOIN locations as pickup_location ON pickup_location.id = packages.pickup_location_id)

Idea is to create your own alias while joining the table

Juicy

The problem with that is I am not making the joins myself. I am using a gem called filterrific. I would think that there must be a way to get the alias name from current_scope? – Authoritative

I have given you the bounty because your answer is closer to what I am after but it is in no way the ideal solution I was looking for. – Authoritative