Ruby Sequel Subquery Happening Before Main Query

Question

Can someone help me out with this?

I need the following sql code translated to sequel:

SELECT DISTINCT sales_leads.employee_person_id,
    (SELECT full_name from persons 
        WHERE sales_leads.employee_person_id=persons.id),
    (SELECT COUNT (sales_leads.id) from sales_leads 
        WHERE sales_leads.employee_person_id=employees.person_id 
        AND sales_leads.status='open') AS open_leads_count,
    (SELECT COUNT (sales_leads.id) from sales_leads 
        WHERE sales_leads.employee_person_id=employees.person_id 
        AND sales_leads.status='lost' OR sales_leads.status='won') AS closed_leads_count
FROM sales_leads
JOIN employees ON sales_leads.employee_person_id=employees.person_id
ORDER BY sales_leads.employee_person_id;

This is what I have so far:

DB[:sales_leads]
  .inner_join(:persons, id: :employee_person_id)
  .select(:full_name)
  .select_append(
    DB[:sales_leads]
      .select(:id)
      .where(Sequel[:sales_leads][:employee_person_id]=>Sequel[:persons][:id])
      .count
  ).all

But I am getting an error:

PG::UndefinedTable: ERROR: missing FROM-clause entry for table "persons" LINE 1: ...eads" WHERE ("sales_leads"."employee_person_id" = "persons".... ^: SELECT count(*) AS "count" FROM "sales_leads" WHERE ("sales_leads"."employee_person_id" = "persons"."id") LIMIT 1 Sequel::DatabaseError: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "persons" LINE 1: ...eads" WHERE ("sales_leads"."employee_person_id" = "persons".... ^ from /home/john/.rbenv/versions/2.5.0/lib/ruby/gems/2.5.0/gems/sequel-5.5.0/lib/sequel/adapters/postgres.rb:145:in `async_exec'

Answer 1

You are calling Dataset#count on the dataset you are passing to the select_append call, and Dataset#count runs a query. You probably want .select{count(sales_leads[:id])} instead of .count.