Heroku join/includes not working like sqlite dev environment

Question

So this is what I have in my dev (SQLite) environment that works perfectly:

key = "%#{keywords}%"
listings = Listing.order(:headline)
listings = listings.includes(:neighborhood).where("headline like ? or neighborhoods.name like ?", key, key) if keywords.present?

But, when I try something similar on Heroku, I get a number of errors:

listings = listings.includes(:neighborhood).where("headline like ? or neighborhoods.name like ?", key, key)
ActiveRecord::ConfigurationError: Association named 'neighborhoods' was not found; perhaps you misspelled it?

I also tried the following & got the corresponding error:

> listings = Listing.includes(:neighborhood).where("headline like :keywords or neighborhood.name like :keywords", :keywords => "%#{keywords}%")
  SQL (3.1ms)  SELECT "listings"."id" AS t0_r0, "listings"."listing_category_id" AS t0_r1, "listings"."listing_type_id" AS t0_r2, "listings"."user_id" AS t0_r3, "listings"."boro_id" AS t0_r4, "listings"."neighborhood_id" AS t0_r5, "listings"."building_id" AS t0_r6, "listings"."term_id" AS t0_r7, "listings"."headline" AS t0_r8, "listings"."property_type_id" AS t0_r9, "listings"."unit_num" AS t0_r10, "listings"."common_charges" AS t0_r11, "listings"."taxes" AS t0_r12, "listings"."maintenance" AS t0_r13, "listings"."num_bedrooms" AS t0_r14, "listings"."num_bathrooms" AS t0_r15, "listings"."square_footage" AS t0_r16, "listings"."list_square_footage" AS t0_r17, "listings"."description" AS t0_r18, "listings"."condition_id" AS t0_r19, "listings"."pet_policy_id" AS t0_r20, "listings"."exposure_id" AS t0_r21, "listings"."floor_plan" AS t0_r22, "listings"."photo" AS t0_r23, "listings"."security" AS t0_r24, "listings"."coop_deductible" AS t0_r25, "listings"."flip_tax" AS t0_r26, "listings"."house_families" AS t0_r27, "listings"."house_extensions" AS t0_r28, "listings"."house_stories" AS t0_r29, "listings"."house_units" AS t0_r30, "listings"."created_at" AS t0_r31, "listings"."updated_at" AS t0_r32, "listings"."price" AS t0_r33, "neighborhoods"."id" AS t1_r0, "neighborhoods"."name" AS t1_r1, "neighborhoods"."created_at" AS t1_r2, "neighborhoods"."updated_at" AS t1_r3, "neighborhoods"."boro_id" AS t1_r4 FROM "listings" LEFT OUTER JOIN "neighborhoods" ON "neighborhoods"."id" = "listings"."neighborhood_id" WHERE (headline like '%prospect heights%' or neighborhood.name like '%prospect heights%')
ActiveRecord::StatementInvalid: PG::Error: ERROR:  missing FROM-clause entry for table "neighborhood"
LINE 1: ..._id" WHERE (headline like '%prospect heights%' or neighborho...
                                                             ^
: SELECT "listings"."id" AS t0_r0, "listings"."listing_category_id" AS t0_r1, "listings"."listing_type_id" AS t0_r2, "listings"."user_id" AS t0_r3, "listings"."boro_id" AS t0_r4, "listings"."neighborhood_id" AS t0_r5, "listings"."building_id" AS t0_r6, "listings"."term_id" AS t0_r7, "listings"."headline" AS t0_r8, "listings"."property_type_id" AS t0_r9, "listings"."unit_num" AS t0_r10, "listings"."common_charges" AS t0_r11, "listings"."taxes" AS t0_r12, "listings"."maintenance" AS t0_r13, "listings"."num_bedrooms" AS t0_r14, "listings"."num_bathrooms" AS t0_r15, "listings"."square_footage" AS t0_r16, "listings"."list_square_footage" AS t0_r17, "listings"."description" AS t0_r18, "listings"."condition_id" AS t0_r19, "listings"."pet_policy_id" AS t0_r20, "listings"."exposure_id" AS t0_r21, "listings"."floor_plan" AS t0_r22, "listings"."photo" AS t0_r23, "listings"."security" AS t0_r24, "listings"."coop_deductible" AS t0_r25, "listings"."flip_tax" AS t0_r26, "listings"."house_families" AS t0_r27, "listings"."house_extensions" AS t0_r28, "listings"."house_stories" AS t0_r29, "listings"."house_units" AS t0_r30, "listings"."created_at" AS t0_r31, "listings"."updated_at" AS t0_r32, "listings"."price" AS t0_r33, "neighborhoods"."id" AS t1_r0, "neighborhoods"."name" AS t1_r1, "neighborhoods"."created_at" AS t1_r2, "neighborhoods"."updated_at" AS t1_r3, "neighborhoods"."boro_id" AS t1_r4 FROM "listings" LEFT OUTER JOIN "neighborhoods" ON "neighborhoods"."id" = "listings"."neighborhood_id" WHERE (headline like '%prospect heights%' or neighborhood.name like '%prospect heights%')

