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!

Appreciated your response.I have n categories and I need a question from each category — Gowri Naidu R
– Gowri Naidu R, Commented Apr 18, 2014 at 12:24

Yan Foto · Accepted Answer · 2015-01-24 09:26:08Z

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])

answered Jan 24, 2015 at 9:26

Yan Foto

11.5k6 gold badges61 silver badges98 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Slicedpan · Accepted Answer · 2014-04-18 12:56:45Z

0

The only way I can think of to do this requires a good bit of application code. I don't think there's a way of accessing the RAND() functionality in MySQL (or equivalent in other DB technologies) using ActiveRecord. Here's what I came up with:

counts = Question.group(:category_id).count(:id)
offsets = {}
counts.each do |cat_id, count|
  offsets[cat_id] = rand(count)
end
random_questions = []
offsets.each do |cat_id, offset|
  random_questions.push(Question.where(:category_id => cat_id).offset(offset).first)
end

answered Apr 18, 2014 at 12:56

Slicedpan

5,0172 gold badges20 silver badges33 bronze badges

1 Comment

Gowri Naidu R Over a year ago

Thanks for your response . But this is not satisfied my requirements. Actually I want that mysql query to convert active record query with out using find_by_sql . Please refer once again my question . Finally We need only active record query .

Collectives™ on Stack Overflow

Convert Mysql Query to Rails ActiveRecord Query Without using find_by_sql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related