2

I am using the datatables gem to display a table that has two related tables included. As a result, it is pulling in and displaying columns from three different tables.

This is working fine, and I can even sort by the required columns. Unfortunately, the global search function is broken. It appears the SQL statement is not being formed correctly. It is not finding the table to do the where clause on.

The error is:

PG::UndefinedTable: ERROR: missing FROM-clause entry for table "items" LINE 1: SELECT COUNT() FROM "item_stores" WHERE ((CAST("items"."de... ^ : SELECT COUNT() FROM "item_stores" WHERE ((CAST("items"."description" AS VARCHAR) ILIKE '%b%' OR CAST("stores"."store_name" AS VARCHAR) ILIKE '%b%'))

The model I am using sits between the other two, with a many to 1 relationship to each: Items(1)-(M)ItemStores(M)-(1)Stores.

My raw data query is: 

def get_raw_records
  # insert query here
  ItemStore.includes([ :item, :store ]).all 
end

index.json.builder

json.item_stores @item_stores do |item_store|
  json.id item_stores.id
  json.item.image_path item_store.item.image_path
  json.store_id item_stores.store_id
  json.price item_stores.price

  json.items item_store.items do |item|
    json.(item, :description, :price, :image_path)
  end

  json.stores item_store.stores do |store|
    json.(store, :store_name)
  end

  json.url item_stores_url(i, format: :json)
end

I can't figure out what I can do to fix the underlying SQL attached to the global search...any help would be greatly appreciated. Thanks.

Radiodef
  • 37,180
  • 14
  • 90
  • 125
Lloydo
  • 303
  • 1
  • 8

1 Answers1

5

Answer found!

The query in get_raw_records should read:

ItemStore.includes( :item, :store ).references(:item, :store)

Lloydo
  • 303
  • 1
  • 8
  • Adding references seems to break it. Without `references` the page loads in under 2 seconds but throws the described error when trying to search. With the `references` it never loads. I've waited half an hour before. I'm wondering if anyone else has seen this – Brian H Mar 27 '19 at 16:00