Looking for non-existent table with combination of search fields

[sha1sum]

It seems that, given a specific set of models and relationships, when a combination of fields are searched together, a PG::UndefinedTable error occurs. The two fields can be searched separately and work fine.

The model being searched is Lead. Here is schema and model data for that model and its related model, Customer, as well as the Person (users, basically) schema for good measure:

# ------------------- schema -----------------------

create_table "leads", force: :cascade do |t|
  t.boolean "active", default: true, null: false
  t.integer "customer_id", null: false
  t.text "comments"
  t.datetime "created_at", null: false
  t.date "follow_up_by"
  t.boolean "internet", default: false, null: false
  t.boolean "ok_to_call_and_text", default: false, null: false
  t.boolean "phone", default: false, null: false
  t.boolean "security", default: false, null: false
  t.boolean "tv", default: false, null: false
  t.datetime "updated_at", null: false
end

create_table "customers", force: :cascade do |t|
  t.text "comments"
  t.datetime "created_at", null: false
  t.string "first_name", null: false
  t.string "last_name", null: false
  t.integer "location_id", null: false
  t.string "mobile_phone"
  t.string "other_phone"
  t.integer "person_id", null: false
  t.datetime "updated_at", null: false
end

create_table "people", force: :cascade do |t|
  t.boolean "active", default: true, null: false
  t.datetime "created_at"
  t.string "display_name", null: false
  t.string "email", null: false
  t.string "first_name", null: false
  t.string "home_phone"
  t.string "last_name", null: false
  t.string "mobile_phone"
  t.string "office_phone"
  t.datetime "updated_at"
end

# ------------------- models -----------------------
class Lead < ActiveRecord::Base
  validates :customer, presence: true
  validate :one_service_selected
  validate :no_past_follow_up_by_date
  validate :must_be_ok_to_call_and_text

  belongs_to :customer

  default_scope {
    joins(:customer).
    order(:follow_up_by)

  }

  private

  def no_past_follow_up_by_date
    return unless self.follow_up_by
    if self.follow_up_by.to_date <= Date.current and not self.persisted?
      errors.add(:follow_up_by, 'must be in the future')
    end
  end

  def must_be_ok_to_call_and_text
    unless self.ok_to_call_and_text?
      errors.add(:ok_to_call_and_text, 'must be checked to save as a lead')
    end
  end
end

class Customer < ActiveRecord::Base
  validates :first_name, presence: true
  validates :last_name, presence: true
  validates :person, presence: true
  validates :location, presence: true
  validates_with CustomerPhoneValidator

  nilify_blanks

  belongs_to :person # Person has_many :leads
  belongs_to :location
  has_one :lead
  has_one :sale
  has_many :customer_notes
end

... and here is the partial with the search form:

- content_for :header do
  = link_to icon('magnifying-glass'), '#', id: 'show_search'

  = search_form_for @search, id: 'search_form' do |f|
    = link_to "×".html_safe, '#', id: 'hide_search'
    = link_to 'Clear', request.path if params[:q]
    = f.select :active_eq, [['Active', true],['Dismissed', false]], include_blank: 'All'
    = f.text_field :follow_up_by_gteq, placeholder: 'F/U on/after'
    = f.text_field :follow_up_by_lteq, placeholder: 'F/U on/before'
    = f.text_field :customer_person_display_name_cont, placeholder: 'Rep Name'
    = f.text_field :customer_first_name_cont, placeholder: 'Cust. first name'
    = f.text_field :customer_last_name_cont, placeholder: 'Cust. last name'
    = f.submit 'search', class: 'button'
  - if @leads
    .top-pagination
      = paginate @leads

... and the controller action and corresponding Pundit policy (just in case, but I don't want to believe it's a scoping issue):

class LeadsController < ApplicationController
  after_action :verify_authorized, only: [:new, :create, :index] # Pundit
  after_action :verify_policy_scoped, only: [:index, :csv] # Pundit

  def index
    @search = policy_scope(Lead).search(params[:q])
    @leads = @search.result.page(params[:page])
    authorize Lead.new # Pundit
  end
end

class LeadPolicy < ApplicationPolicy
  class Scope < Struct.new(:person, :scope)
    def resolve
      customers = CustomerPolicy::Scope.new(self.person, Customer).resolve
      if customers.empty?
        scope.none
      else
        scope.where('leads.customer_id IN (?)', [customers.ids].flatten)
      end
    end
  end
end

With this structure, one cannot search for both the customer_person_display_name_cont and either of the customer_first_name_cont or customer_last_name_cont fields. So, for example, I cannot search customer.person.display_name and customer.first_name together. The following is an example of the exception that is raised (line breaks and indentation added to SQL for convenience):

An ActionView::Template::Error occurred in leads#index:

  PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "customers_leads"
LINE 1: ...ople"."display_name" ILIKE '%Bob%' AND "c...
                                                             ^
: SELECT 

COUNT(DISTINCT "leads"."id") 

FROM "leads" 
INNER JOIN "customers" 
  ON "customers"."id" = "leads"."customer_id" 
LEFT OUTER JOIN "people" 
  ON "people"."id" = "customers"."person_id" 

WHERE 
  (customers.person_id IN (16436)) 
  AND ("people"."display_name" ILIKE '%Bob%' 
    AND "customers_leads"."first_name" ILIKE '%derrick%')

All of the fields work if used alone.

