Rails SQL query optimization

Question

I have a featured section in my website that contains featured posts of three types: normal, big and small. Currently I am fetching the three types in three separate queries like so:

@featured_big_first = Post.visible.where(pinged: 1).where('overlay_type =?', :big).limit(1)
@featured_big_first = Post.visible.where(pinged: 1).where('overlay_type =?', :small).limit(1)
@featured_big_first = Post.visible.where(pinged: 1).where('overlay_type =?', :normal).limit(5)

Basically I am looking for a query that will combine those three in to one and fetch 1 big, 1 small, 5 normal posts.

Marlin Pierce · Accepted Answer · 2012-09-21 13:17:03Z

I'd be surprised if you don't want an order. As you have it, it is supposed to find a random small, random large, and 5 random normal.

Yes, you can use a UNION. However, you will have to do an execute SQL. Look at the log for the SQL for each of your three queries, and do an execute SQL of a string which is each of the three queries with UNION in between. It might work, or it might have problems with the limit.

It is possible in SQL by joining the table to itself, doing a group by on one of the aliases for the table, a where when the other aliased table is <= the group by table, and adding a having clause where count of the <= table is under the limit.

So, if you had a simple query of the posts table (without the visible and pinged conditions) and wanted the records with the latest created_at date, then the normal query would be:

SELECT posts1.*
FROM posts posts1, posts posts2
WHERE posts2.created_at >= posts1.create_at
  AND posts1.overlay_type = 'normal'
  AND posts2.overlay_type = 'normal'
GROUP BY posts1.id
HAVING count(posts2.id) <= 5

Take this SQL, and add your conditions for visible and pinged, remembering to use the condition for both posts1 and posts2.

Then write the big and small versions and UNION it all together.

I'd stick with the three database calls.

Salil · Accepted Answer · 2012-09-20 08:50:12Z

1

I don't think this is possible but you can use scope which is more rails way to write a code

Also it may just typo but you are reassigning the @featured_big_first so it will contain the data of the last query only

in post.rb

  scope :overlay_type_record lamda{|type| joins(:visible).where(["visible.pinged=1 AND visible.overlay_type =", type])}

and in controller

@featured_big_first = Post.overlay_type_record(:big).limit(1)
@featured_small_first = Post.overlay_type_record(:small).limit(1)
@featured_normal_first = Post.overlay_type_record(:normal).limit(5)

edited Sep 20, 2012 at 8:50

answered Sep 20, 2012 at 8:40

Salil

47.7k22 gold badges127 silver badges161 bronze badges

1 Comment

zevstatiev Over a year ago

Thanks for the suggestion, I will implement it. I will wait a bit before accept your answer, b/c I believe it's possible with a custom sql query with UNION.

Collectives™ on Stack Overflow

Rails SQL query optimization

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