Rails Having trouble re-writing one last DB query for FILTERtrak that was originally written using a-CodePudding

I have been re-writing and upgrading an application called FILTERtrak. The old version is up and running on Heroku right now. It was on Rails v4.2.1, I have been upgrading it to Rails v6.1.5. I have everything working except one last DB query which I thought I had it working but upon doing some QA it is not working.

This is the original if statement I am having trouble with in its original squeel form: It is in a method called quick_search but the specific part I am having trouble with is this specific IF statement and specifically the ELSE part of this is where I am struggling. It has an additional check to see if a Company has public_records: true:
public_records is a Boolean DB field on Company.

If the company is the parent company then the check to see if public_records: true is checked is skipped, this is the IF section. The ELSE section is for any other company that isn't the parent company and as a result the public_records: true needs to be follwed.

The relationship between Companies and customers is: Company has_many Customers Customers belongs_to a Company.

if current_user.company.parent_company
      @q = Customer.joins{ company }.where{ ( company_id.eq my{ current_user.company_id } )
      | ( company.vendor_id.eq my{ current_user.company.vendor_id } ) }.order("company_name desc")
    else
      @q = Customer.joins{ company }.where{ ( company_id.eq my{ current_user.company_id } )
      | ( ( company.vendor_id.eq my{ current_user.company.vendor_id } ) & ( company.public_records.eq true ) ) }.order("company_name desc")
    end

Here is the current SQL from the working older version of FILTERtrak with squeel as it is coming out of Heroku:

