Combining data from two different queries

[Karl Baum]

On our website we track visits and signups for each of our campaigns.  Visit is a unique visit to the site and a visit is associated with a signup if that user signs up.  I created a table for visits:

>> @table1 = Signup.report_table_by_sql "select count(*),c.code from signups s,campaigns c where s.campaign_id=c.id and s.user_id is not null group by code"

+---------------------+
| code | signup_count |
+---------------------+
| abc  | 50           |
| dfg  | 50           |
+---------------------+


>> @table2 = Visit.report_table_by_sql "select count(*),c.code from visits v,campaigns c where v.campaign_id=c.id group by code"
>> @table2.rename_column("count(*)", 'visit_count')

+--------------------+
| code | visit_count |
+--------------------+
| abc  | 300         |
| dfg  | 300         |
+--------------------+

What I would really like is to combine these two results into one table with code, signup_count, visit_count, and conversion rate.  

Thx.

-karl

[Andrew France]

Hi Karl,

Have you tried using SQL sub-selects? I can never remember the syntax
without looking it up but it's something like:
SELECT signup.code, signup.count, visit.count FROM (SELECT count(*),
c.code FROM signups s......) AS signup JOIN (SELECT ....) AS visit ON
visit.code = signup.code;

You can do this via either model, I don't think it cares what attributes
are returned!

Hope that helps!

Andrew

[Karl Baum]

Hi Andrew. I ended up using a slightly different approach. Instead of
building the table directly from the SQL statement, I created a table
manually using methods on my ActiveRecord objects. It ended up being much
simpler this way. I think I was headed down the wrong path with my question
below.

Thanks for your help.

-karl