Send this to a friend
having example in rails. from: http://blog.grayproductions.net/articles/five_activerecord_tips
1 duplicates = User.find( :all,
2 :select => "email, COUNT(email) AS duplicate_count",
3 :conditions => "email IS NOT NULL AND email != ''",
4 :group => "email HAVING duplicate_count > 1"
5 )
duplicates = User.find( :all,
:select => "email, COUNT(email) AS duplicate_count",
:conditions => "email IS NOT NULL AND email != ''",
:group => "email HAVING duplicate_count > 1"
)
Send this to a friend
from: http://www.mendable.com/sql-trick-where-are-your-users-from/
1 SELECT COUNT(*) AS Total, SUBSTRING_INDEX(email, '@', -1) AS Domain FROM users
2 GROUP BY SUBSTRING_INDEX(email, '@', -1) ORDER BY COUNT(*) DESC LIMIT 10;
SELECT COUNT(*) AS Total, SUBSTRING_INDEX(email, '@', -1) AS Domain FROM users
GROUP BY SUBSTRING_INDEX(email, '@', -1) ORDER BY COUNT(*) DESC LIMIT 10;
Send this to a friend
it’s a small how to import a file in migrations.
from: http://code-ronin.com/articles/rails-migrations-directly-import-sql
1
2
3 class ActiveRecord::ConnectionAdapters::MysqlAdapter
4 def import_sql(file)
5 conf = ActiveRecord::Base.configurations[RAILS_ENV]
6 sql_file = File.join(RAILS_ROOT, 'db', file + '.sql')
7 cmd_line = "mysql -h "+conf["host"]+" -D "+conf["database"]+ " --user="+conf["username"]+" --password="+conf["password"]+" < "+sql_file
8 raise Exception, "Error executing " + cmd_line unless system(cmd_line)
9 end
10 end
11
12
13
14
15 ./script/generate migration sql_external_test
16
17
18
19 class SqlExternalTest < ActiveRecord::Migration
20 def self.up
21 import_sql("test")
22 end
23
24 def self.down
25 end
26 end
27
class ActiveRecord::ConnectionAdapters::MysqlAdapter
def import_sql(file)
conf = ActiveRecord::Base.configurations[RAILS_ENV]
sql_file = File.join(RAILS_ROOT, 'db', file + '.sql')
cmd_line = "mysql -h "+conf["host"]+" -D "+conf["database"]+ " --user="+conf["username"]+" --password="+conf["password"]+" < "+sql_file
raise Exception, "Error executing " + cmd_line unless system(cmd_line)
end
end
./script/generate migration sql_external_test
class SqlExternalTest < ActiveRecord::Migration
def self.up
import_sql("test")
end
def self.down
end
end
Send this to a friend
1 ALTER TABLE tablename AUTO_INCREMENT = 1
ALTER TABLE tablename AUTO_INCREMENT = 1