Rails query working in console but not in the controller

Question

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.

Your view code does not match the view code in the error message. — Schwern
– Schwern, Commented Mar 16, 2024 at 23:36

Schwern · Accepted Answer · 2024-03-16 23:58:29Z

3

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

edited Mar 16, 2024 at 23:58

answered Mar 16, 2024 at 23:52

Schwern

167k28 gold badges225 silver badges370 bronze badges

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

2 Comments

Simon Perepelitsa Over a year ago

Alternatively, change @doctors.count to @doctors.length

Robert Reynolds Over a year ago

Thanks! I had changed the code locally but hadn't saved it so was still picking up the previous version. I needed a bit more caffeine that day!

Collectives™ on Stack Overflow

Rails query working in console but not in the controller

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related