2022-08-03T18:45:39.233025 00:00 app[web.1]: Started POST "/customers/quick_search" for 66.189.160.205 at 2022-08-03 18:45:39  0000
2022-08-03T18:45:39.234971 00:00 app[web.1]: Processing by CustomersController#quick_search as JS
2022-08-03T18:45:39.234990 00:00 app[web.1]: Parameters: {"utf8"=>"✓", "q"=>{"company_name_cont"=>"Potato"}, "commit"=>"Search"}
2022-08-03T18:45:39.237044 00:00 app[web.1]: User Load (0.7ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = $1  ORDER BY "users"."id" ASC LIMIT 1  [["id", 362]]
2022-08-03T18:45:39.238955 00:00 app[web.1]: Company Load (0.6ms)  SELECT  "companies".* FROM "companies" WHERE "companies"."id" = $1 LIMIT 1  [["id", 180]]
2022-08-03T18:45:39.256546 00:00 app[web.1]:  (9.7ms)  SELECT COUNT(count_column) FROM (SELECT  1 AS count_column FROM "customers" INNER JOIN "companies" ON "companies"."id" = "customers"."company_id" WHERE "customers"."company_name" ILIKE '%Potato%' AND ("customers"."company_id" = 180 OR ("companies"."vendor_id" = 24 AND "companies"."public_records" = 't')) LIMIT 8) subquery_for_count
2022-08-03T18:45:39.262671 00:00 app[web.1]: Customer Load (5.6ms)  SELECT  "customers".* FROM "customers" INNER JOIN "companies" ON "companies"."id" = "customers"."company_id" WHERE "customers"."company_name" ILIKE '%Potato%' AND ("customers"."company_id" = 180 OR ("companies"."vendor_id" = 24 AND "companies"."public_records" = 't'))  ORDER BY company_name desc LIMIT 8

Here is my re-write of this same if statement. The top section works fine on the IF area it is the ELSE area I am having a problem with. I am pretty sure it is a problem of syntax because I have to also check to see if the Companies have public_records: true. If I remove the .AND line that has the public_records: true it will return my result. However if I search for a more common string it will return other companies results because it is not checking to see if the other companies customers have the public_records: true or not. It shouldn't show me other Companies customers UNLESS they specifically check a box to allow their data to be shared. I should only be getting the customer back with the name that belongs to my company. I somehow am not getting the syntax right to communicate this.

if current_user.company.parent_company
      #logger.debug "\n\nIN: if current_user.company.parent_company METHOD\n\n"
      @q = Customer.joins(:company).where(company_id: {current_user: :company_id })
        .or(Customer.joins(:company).where(company: {vendor_id: current_user.company.vendor_id}))
        .order(company_name: :desc)
           
    else
      #logger.debug "\n\nIN: ELSE of if current_user.company.parent_company METHOD\n\n"
      @q = Customer.joins(:company).where(company_id: {current_user: :company_id })
        .or(Customer.joins(:company).where(company: {vendor_id: current_user.company.vendor_id}))
        .and(Customer.joins(:company).where(company: {public_records: true}))
        .order(company_name: :desc)
    end

It doesn't give any errors it just isn't returning the results.
Here is the localhost console for the same search term Potato

IN: ELSE of if current_user.company.parent_company METHOD


  Rendering customers/index.js.coffee
  Customer Exists? (5.7ms)  SELECT 1 AS one FROM "customers" INNER JOIN "companies" "company" ON "company"."id" = "customers"."company_id" WHERE ("customers"."company_id" = $1 OR "company"."vendor_id" = $2) AND "company"."public_records" = $3 AND "customers"."company_name" ILIKE '%Potato%' LIMIT $4 /*line:/app/views/customers/index.js.coffee:4:in `_app_views_customers_index_js_coffee__807791716_681440'*/  [["company_id", nil], ["vendor_id", 1], ["public_records", true], ["LIMIT", 1]]
  ↳ app/views/customers/index.js.coffee:4
  Customer Load (2.0ms)  SELECT "customers".* FROM "customers" INNER JOIN "companies" "company" ON "company"."id" = "customers"."company_id" WHERE ("customers"."company_id" = $1 OR "company"."vendor_id" = $2) AND "company"."public_records" = $3 AND "customers"."company_name" ILIKE '%Potato%' ORDER BY "customers"."company_name" DESC LIMIT $4 /*line:/app/views/customers/index.js.coffee:5:in `_app_views_customers_index_js_coffee__807791716_681440'*/  [["company_id", nil], ["vendor_id", 1], ["public_records", true], ["LIMIT", 8]]
  ↳ app/views/customers/index.js.coffee:5
  Rendered collection of customers/_mini.html.haml [0 times] (Duration: 0.0ms | Allocations: 11)
  Rendered customers/index.js.coffee (Duration: 195.1ms | Allocations: 6951)
Completed 200 OK in 478ms (Views: 189.9ms | ActiveRecord: 33.4ms | Allocations: 59729)

Here is the localhost console for the same search term Potato IF I REMOVE the .and with the public_records: true line.

IN: ELSE of if current_user.company.parent_company METHOD


  Rendering customers/index.js.coffee
  Customer Exists? (11.8ms)  SELECT 1 AS one FROM "customers" INNER JOIN "companies" "company" ON "company"."id" = "customers"."company_id" WHERE ("customers"."company_id" = $1 OR "company"."vendor_id" = $2) AND "customers"."company_name" ILIKE '%Potato%' LIMIT $3 /*line:/app/views/customers/index.js.coffee:4:in `_app_views_customers_index_js_coffee__807791716_681440'*/  [["company_id", nil], ["vendor_id", 1], ["LIMIT", 1]]
  ↳ app/views/customers/index.js.coffee:4
  Customer Load (11.8ms)  SELECT "customers".* FROM "customers" INNER JOIN "companies" "company" ON "company"."id" = "customers"."company_id" WHERE ("customers"."company_id" = $1 OR "company"."vendor_id" = $2) AND "customers"."company_name" ILIKE '%Potato%' LIMIT $3 /*line:/app/views/customers/index.js.coffee:5:in `_app_views_customers_index_js_coffee__807791716_681440'*/  [["company_id", nil], ["vendor_id", 1], ["LIMIT", 8]]
  ↳ app/views/customers/index.js.coffee:5
  Rendered collection of customers/_mini.html.haml [1 times] (Duration: 1.0ms | Allocations: 130)
  Rendered customers/index.js.coffee (Duration: 249.4ms | Allocations: 12971)
Completed 200 OK in 418ms (Views: 225.9ms | ActiveRecord: 54.5ms | Allocations: 64628)

This is what it looks like in the app when it returns the results:

I also tried re-writing it like this:

if current_user.company.parent_company
      logger.debug "\n\nIN: if current_user.company.parent_company METHOD\n\n"
      @q = Customer.joins(:company).where(company_id: {current_user: :company_id })
        .or(Customer.joins(:company).where(company: {vendor_id: current_user.company.vendor_id}))
        .order(company_name: :desc)
           
    else
      logger.debug "\n\nIN: ELSE of if current_user.company.parent_company METHOD\n\n"
      @q = Customer.joins(:company).where(company_id: {current_user: :company_id })
         .or(Customer.joins(:company).where(company: {vendor_id: current_user.company.vendor_id}))
         .and(Company.where(company: {public_records: true}))#.order(company_name: :desc))
    end

Again I am not getting the results back.

Here is the console from this version:

IN: ELSE of if current_user.company.parent_company METHOD


  Rendering customers/index.js.coffee
  Customer Exists? (2.6ms)  SELECT 1 AS one FROM "customers" INNER JOIN "companies" "company" ON "company"."id" = "customers"."company_id" WHERE ("customers"."company_id" = $1 OR "company"."vendor_id" = $2) AND "company"."public_records" = $3 AND "customers"."company_name" ILIKE '%Potato%' LIMIT $4 /*line:/app/views/customers/index.js.coffee:4:in `_app_views_customers_index_js_coffee__807791716_681440'*/  [["company_id", nil], ["vendor_id", 1], ["public_records", true], ["LIMIT", 1]]
  ↳ app/views/customers/index.js.coffee:4
  Customer Load (2.1ms)  SELECT "customers".* FROM "customers" INNER JOIN "companies" "company" ON "company"."id" = "customers"."company_id" WHERE ("customers"."company_id" = $1 OR "company"."vendor_id" = $2) AND "company"."public_records" = $3 AND "customers"."company_name" ILIKE '%Potato%' LIMIT $4 /*line:/app/views/customers/index.js.coffee:5:in `_app_views_customers_index_js_coffee__807791716_681440'*/  [["company_id", nil], ["vendor_id", 1], ["public_records", true], ["LIMIT", 8]]
  ↳ app/views/customers/index.js.coffee:5
  Rendered collection of customers/_mini.html.haml [0 times] (Duration: 0.1ms | Allocations: 11)
  Rendered customers/index.js.coffee (Duration: 203.8ms | Allocations: 6944)
Completed 200 OK in 368ms (Views: 201.7ms | ActiveRecord: 39.5ms | Allocations: 58973)

Then I also tried this:

if current_user.company.parent_company
      logger.debug "\n\nIN: if current_user.company.parent_company METHOD\n\n"
      @q = Customer.joins(:company).where(company_id: {current_user: :company_id })
        .or(Customer.joins(:company).where(company: {vendor_id: current_user.company.vendor_id}))
        .order(company_name: :desc)
           
    else
      logger.debug "\n\nIN: ELSE of if current_user.company.parent_company METHOD\n\n"
      @q = Customer.joins(:company).where(company_id: {current_user: :company_id })
         .or(Customer.joins(:company).where(company: {vendor_id: current_user.company.vendor_id, public_records: true}))
         .order(company_name: :desc)
    end

And I got again no error but also didn't get my returned customer back: Here is the console from this attempt:

IN: ELSE of if current_user.company.parent_company METHOD


  Rendering customers/index.js.coffee
  Customer Exists? (5.9ms)  SELECT 1 AS one FROM "customers" INNER JOIN "companies" "company" ON "company"."id" = "customers"."company_id" WHERE ("customers"."company_id" = $1 OR "company"."vendor_id" = $2 AND "company"."public_records" = $3) AND "customers"."company_name" ILIKE '%Potato%' LIMIT $4 /*line:/app/views/customers/index.js.coffee:4:in `_app_views_customers_index_js_coffee__807791716_681440'*/  [["company_id", nil], ["vendor_id", 1], ["public_records", true], ["LIMIT", 1]]
  ↳ app/views/customers/index.js.coffee:4
  Customer Load (1.9ms)  SELECT "customers".* FROM "customers" INNER JOIN "companies" "company" ON "company"."id" = "customers"."company_id" WHERE ("customers"."company_id" = $1 OR "company"."vendor_id" = $2 AND "company"."public_records" = $3) AND "customers"."company_name" ILIKE '%Potato%' ORDER BY "customers"."company_name" DESC LIMIT $4 /*line:/app/views/customers/index.js.coffee:5:in `_app_views_customers_index_js_coffee__807791716_681440'*/  [["company_id", nil], ["vendor_id", 1], ["public_records", true], ["LIMIT", 8]]
  ↳ app/views/customers/index.js.coffee:5
  Rendered collection of customers/_mini.html.haml [0 times] (Duration: 0.1ms | Allocations: 11)
  Rendered customers/index.js.coffee (Duration: 190.2ms | Allocations: 6954)
Completed 200 OK in 424ms (Views: 185.0ms | ActiveRecord: 31.8ms | Allocations: 59578)

In case anyone thinks it would help here is the entire quick_search method. It uses ransaq to do a search, the Job search works, the filter ID search works the company name part is where it is failing.

def quick_search
    accessible_params = current_user.accessible_params(controller_name, params)

    # if parent company, ignore pool privacy

    if current_user.company.parent_company
      logger.debug "\n\nIN: if current_user.company.parent_company METHOD\n\n"
      @q = Customer.joins(:company).where(company_id: {current_user: :company_id })
         .or(Customer.joins(:company).where(company: {vendor_id: current_user.company.vendor_id}))
         .order(company_name: :desc)

    else
      logger.debug "\n\nIN: ELSE of if current_user.company.parent_company METHOD\n\n"
      @q = Customer.joins(:company).where(company_id: {current_user: :company_id })
         .or(Customer.joins(:company).where(company: {vendor_id: current_user.company.vendor_id}))
         .and(Company.where(company: {public_records: true})).order(company_name: :desc))
    end

    if params[:q].blank?
      @q = @q.none.ransack
    else
      @q = @q.ransack(accessible_params)
    end

    @customers = @q.result.limit(8)

    render :index
  end

I am almost positive it is just a syntax error on my part trying to search for the customer while also checking the public_records: true.

If Anyone has ANY suggestions I would be very appreciative. If anyone wants me to add anything else please let me know. I have learned to try to keep it as to the point as possible.

I managed to re-write literally about 30 different DB Queries but a few of them I have had trouble with, this one on the ELSE is causing me some grief.

Thank You, Scott

CodePudding user response：

Thanks to @Mike Szyndel who noticed my issue was on the TOP of my else statement and not the bottom. I had been troubleshooting the bottom section because if I used only the TOP section it worked (more or less).

It is now working properly and it is properly checking the public_records: true Boolean.

Here is the ending DB Query on the ELSE side:

else  
@q = Customer.joins(:company)
     .where(company_id: current_user.company_id) 
     .or(Customer.joins(:company)
     .where(company: {vendor_id: current_user.company.vendor_id, public_records: true}))

Again a big THANK YOU to Mike Szyndel!!