I also tried and got the following error:

0> listings = Listing.joins(:neighborhood).where("headline like :keywords or neighborhood.name like :keywords", :keywords => "%#{keywords}%")
  Listing Load (2.7ms)  SELECT "listings".* FROM "listings" INNER JOIN "neighborhoods" ON "neighborhoods"."id" = "listings"."neighborhood_id" WHERE (headline like '%prospect heights%' or neighborhood.name like '%prospect heights%')
ActiveRecord::StatementInvalid: PG::Error: ERROR:  missing FROM-clause entry for table "neighborhood"
LINE 1: ..._id" WHERE (headline like '%prospect heights%' or neighborho...
                                                             ^
: SELECT "listings".* FROM "listings" INNER JOIN "neighborhoods" ON "neighborhoods"."id" = "listings"."neighborhood_id" WHERE (headline like '%prospect heights%' or neighborhood.name like '%prospect heights%')

Thoughts?

Thanks.

Edit 1:

A listing belongs_to neighborhood, and neighborhood has_many listings.

Edit 2:

Extracted one of the SQL statements and ran it via psql, this is the result:

SELECT "listings".* FROM "listings" INNER JOIN "neighborhoods" ON "neighborhoods"."id" = "listings"."neighborhood_id" WHERE (headline like '%prospect heights%' or neighborhood.name like '%prospect heights%');                                                                                                                                                                 
ERROR:  missing FROM-clause entry for table "neighborhood"
LINE 1: ..._id" WHERE (headline like '%prospect heights%' or neighborho...

Looks like you didn't include "neightborhoods" in your "FROM" clause, but the thing is virtually unreadable. Extract actual SQL query, run it via psql and then paste the query and exact error. — , Feb 11 '13 at 14:39
Not quite sure how to run it via psql, but I can clean it up. Thought the other stuff would help. Actually....the long-ass query is the exact query. That's the SQL that was run...which is what I am not understanding. — marcamillion, Feb 11 '13 at 14:40
You can run it via `psql` by running `heroku pg:psql -a app_name` first – that will give you a `psql` connection to your database on Heroku — catsby, Feb 11 '13 at 14:45

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

Try the below:

listings = Listing.includes(:neighborhood).where("listings.headline like :keywords or neighborhoods.name like :keywords", :keywords => "%#{keywords}%")

I think it should call listings.headline instead of headline

SELECT "listings".* FROM "listings" INNER JOIN "neighborhoods"   
ON "neighborhoods"."id" = "listings"."neighborhood_id"  
WHERE (
headline like '%prospect heights%'               #I mean here
or neighborhood.name like '%prospect heights%')  #also here, should be neighborhoods.name

EDIT - do you know that there are some difference between SQLite and PostgreSQL? I faced a similar problem. I think you want ILIKE instead of LIKE:

listings = Listing.includes(:neighborhood).where("listings.headline ilike :keywords or neighborhoods.name ilike :keywords", :keywords => "%#{keywords}%")

edited May 23 '17 at 12:12

Community

1
1

answered Feb 11 '13 at 14:49

gabrielhilal

10,660
6
54
81

