gistfile1.rb

dealers = Dealer.arel_table
sales_reps = SalesRep.arel_table
dealer_counts = dealers.
                group(dealers[:sales_rep_id]).
                where(dealers[:deleted_at].eq(nil)).
                where(dealers[:ended].eq(nil)).
                project(dealers[:sales_rep_id], dealers[:id].count.as('value'))

def left_outer_join(subselect,as,on)
 result = Arel.sql("")
 result << "LEFT OUTER JOIN ("
 result << subselect.to_sql
 result << ") AS "
 result << as
 result << " ON "
 result << on
 result
end

join_sql = left_outer_join(dealer_counts, 'dealer_counts', Arel.sql('"dealer_counts"."sales_rep_id" = "sales_reps"."id"'))

sales_reps.join(join_sql).project(sales_reps[:fname], Arel.sql('"dealer_counts"."value"'))

# SELECT
#  "sales_reps"."fname", "dealer_counts"."value" FROM "sales_reps" LEFT OUTER JOIN (SELECT "dealers"."sales_rep_id", COUNT("dealers"."id") AS value FROM "dealers" WHERE #"dealers"."deleted_at" IS NULL AND "dealers"."ended" IS NULL GROUP BY "dealers"."sales_rep_id") AS dealer_counts ON "dealer_counts"."sales_rep_id" = "sales_reps"."id";



sales_reps.join()

#----------------------

dealers = Arel::Table.new(:dealers)
sales_reps = Arel::Table.new(:sales_reps)

#  photo_counts = photos.
#  group(photos[:user_id]).
#  project(photos[:user_id], photos[:id].count)
dealer_counts = dealers.group(dealers[:sales_rep_id]).project(dealers[:sales_rep_id], dealers[:id].count.as('value'))

#  users.join(photo_counts).on(users[:id].eq(photo_counts[:user_id]))
sales_reps.join(dealer_counts).on(sales_reps[:id].eq(dealer_counts[:sales_rep_id]))


SalesRep.find_by_sql(sales_reps.join(Arel.sql("LEFT OUTER JOIN (" + dealer_counts.to_sql + ") AS dealer_counts ON dealer_counts.sales_rep_id = sales_reps.id")).project(sales_reps[:id],Arel.sql('dealer_counts.value')).to_sql)
#
# SELECT 
#   "sales_reps"."id",
#   "dealer_counts"."value" 
# FROM 
#   "sales_reps" 
# LEFT OUTER JOIN 
#   (SELECT
#      "dealers"."sales_rep_id",
#      COUNT("dealers"."id") AS value 
#    FROM 
#      "dealers" 
#    GROUP BY 
#      "dealers"."sales_rep_id")
# AS
#  dealer_counts
# ON
#  dealer_counts.sales_rep_id = sales_reps.id