Class: RuboCop::Cop::Isucon::Sqlite3::ManyJoinTable

Inherits:

Base

• Object
• Base
• RuboCop::Cop::Isucon::Sqlite3::ManyJoinTable

Includes:

Mixin::ManyJoinTableMethods, Mixin::Sqlite3ExecuteMethods

Defined in:

lib/rubocop/cop/isucon/sqlite3/many_join_table.rb

Overview

Check if SQL contains many JOINs

Examples:

CountTables: 3 (default)

# bad
totals = db.execute(
  "SELECT IFNULL(SUM(`submissions`.`score`), 0) AS `total_score`" \
  " FROM `users`" \
  " JOIN `registrations` ON `users`.`id` = `registrations`.`user_id`" \
  " JOIN `courses` ON `registrations`.`course_id` = `courses`.`id`" \
  " LEFT JOIN `classes` ON `courses`.`id` = `classes`.`course_id`" \
  " LEFT JOIN `submissions` ON `users`.`id` = `submissions`.`user_id` AND `submissions`.`class_id` = `classes`.`id`" \
  " WHERE `courses`.`id` = ?" \
  " GROUP BY `users`.`id`",
  [course[:id]]
).map { |_| _[:total_score] }

# good
registration_users_count =
  db.execute("SELECT COUNT(`user_id`) AS cnt FROM `registrations` WHERE `course_id` = ?", [course[:id]]).first[:cnt]

totals = db.execute(<<~SQL, course[:id]).map { |_| _[:total_score] }
  SELECT IFNULL(SUM(`submissions`.`score`), 0) AS `total_score`
  FROM `submissions`
  JOIN `classes` ON `classes`.`id` = `submissions`.`class_id`
  WHERE `classes`.`course_id` = ?
  GROUP BY `submissions`.`user_id`
SQL

if totals.count < registration_users_count
  no_submissions_count = registration_users_count - totals.count
  totals += [0] * no_submissions_count
end

CountTables: 5

# good
totals = db.execute(
  "SELECT IFNULL(SUM(`submissions`.`score`), 0) AS `total_score`" \
  " FROM `users`" \
  " JOIN `registrations` ON `users`.`id` = `registrations`.`user_id`" \
  " JOIN `courses` ON `registrations`.`course_id` = `courses`.`id`" \
  " LEFT JOIN `classes` ON `courses`.`id` = `classes`.`course_id`" \
  " LEFT JOIN `submissions` ON `users`.`id` = `submissions`.`user_id` AND `submissions`.`class_id` = `classes`.`id`" \
  " WHERE `courses`.`id` = ?" \
  " GROUP BY `users`.`id`",
  [course[:id]]
).map { |_| _[:total_score] }

Constant Summary

Constants included from Mixin::ManyJoinTableMethods

Mixin::ManyJoinTableMethods::MSG

Constants included from Mixin::Sqlite3ExecuteMethods

Mixin::Sqlite3ExecuteMethods::NON_STRING_WARNING_MSG

Method Summary

Methods included from Mixin::ManyJoinTableMethods

#on_send

Methods included from Mixin::Sqlite3ExecuteMethods

#find_xquery, #with_db_query

Methods included from Mixin::OffenceLocationMethods

#offense_location