CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Aravind
Aravind

Reputation: 1401

Unable to retrive information using where clause rails

So I have three Tables:

  1. User
  2. Customer
  3. Appointment

Customer has_many Appointments, Customer belongs_to User

I have a required where I have to retrieve Appointments based on the email of the customer. I have the email field in the User table.

I tried the following query:

Appointment.joins(:customer).where("customers.users.email ILIKE test")

gave me this error:

 Appointment Load (1.3ms)  SELECT "appointments".* FROM "appointments" INNER JOIN "customers" ON "customers"."id" = "appointments"."customer_id" WHERE (customers.users.email ILIKE test)
PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "users"
LINE 1: ...omers"."id" = "appointments"."customer_id" WHERE (customers....
                                                             ^
: SELECT "appointments".* FROM "appointments" INNER JOIN "customers" ON "customers"."id" = "appointments"."customer_id" WHERE (customers.users.email ILIKE test)

I tried adding this:

 belongs_to :customer, inverse_of: :appointments
 belongs_to :user, through: :customer

and also

has_one :customer_user, through: :customer

Obviously none of those things worked. I also tried this in customer model:

delegate :email, to: :user, prefix: true

and query:

User.joins(:customer).where("customers.user_email ILIKE test")

Throws the following error:

User Load (1.2ms)  SELECT "users".* FROM "users" INNER JOIN "customers" ON "customers"."user_id" = "users"."id" WHERE "users"."deleted_at" IS NULL AND "users"."active" = 't' AND (customers.user_email ILIKE test)
PG::UndefinedColumn: ERROR:  column customers.user_email does not exist
LINE 1: ...leted_at" IS NULL AND "users"."active" = 't' AND (customers....
                                                             ^
: SELECT "users".* FROM "users" INNER JOIN "customers" ON "customers"."user_id" = "users"."id" WHERE "users"."deleted_at" IS NULL AND "users"."active" = 't' AND (customers.user_email ILIKE test)

How do I fetch Appointments with a customer having a particular email id.

Upvotes: 0

Views: 115

Answers (1)

vee
vee

Reputation: 38645

In order to filter on users table, you need to include it in the join. Try:

Appointment.joins([customer: :user]).where("users.email ILIKE test")

This will create a join between appointments and customers and customers and users allowing you to filter on users table columns.

Upvotes: 3

Related Questions