Nope...still getting that `FROM-clause entry` error I put above. – marcamillion Feb 11 '13 at 14:57
have you put `neighborhoods.name`? – gabrielhilal Feb 11 '13 at 14:58
Ok...when I try `neighborhoods.name`, I get an empty result: `Listing.includes(:neighborhood).where("listings.headline like :keywords or neighborhoods.name like :keywords", :keywords => "%#{keywords} SQL (2.5ms) SELECT "listings"."id" AS "neighborhoods"."id" AS t1_r0, "neighborhoods"."name" AS t1_r1 FROM "listings" LEFT OUTER JOIN "neighborhoods" ON "neighborhoods"."id" = "listings"."neighborhood_id" WHERE (listings.headline like '%prospect heights%' or neighborhoods.name like '%prospect heights%') => []` Which is not the result I am expecting. – marcamillion Feb 11 '13 at 15:04
I guess the question here is, will that query return listings with `neighborhood.name = "Prospect Heights"` as well as `prospect heights`? If not, then perhaps this query is correct. – marcamillion Feb 11 '13 at 15:07
What's the difference between `ilike` and `like` with respect to SQLite and PGSQL? – marcamillion Feb 11 '13 at 15:11
your model is `neighborhood`, but the table is `neighborhoods`. When it goes to sql you must use the table name. Take a look in the difference between ilike and like. – gabrielhilal Feb 11 '13 at 15:11
Should `ilike` work in SQLite? I just tried it and got an error and am wondering if the error is related to that? – marcamillion Feb 11 '13 at 15:13
ilike does not work in SQLite :( I recommend you to instal postgreSQL in you computer as well. You will avoid many compatibility issues if you have the same environment for development and production. – gabrielhilal Feb 11 '13 at 15:16
I never had much need for postgreSQL locally before, but now that I am running more complex queries I just might have to. Is it difficult to get installed locally on OSX? i.e. is it as "nice" to install as say ImageMagick or something like that? – marcamillion Feb 11 '13 at 15:19
I faced many issues to get it working. The easier way is through homebrew. – gabrielhilal Feb 11 '13 at 15:21

score 1 · Answer 2 · answered Feb 11 '13 at 14:54

Reformatted query:

SELECT "listings"."id" AS t0_r0,
       "listings"."listing_category_id" AS t0_r1,
       "listings"."listing_type_id" AS t0_r2,
       "listings"."user_id" AS t0_r3,
       "listings"."boro_id" AS t0_r4,
       "listings"."neighborhood_id" AS t0_r5,
       "listings"."building_id" AS t0_r6,
       "listings"."term_id" AS t0_r7,
       "listings"."headline" AS t0_r8,
       "listings"."property_type_id" AS t0_r9,
       "listings"."unit_num" AS t0_r10,
       "listings"."common_charges" AS t0_r11,
       "listings"."taxes" AS t0_r12,
       "listings"."maintenance" AS t0_r13,
       "listings"."num_bedrooms" AS t0_r14,
       "listings"."num_bathrooms" AS t0_r15,
       "listings"."square_footage" AS t0_r16,
       "listings"."list_square_footage" AS t0_r17,
       "listings"."description" AS t0_r18,
       "listings"."condition_id" AS t0_r19,
       "listings"."pet_policy_id" AS t0_r20,
       "listings"."exposure_id" AS t0_r21,
       "listings"."floor_plan" AS t0_r22,
       "listings"."photo" AS t0_r23,
       "listings"."security" AS t0_r24,
       "listings"."coop_deductible" AS t0_r25,
       "listings"."flip_tax" AS t0_r26,
       "listings"."house_families" AS t0_r27,
       "listings"."house_extensions" AS t0_r28,
       "listings"."house_stories" AS t0_r29,
       "listings"."house_units" AS t0_r30,
       "listings"."created_at" AS t0_r31,
       "listings"."updated_at" AS t0_r32,
       "listings"."price" AS t0_r33,
       "neighborhoods"."id" AS t1_r0,
       "neighborhoods"."name" AS t1_r1,
       "neighborhoods"."created_at" AS t1_r2,
       "neighborhoods"."updated_at" AS t1_r3,
       "neighborhoods"."boro_id" AS t1_r4
FROM "listings"
LEFT OUTER JOIN "neighborhoods" ON "neighborhoods"."id" = "listings"."neighborhood_id"
WHERE (headline LIKE '%prospect heights%'
       OR neighborhood.name LIKE '%prospect heights%');

Problem seems obvious. You include table "neighborhoods", but later on are trying to use "neighborhood". How to fix that in RoR - no idea, but perhaps it will be enough to point you in right direction.

I have tried both `neighborhoods` and `neighborhood` and it gives me different variations of the error. In fact, the first two errors were generated from `neighborhoods.name` whereas the second two errors were generated from `neighborhood.name`. — marcamillion, Feb 11 '13 at 14:56
As I requested earlier - please provide actual queries, formatted that it will be readable, and respective errors. Single line query of 300 characters or more - is not readable. — , Feb 11 '13 at 15:04

score 1 · Answer 3 · answered Feb 11 '13 at 17:56

1

The best way to diagnose this is to make sure your development and production environments match as closely as possible, and actually run PostgreSQL locally. Inevitably, you will encounter inconsistencies between SQLite and PostgreSQL that are difficult to debug, or will surprise you when you push your code.

If you're on a Mac, Heroku Postgres has a standalone application you can download and use with no configuration: http://postgresapp.com/

answered Feb 11 '13 at 17:56

Dominic

3,304
19
22

1

Thanks for this. This is what I was looking for. Going through Homebrew and all that stuff would be too much for my needs. – marcamillion Feb 11 '13 at 23:10
No problem. That's exactly what it's made for :) – Dominic Feb 12 '13 at 00:03

Heroku join/includes not working like sqlite dev environment

3 Answers3

Linked