Convert Mysql Query to Rails ActiveRecord Query Without using find_by_sql

Question

I have table named questions like follows

+----+---------------------------------------------------------+----------+
| id | title                                                   | category |
+----+---------------------------------------------------------+----------+
| 89 | Tinker or work with your hands?                         |        2 |
| 54 | Sketch, draw, paint?                                    |        3 |
| 53 | Express yourself clearly?                               |        4 |
| 77 | Keep accurate records?                                  |        6 |
| 32 | Efficient?                                              |        6 |
| 52 | Make original crafts, dinners, school or work projects? |        3 |
| 70 | Be elected to office or make your opinions heard?       |        5 |
| 78 | Take photographs?                                       |        3 |
| 84 | Start your own political campaign?                      |        5 |
|  9 | Free spirit or a rebel?                                 |        3 |
| 38 | Lead a group?                                           |        5 |
| 71 | Work in groups?                                         |        4 |
|  2 | Helpful?                                                |        4 |
|  4 | Mechanical?                                             |        6 |
| 14 | Responsible?                                            |        6 |
| 66 | Pitch a tent, an idea?                                  |        1 |
| 62 | Write useful business letters?                          |        5 |
| 28 | Creative?                                               |        3 |
| 68 | Perform experiments?                                    |        2 |
| 10 | Like to figure things out?                              |        2 |
+----+---------------------------------------------------------+----------+

I have a sql query to get one random record from each category.Can any one convert the mysql query to rails activerecord query(with out using Question.find_by_sql).This mysql query is working absolutely fine but I need only active record query because of my dependency in further steps.

Here is mysql query

             SELECT t.id, title as question, category
                FROM
              (
                SELECT 
                (
                  SELECT id
                    FROM questions
                   WHERE category = t.category
                   ORDER BY RAND()
                   LIMIT 1
                ) id
                  FROM questions t
                 GROUP BY category
              ) q JOIN questions t
                  ON q.id = t.id

Thank You for your consideration!

Answer 1

When things get crazy one have to reach out for Arel:

It is intended to be a framework framework; that is, you can build your own ORM with it, focusing on innovative object and collection modeling as opposed to database compatibility and query generation.

So what we want to do is to let Arel create the query for us. Moreover the approach here is gonna be used: the questions table is left joined with randomized version of itself:

q_normal = Arel::Table.new("questions")
q_random = Arel::Table.new("questions").project(Arel.sql("*")).order("RAND()").as("q2")

Time to left join

query = q_normal.join(q_random, Arel::Nodes::OuterJoin).on(q_normal[:category].eq(q_random[:category])).group(q_normal[:category]).order(q_random[:category])

Now you can use which columns you want using project, e.g.:

query.project(q_normal[:id])