Using sub-queries to avoid multiple DB requests in Rails

Posted by Dmytro Shteflyuk on under Ruby & Rails

When I worked on Best Tech Videos with Alexey Kovyrin, we faced a problem of filtering videos by category with selecting posts categories in the same query. It was easy to solve the problem, but there is one query optimization trick exists.

For example, we are developing blogging software. We have Post and Category model, where post could have one or more categories (many-to-many relation):

1
2
3
4
5
6
7
class Post < ActiveRecord::Base
  has_and_belongs_to_many :categories
end

class Category < ActiveRecord::Base
  has_and_belongs_to_many :posts
end

We need to display posts on the page along with categories for each one. The simplest way is to do find with :include:

1
Post.find :all, :include => :categories

And corresponding helper:

1
2
3
def show_categories(post)
  post.categories.map(&:name).join(' ')
end

But what if you need to filter off posts by category? Here is example:

1
2
3
Post.find :all,
          :include => :categories,
          :conditions => ['categories.id = ?', category_id]

It works, but there is one small trouble: you would get not all categories! Now we could fix it in helper:

1
2
3
4
def show_categories(post)
  # reload categories
  post.categories(true).map(&:name).join(' ')
end

In this case categories for all posts would be requested in separate queries. It’s not so good, therefor I propose to use sub-query:

1
2
3
Post.find :all,
          :include => :categories,
          :conditions => ['EXISTS (SELECT tmp_cp.category_id FROM categories_posts tmp_cp WHERE posts.id = tmp_cp.post_id AND tmp_cp.category_id = ?)', category_id]

Now posts would be filtered by category and all categories of posts would be loaded properly. Do you have other ideas?

8 Responses to this entry

Subscribe to comments with RSS

Alex
said on May 16th, 2007 at 11:49 · Permalink

Вопрос чисто из любопытства :)
Какой язык используется в этом примере и почему нельзя было использовать классический SQL запрос с оператором JOIN?

said on May 16th, 2007 at 12:04 · Permalink

Этот таинственный язык — Ruby. Данная заметка посвящена использованию Ruby on Rails (фрейморка), в частности Active Record (ORM этого фреймворка).

Твой воспрос не особо корректен. Это то же самое, что спросить “Зачем использовать ПХП и почему нельзя использовать классический ассемблер?” (без обид, ничего личного) :-)

Active Record берет на себя все сложности (и рутину) составления типовых SQL-запросов, а также конструирует объекты (сущности) вашего приложения из результатов запроса. В общем много всяких вкусностей, настоятельно рекомендую хотя бы посмотреть (скринкаст можно найти на официальном сайте Ruby on Rails) или почитать что-нибудь об этом фреймворке, даже если не собираетесь его использовать.

Alex
said on May 16th, 2007 at 13:56 · Permalink

Обижаться конечно же не стану, не для того вопрос задал.
Обязательно попытаюсь узнать что такое Ruby и с чем эти вкусности едят :).
Ну а по поводу сложности и рутины – не зная как устроены эти самые классы (их ведь писали люди для упрощения каких-то своих задач) не сможешь понять как они работают. Я вот смотрю на эти запросы и со своим приверженством чтоли к языкам так-называемого “среднего уровня”, не понимаю чего хотел программист получить, без обид :).
Приятно конечно осознать, что SQL у кого-то ассоциируется с ассемблером, но все же SQL также написан для людей (не как ассемблер – словесное представление машинных кодов) и надеюсь, что Active Record (как всеже дополнительное звено) не является помехой для конструирования гибких запросов к базе данных.
Не сочти за критику, это лишь мое первое представление, обещаю ознакомиться с Active Record.

said on May 16th, 2007 at 14:18 · Permalink

Честно говоря, Вы можете не осознавать всю работу ORM фреймворка для того, чтобы работать с ним. К тому же вопрос о гибких запросов к базе данных должен подыматься только в случае необходимости. В большинстве случаем мы делаем большую ошибку, начиная думать об оптимизации.

Только после нескольких предварительных релизов и более менее установлении архитектуры, можно думать об оптимизации SQL запросов. А так получается – еще ничего не сделали, зато уже хотим оптимизировать.

Кстати, как с кешированием выборок в RoR?

said on May 16th, 2007 at 14:23 · Permalink

Ты все переставил местами :-) SQL ассоциируется с ассемблером, как Ruby ассоциируется с PHP. Эдакая пропорция. Может немного преувеличено, но смысл такой.

Я не вижу преимущества у следующего кода (PHP, для простоты, именно то, что я назвал “ассемблером”)

1
2
3
4
5
6
7
8
9
10
11
$rs = $db->fetch_all('SELECT v.id AS v_id, v.title AS v_title, c.id AS c_id, c.name AS c_name FROM videos v JOIN categories_videos cv ON v.id = cv.video_id JOIN categories c ON c.id = cv.category_id LIMIT 20')
$old_post_id = 0;
$posts = array();
foreach ($rs as $row) {
  if ($old_post_id != $row['v_id']) {
    $post = array('id' => $row['v_id'], 'title' => $row['v_title'], 'categories' => array());
    $posts[] = $post;
    $old_post_id = $post['id'];
  }
  $post['categories'][] = array('id' => $row['c_id'], 'name' => $row['c_name']);
}

(код я не проверял, скорее всего не работает) перед кодом на руби с использованием рельсов:

1
Video.find :all, :include => :categories, :limit => 20

Да, не надо меня пинать на тему, что под пхп есть куча фреймворком и мой код не имеет смысла. Просто попробуйте руби и вы не сможете остановиться.

ЗЫ. Приведенный код имеет несколько проблем. Во-первых, я выбрал не все поля (у меня в таблице их десяток), во-вторых использовал LIMIT в SQL, что нельзя использовать в других СУБД, потому еще надо долепить условий. Ну и никаких WHERE тоже не приводил, потому что надоело писать.

said on May 16th, 2007 at 14:34 · Permalink

2Roman:

1
2
3
4
5
6
7
8
9
10
# Выбираем видео
@video = Video.find :first
# Загружаем категории отдельным запросом
@video.categories
# Здесь категории юзаются уже загруженные
# дополнительного запроса нет
@video.categories
# Здесь форсируем загрузку категорий
# повторый запрос к базе
@video.categories(true)

Ответил?

said on May 16th, 2007 at 14:43 · Permalink

Мог бы просто ответить, что кеширование есть :). К чему какие подробности.

Comments are closed

Comments for this entry are closed for a while. If you have anything to say – use a contact form. Thank you for your patience.