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

Question

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>

MrYoshiji · Accepted Answer · 2017-09-04T14:43:26.987

14

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' })

edited Sep 04 '17 at 14:43

answered Sep 04 '17 at 14:41

MrYoshiji

54,334
13
124
117

Thanks for that information! Now i know. this works great – WQ.Kevin Sep 04 '17 at 14:43

Sebastián Palma · Answer 2 · 2017-09-04T14:42:30.193

1

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

edited Sep 04 '17 at 14:42

answered Sep 04 '17 at 14:34

Sebastián Palma

32,692
6
40
59

Can you add an 's' to params[:user][:university]. so it's like params[:users][:university]. because that works! thank you :) – WQ.Kevin Sep 04 '17 at 14:37
1

try this `Book.joins(:user).where("books.title = ? and users.university = ?" , params[:book][:title], params[:users][:university]).uniq` – Pardeep Saini Sep 04 '17 at 14:38
1

The `where` method needs to receive the *exact table name*, it may not be the plural version of the relation. If the `User` record is linked to the table named `utilisateurs` (french), then in the where you would use `Book.joins(:user).where(utilisateurs: { name: 'bob' })` – MrYoshiji Sep 04 '17 at 14:38

score 0 · Answer 3 · answered Oct 30 '19 at 13:01

0

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

answered Oct 30 '19 at 13:01

Kiryl Plyashkevich

2,157
19
18

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

3 Answers3