CODEWITHSUNDEEP
ruby-on-railsjquery-datatables-rails
Lloydo
Lloydo

Reputation: 313

Missing FROM-clause with DataTables Global Search

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.

Upvotes: 2

Views: 435

Answers (1)

Lloydo
Lloydo

Reputation: 313

Answer found!

The query in get_raw_records should read:

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

Upvotes: 5

Related Questions