Class: RuboCop::Cop::Isucon::Mysql2::ManyJoinTable

Inherits:

Base

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

Includes:

RuboCop::Cop::Isucon::Mixin::ManyJoinTableMethods, RuboCop::Cop::Isucon::Mixin::Mysql2XqueryMethods

Defined in:

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

Overview

Check if SQL contains many JOINs

Examples:

CountTables: 3 (default)

# bad
totals = db.xquery(
  "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.xquery("SELECT COUNT(`user_id`) AS cnt FROM `registrations` WHERE `course_id` = ?", course[:id]).first[:cnt]

totals = db.xquery(<<~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.xquery(
  "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 RuboCop::Cop::Isucon::Mixin::ManyJoinTableMethods

RuboCop::Cop::Isucon::Mixin::ManyJoinTableMethods::MSG

Constants included from RuboCop::Cop::Isucon::Mixin::Mysql2XqueryMethods

RuboCop::Cop::Isucon::Mixin::Mysql2XqueryMethods::NON_STRING_WARNING_MSG

Method Summary

Methods included from RuboCop::Cop::Isucon::Mixin::ManyJoinTableMethods

#on_send

Methods included from RuboCop::Cop::Isucon::Mixin::Mysql2XqueryMethods

#find_xquery, #with_db_query

Methods included from RuboCop::Cop::Isucon::Mixin::OffenceLocationMethods

#offense_location