I have an application that has a table Doctors with a name field. The users can put any version of the name in there they want. I'm trying to sort the records based on last name(last word in the string) before I display them in the index view.
I came up with a query that works in the console but gets an error when running it in the controller.
The query is as follows:
current_user.doctors.select("doctors.*, split_part(doctors.name, ' ', -1) AS lastname").order('lastname ASC')
My table migration:
class CreateDoctors < ActiveRecord::Migration[7.1]
def change
create_table :doctors do |t|
t.string :name
t.string :field
t.string :address
t.string :phone
t.string :fax
t.string :website
t.string :portal
t.binary :active, default: true
t.string :group
t.timestamps
end
end
end
My controller code:
def index
@doctors =
current_user.doctors.select("doctors.*, split_part(doctors.name, ' ', -1) AS lastname").order('lastname ASC')
end
And my view code:
<%= turbo_frame_tag Doctor.new %>
<%= turbo_frame_tag "doctors" do %>
<% @doctors.each do |doctor| %>
<%= render 'doctor_index', doctor: doctor %>
<% end %>
<% end %>
The error I get is the following:
17:16:53 web.1 | Started GET "/doctors" for ::1 at 2024-03-16 17:16:53 -0600
17:16:53 web.1 | ActiveRecord::SchemaMigration Load (1.0ms) SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
17:16:53 web.1 | Processing by DoctorsController#index as HTML
17:16:53 web.1 | User Load (1.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2 [["id", 2], ["LIMIT", 1]]
17:16:53 web.1 | Rendering layout layouts/application.html.erb
17:16:53 web.1 | Rendering doctors/index.html.erb within layouts/application
17:16:53 web.1 | Rendered shared/_nav-tabs.erb (Duration: 1.2ms | Allocations: 482)
17:16:53 web.1 | Doctor Count (21.8ms) SELECT COUNT(doctors.*, split_part(doctors.name, ' ', -1) AS lastname) FROM "doctors" WHERE "doctors"."user_id" = $1 [["user_id", 2]]
17:16:53 web.1 | ↳ app/views/doctors/index.html.erb:12
17:16:53 web.1 | Rendered doctors/index.html.erb within layouts/application (Duration: 83.8ms | Allocations: 17358)
17:16:53 web.1 | Rendered layout layouts/application.html.erb (Duration: 84.2ms | Allocations: 17477)
17:16:53 web.1 | Completed 500 Internal Server Error in 254ms (ActiveRecord: 42.9ms | Allocations: 54020)
17:16:53 web.1 |
17:16:53 web.1 |
17:16:53 web.1 |
17:16:53 web.1 | ActionView::Template::Error (PG::SyntaxError: ERROR: syntax error at or near "AS"
17:16:53 web.1 | LINE 1: ...OUNT(doctors.*, split_part(doctors.name, ' ', -1) AS lastnam...
17:16:53 web.1 | ^
17:16:53 web.1 | ):
17:16:53 web.1 | 9: </div>
17:16:53 web.1 | 10: <%= turbo_frame_tag Doctor.new %>
17:16:53 web.1 | 11: <%= turbo_frame_tag "doctors" do %>
17:16:53 web.1 | 12: <% if @doctors.count != 0 %>
17:16:53 web.1 | 13: <% @doctors.each do |doctor| %>
17:16:53 web.1 | 14: <%= render 'doctor_index', doctor: doctor %>
17:16:53 web.1 | 15: <% end %>
17:16:53 web.1 |
Running it in the console produces the results I'm looking for, which is a list of doctors records ordered by last name.
I'm sure it's a conversion problem from the Rails query to the psql query but I can't figure out how to rewrite it so that it works the way I want. I am confused why it works in the console but not the controller though.
The statement is:
SELECT COUNT(doctors.*, split_part(doctors.name, ' ', -1) AS lastname)
Where did the count come from? Let's look at the error message.
17:16:53 web.1 | 9: </div>
17:16:53 web.1 | 10: <%= turbo_frame_tag Doctor.new %>
17:16:53 web.1 | 11: <%= turbo_frame_tag "doctors" do %>
17:16:53 web.1 | 12: <% if @doctors.count != 0 %>
17:16:53 web.1 | 13: <% @doctors.each do |doctor| %>
17:16:53 web.1 | 14: <%= render 'doctor_index', doctor: doctor %>
17:16:53 web.1 | 15: <% end %>
It's from <% if @doctors.count != 0 %>. If select is used with count it will count the selected columns.
As you probably realized, there is no point to checking @doctors.count; if @doctors is empty @doctors.each will do nothing. The extra count query is an extra query and also introduces a race condition, in this particular case a probably inconsequential one.
You probably deleted the line locally, but did not save or deploy it.
If all you want to do is order by last name, you can use order directly and leave your selected columns alone.
current_user.doctors.order("split_part(doctors.name, ' ', -1) asc")
If you do this frequently, and there are many doctors, you'll want to do an indexed query. You could add an index on that particular expression...
create index doctor_lastname_idx on doctors(split_part(name, ' ', -1))
Or you could add it as a generated column. You could add a lastname column to the doctors table and do this in a before_save callback...
class Doctor < ApplicationRecord
...
before_save :set_lastname
private def set_lastname
self[:lastname] = name.rpartition(/\s+/).last
end
end
Or you can do it directly in the database with a real virtual column.
change_table :users do |t|
t.virtual :lastname, type: :string, as: 'split_part(doctors.name, ' ', -1)', stored: true
t.index(:lastname)
end