you are in: codestacker → codes [RSS] → tag: sql [RSS]

finding duplicates entries Delicious Email

having example in rails. from: http://blog.grayproductions.net/articles/five_activerecord_tips

show/hide lines
   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  )
created by leozera — 18 July 2009 — get a short url — tags: activerecord rails sql — embed

count domain users using sql Delicious Email

from: http://www.mendable.com/sql-trick-where-are-your-users-from/

show/hide lines
   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;
created by leozera — 12 July 2009 — get a short url — tags: mysql sql — embed

import sql files into migrations Delicious Email

it’s a small how to import a file in migrations.

from: http://code-ronin.com/articles/rails-migrations-directly-import-sql

show/hide lines
   1  # first, create the method import_sql:
   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  # create a migration. ex:
  14  
  15  ./script/generate migration sql_external_test
  16  
  17  # save your sql file with sql commands in db/. migration's sample:
  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
created by leozera — 02 February 2009 — get a short url — tags: migrations rails ruby sql — embed

resets autoincrement (mysql) Delicious Email

show/hide lines
   1  ALTER TABLE tablename AUTO_INCREMENT = 1
created by leozera — 02 July 2008 — get a short url — tags: mysql sql — embed