From 536aa3a1f4b96abc4ca34489bf2cbe503afcded7 Mon Sep 17 00:00:00 2001 From: GitLab Bot Date: Thu, 30 Jan 2020 15:09:15 +0000 Subject: Add latest changes from gitlab-org/gitlab@master --- doc/development/database_helpers.md | 63 ------------------------------------- 1 file changed, 63 deletions(-) delete mode 100644 doc/development/database_helpers.md (limited to 'doc/development/database_helpers.md') diff --git a/doc/development/database_helpers.md b/doc/development/database_helpers.md deleted file mode 100644 index 21e4e725de6..00000000000 --- a/doc/development/database_helpers.md +++ /dev/null @@ -1,63 +0,0 @@ -# Database helpers - -There are a number of useful helper modules defined in `/lib/gitlab/database/`. - -## Subquery - -In some cases it is not possible to perform an operation on a query. -For example: - -```ruby -Geo::EventLog.where('id < 100').limit(10).delete_all -``` - -Will give this error: - -> ActiveRecord::ActiveRecordError: delete_all doesn't support limit - -One solution would be to wrap it in another `where`: - -```ruby -Geo::EventLog.where(id: Geo::EventLog.where('id < 100').limit(10)).delete_all -``` - -This works with PostgreSQL, but with MySQL it gives this error: - -> ActiveRecord::StatementInvalid: Mysql2::Error: This version of MySQL -> doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' - -Also, that query doesn't have very good performance. Using a -`INNER JOIN` with itself is better. - -So instead of this query: - -```sql -SELECT geo_event_log.* -FROM geo_event_log -WHERE geo_event_log.id IN - (SELECT geo_event_log.id - FROM geo_event_log - WHERE (id < 100) - LIMIT 10) -``` - -It's better to write: - -```sql -SELECT geo_event_log.* -FROM geo_event_log -INNER JOIN - (SELECT geo_event_log.* - FROM geo_event_log - WHERE (id < 100) - LIMIT 10) t2 ON geo_event_log.id = t2.id -``` - -And this is where `Gitlab::Database::Subquery.self_join` can help -you. So you can rewrite the above statement as: - -```ruby -Gitlab::Database::Subquery.self_join(Geo::EventLog.where('id < 100').limit(10)).delete_all -``` - -And this also works with MySQL, so you don't need to worry about that. -- cgit v1.2.1