CODEWITHSUNDEEP
ruby-on-railsjoin
WQ.Kevin
WQ.Kevin

Reputation: 319

PG::UndefinedTable: ERROR: missing FROM-clause — Searching with two models Rails

I have a searchbar on my landing page where I can search for books from users from different universities. It doesn't seem to accept the .joins when it gets redirected to the Book Index Page. Book belongs to user and user has many books.

I always get:

PG::UndefinedTable: ERROR: missing FROM-clause entry for table "user"

BooksController.rb

def index
  if params[:book][:title].present? && params[:users][:university].present?
  @books = Book.where({title: params[:book][:title]})
  .joins(:user).where(user: {university: params[:users][:university]}).uniq
  end
end

PagesController.rb

def home
  @books = Book.new
end

And this is my search in simple_form:

<%= simple_form_for [@books], url: books_path, method: :get do |f| %>
  <ul class="list-inline">
    <li><%= f.input :title, placeholder: "Title", label: false %></li>
    <%= simple_fields_for :users do |r| %>
      <li><%= r.input :university, placeholder: "University", label: false %></li>
    <% end %>
  </ul>
    <%= f.button :submit, 'search', class: "btn"  %>
<% end %>

routes.rb

resources :books do
  resources :users
end

Full error is:

LINE 1: ... "books"."user_id" WHERE "books"."title" = $1 AND "user"."un... ^ : SELECT DISTINCT "books".* FROM "books" INNER JOIN "users" ON "users"."id" = "books"."user_id" WHERE "books"."title" = $1 AND "user"."university" = $2>

Upvotes: 6

Views: 5332

Answers (3)

Kiryl Plyashkevich
Kiryl Plyashkevich

Reputation: 2277

Make sure to provide .includes(:user) before filtering with where to avoid ERROR: missing FROM-clause entry for table

Upvotes: 0

MrYoshiji
MrYoshiji

Reputation: 54882

The where method expects to receive the exact table name (see full example here: How to query a model based on attribute of another model which belongs to the first model?):

@books = Book.where({title: params[:book][:title]})
@books = @books.joins(:user).where(users: {university: params[:users][:university]}).uniq
#                     ^ relation name  ^ exact name of the table

If, for some reason, the name of the table storing the User records was named utilisateurs, then the where usage would be:

@books = Book.joins(:user).where(utilisateurs: { name: 'Bob' })

Upvotes: 14

Sebasti&#225;n Palma
Sebasti&#225;n Palma

Reputation: 33430

Try using the plural form of the relationship with user to query by the university, like:

Book.where('title = ?', params[:book][:title])
    .joins(:user)
    .where(users: { university: params[:users][:university] }).uniq

Upvotes: 1

Related Questions