There are other things in these files as well, which I've left out because they don't seem to me to be related, but I have not made a new Rails application using only the above information, so if the entire files are needed I can provide them after cleaning confidential information out of them such as company names (our clients).

The customers_leads seems to be similar to the naming of a join table (even though a join table would not be searched directly), but I have no idea where that table name came from because it does not exist.

I was using 1.6.2, but after experiencing the problem I also tried using the gem straight from git with the same results.

[durhamka]

+1 on this issue.

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "users_mission_responses"
LINE 1: ..." WHERE (("companies"."name" ILIKE '%GoSpot%' AND "users_mis...
                                                             ^
: SELECT COUNT(DISTINCT count_column) FROM (SELECT  "mission_responses"."id" AS count_column FROM "mission_responses" INNER JOIN "campaigns" ON "campaigns"."id" = "mission_responses"."campaign_id" INNER JOIN "places" ON "places"."id" = "mission_responses"."place_id" INNER JOIN "users" ON "users"."id" = "mission_responses"."user_id" LEFT OUTER JOIN "companies" ON "companies"."id" = "campaigns"."company_id" WHERE (("companies"."name" ILIKE '%GoSpot%' AND "users_mission_responses"."email" ILIKE '%kinsey%')) LIMIT 30 OFFSET 0) subquery_for_count

[richardrails]

+1

[fabienpiette]

+1

I work with Globalize gem.

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

Controller:

def index
    @q = SuperModel.unscoped.with_translations(I18n.locale).order(created_at: :desc).ransack params[:q]

    @super_models = @q.result.includes(:type).page(params[:page]).per(params[:per_page])
  end

Models:

class Category < ActiveRecord::Base
  translates :name, :atelier_name, :slug
  globalize_accessors

  default_scope -> {
    with_translations(I18n.locale).order(:position).where("category_translations.slug != ''")
  }
[...]
end

class SuperModel < ActiveRecord::Base
  translates :name, :description, :slug, :meta_description, :meta_title
  globalize_accessors

  belongs_to :category, # -> { with_translations(I18n.locale) },
    foreign_key: 'category_catalog_id',
    primary_key: 'catalog_id'
[...]
end

I obtain this

SELECT  DISTINCT "public"."super_models".* 
FROM "public"."super_models" 
INNER JOIN "super_model_translations" ON "super_model_translations"."super_model_id" = "public"."super_models"."id" 
LEFT OUTER JOIN "public"."categories" ON "public"."categories"."catalog_id" = "public"."super_models"."category_catalog_id" 
AND "category_translations"."locale" = 'en'
WHERE "super_model_translations"."locale" = 'en' 

While I should have this (this request works console there):

SELECT "public"."super_models".* 
FROM "public"."super_models" 
INNER JOIN "super_model_translations" ON "super_model_translations"."super_model_id" = "public"."super_models"."id" 
LEFT OUTER JOIN "public"."categories" ON "public"."categories"."catalog_id" = "public"."super_models"."category_catalog_id" 
INNER JOIN "category_translations" ON "category_translations"."category_id" = "public"."categories"."id"
AND "category_translations"."locale" = 'en'
WHERE "oxylane_super_model_translations"."locale" = 'en' 

A joint for the table category_translations is missing

[theSociableme]

+1

552 is another example of this.... https://github.com/activerecord-hackery/ransack/issues/552

[jonatack]

I believe this is a duplicate of previous issues and may be linked to how your model queries/relations are written/named/used, or perhaps to issues with active record which has been heavily refactored this past year for 4.2 and 5.0 and work is ongoing.

This is free, open-source software and everyone can improve it (if it is really an issue with Ransack) :heart: Pull requests always welcome!

[jpmermoz]

OK, I've found the error (at least in my case)

This "extra table error" appeared if you mix "joins" and "includes" statements in the same relation. In my case I had something like this on my controller:

@company_phones = @company.company_phones.includes(:phone).mobile_phones.ransack(params[:q])

And in my CompanyPhone class I had this:

scope :mobile_phones, -> { joins(:phone).where(phones: { type: "MobilePhone" }) }

If you remove the "joins" from the model, or replace it with another "includes", it will work.

[richardrails]

yes it works with outer join (which does includes method), but it does not solve the problem, because I need inner join

[theSociableme]

@jpmermoz #552 issue happens when clicking sort link.

How can I remove the "Joins" in that situation?

[jpmermoz]

@theSociableme just use joins in one place, not both. For example:

In model:

scope :mobile_phones, -> { joins(:phone).where(phones: { type: "MobilePhone" }) }

In controller: (not necessary to do joins again since its already inside the scope)

@company_phones = @company.company_phones.mobile_phones.ransack(params[:q])

[nunosilva800]

Similar situation here. I've avoided the problem by specifying ransacks individually:

  def search(collection)
    results = collection
    params[:q].map { |k,v| results = results.ransack(k => v).result }
    results
  end

---

This is what I could figure out:

> ModelA.joins(:modelb).search(name_cont: '1', modelb_address_cont: 'address').result.count

Fails with ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column

Removing the inner join:

> ModelA.search(name_cont: '1', modelb_address_cont: 'address').result.count

Works fine, but I need it for more complex queries, so....

Changing the order of the ransacks:

> ModelA.joins(:modelb).search(modelb_address_cont: 'address', name_cont: '1').result.count

All good!

One ransack at a time:

> ModelA.joins(:modelb).search(modelb_address_cont: 'address').result
                       .search(name_cont: '1').result
                       .count

Also works!

(ransack (1.8.2))