Querying through a join table Rails 4 Postgres

Question

I am trying to get an array of records through a join table. I want to find All of one User's Favorites that are Blue, but that are "current". User_Favorites can expire. This is what I'm trying:

User.rb

has_many :user_favorites, dependent: :destroy
has_many :favorites, through: :user_favorites

Favorite.rb

has_many :user_favorites, dependent: :destroy
has_many :users, through: :user_favorites

UserFavorite.rb

belongs_to :user
belongs_to :favorite

 scope :current_as_of, -> (date) do
    where('start_date <= ?',date).
    where('(end_date >= ? or end_date IS NULL)', date)
  end

  scope :blue, -> { where('self.favorite.color = ?','blue') }

class UsersController < ApplicationController

def profile
 @user = User.find(params[:id])
 @blue_favorites = @user.user_favorites.current_as_of(Date.today).blue.all
end

This is the error I get:

There is an Error: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "favorite"
LINE 1: ...d_date >= '2015-10-06' or end_date IS NULL)) AND (Favorite.co...
                                                             ^
: SELECT "user_favorites".* FROM "user_favorites" WHERE "user_favorites"."user_id" = $1 AND (start_date <= '2015-10-06') AND ((end_date >= '2015-10-06' or end_date IS NULL)) AND (Favorite.color = 'blue')

Paul Richter · Accepted Answer · 2015-10-06 22:08:17Z

2

in regards to this:

  scope :blue, -> { where('self.favorite.color = ?','blue') }

It looks like you're mixing up database and ruby syntax. The other problem too is that the query, at this point, has no idea what favorite is because it hasn't been joined yet. Try something like this instead:

  scope :blue, -> { joins(:favorite).where(favorites: {color: 'blue'}) }

edited Oct 6, 2015 at 22:08

answered Oct 6, 2015 at 19:34

Paul Richter

11.1k10 gold badges56 silver badges89 bronze badges

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

2 Comments

NothingToSeeHere Over a year ago

I am getting this error now "There is an Error: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "favorite" LINE 1: ...id" WHERE "user_favorites"."user_id" = $1 AND "favorite"."... ^ : SELECT "user_favorites".* FROM "user_favorites" INNER JOIN "favorites" ON "favorites"."id" = "user_favorites"."favorite_id" WHERE "user_favorites"."user_id" = $1 AND "favorite"."color" = 'blue'"

Paul Richter Over a year ago

@NothingToSeeHere Sorry I had a typo. In there where statement, it should be favorites (plural), because in the where, you're referencing the actual table's name (which is always plural in rails). Updated answer.

IngoAlbers · Accepted Answer · 2015-10-06 19:34:33Z

1

If I understand it correctly your :blue scope should look something like this:

scope :blue, -> { joins(:favorite).where(favorites: { color: 'blue' }) }

In the joins you have to use the association name while in the where clause you have to use the table name.

answered Oct 6, 2015 at 19:34

IngoAlbers

5,8026 gold badges33 silver badges47 bronze badges

2 Comments

NothingToSeeHere Over a year ago

I am getting this error now "There is an Error: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "favorite" LINE 1: ...id" WHERE "user_favorites"."user_id" = $1 AND "favorite"."... ^ : SELECT "user_favorites".* FROM "user_favorites" INNER JOIN "favorites" ON "favorites"."id" = "user_favorites"."favorite_id" WHERE "user_favorites"."user_id" = $1 AND "favorite"."color" = 'blue'"

IngoAlbers Over a year ago

Are you sure you are using the tablename in the where clause?

Collectives™ on Stack Overflow

Querying through a join table Rails 4 Postgres

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related