mysql - 在事件记录 ruby 中连接表

Question

我有两个表，table1

id   name   age   occupation  ssn_id 
1    A      18    engineer    12
2    B      19    engineer    67
3    C      14    student     89
4    B      19    engineer    67

表2

id   ssn_id  limit   hours
1    67      2       20
2    12      2       17
3    89      2       78

扩展输出

ssn_id  limit  count(*)  occupation
67      2      2         engineer

SQL查询-

select id, occupation, count(*) as TOTAL, `limit` from table1*table2 where table1.ssn_id = table2.ssn_id and occupation = 'engineer' group by table1.ssn_id  having count(*) > `limit`

我想将此查询转换为 Rails Active Record 查询。我试过使用 include 但我无法获取第二个表的列。

Answer 1

如果您有表 1 和表 2 的模型，您可以使用这种方法:

 Table1.
   joins(:table2).
   select("table1.id, table1.occupation, count(*) as TOTAL, table2.limit").
   where(occupation: 'engineer').where("table1.ssn_id = table2.ssn_id").
   group(table1.ssn_id).
   having("count(*) > limit")