group concat usage in MySQL

In this article, i am going to explaining usage of group_concat in MySQL with few example tables. Hope you people enjoy this concept, probably one who learn MySQL concepts. I am introduction two tables one is called "employee" table and "Department" table.

Structure and Data about employee table.

Id	name	Department_id
1	Madhan	1
2	nisha	2
3	janani	1
4	ajay	1
5	poovitha	2
6	ayyasamy	1
7	Jack	3

Structure and Data about department table.

Id	name
1	Accounts
2	IT
3	Product

Suppose if you want to query how many employees each and every department, you can use this following MySQL query.

SELECT department.name AS department_name,COUNT(*) AS department_total_employee FROM department
JOIN employee ON employee.department_id = department.id
GROUP BY employee.department_id

 Result of the above query

Department_name	Department_total_employee
Accounts	4
IT	2
Product	1

Just think about, now you got it how many employees available in each and every department.
If you want the detail, what the people belongs to which department in aggregate manner, you come know the real usage of GROUP_CONCAT method.

So What is GROUP_CONCAT?
" It is used to concatenate multiple column values into single string". If you won't use this, you have to lookup multiple rows and then concatenate them.

Note:
* In side group_concat method, you can override default seperator comma using the following command. Example group_concat(col.name SEPARATOR '--')

* You can change the order by also example

 group_concat(col.name order by col.name DESC)

The following query will give you the result of each and every department employee count as well as what are the employees on the particular department.

SELECT department.name AS department_name,COUNT(*) AS department_total_employee,GROUP_CONCAT(employee.emp_name) AS employees_under_department FROM department
JOIN employee ON employee.department_id = department.id
GROUP BY employee.department_id

  Result of the above query

Department_name	Department_total_employee	Employees_under_department
Accounts	4	Ayyasamy,janani,ajay,Madhan
IT	2	Poovitha,nisha
Product	1	jack

How to implement bulk insert in Rails

Yeah we can implement using "ACTIVERECORD-IMPORT" library, in rails 3.x (It will not support for rails 2.x versions).

Just look at the following code snippets

Class User < ActiveRecord::Base
  ######## schema information about user ##########
    login
    user_name
    email
    address
    city
    state
    country
    zipcode
  ######## schema information ended here #########
  has_many :posts
  has_many :comments
end

Suppose if you want to store/save 5-10 users into database table, normally you can do the following way

test_name= "user_name"
10.each do |number|
  User.create!(:user_name => test_name + number,:login_name => "login"+number,..etc fields)
end

The above code works fine without any issues. Just think about if you want to save 1000 or 2000 records, that time the above code snippets will eat huge time to save the records. So avoiding/handle that huge time difference Ruby on Rails, We can use the library called "activerecord-import".

Now we can insert 1000/2000 records at one shot using activerecord-import library. Here is the small code snippet implementation for your reference

users = []
test_name= "user_name"
1000.each do |number|
  users << User.new(:user_name => test_name + number,:login_name => "login"+number,..etc fields)
end
User.import users

Thats it. In single shot your entire 1000 records inserted into your database.

Note: This library only will work on Rails 3.x versions only not 2.x versions, so keep it this point before your implementation.

How to clone a git repository without history

Suppose if you have one git repository, if you are planning to share that repository to your working partner but it has some sensitive data/files. That time better create a new branch and edit those sensitive files and then get a clone of the repository without history then give to your parter. Using that cloned repository without history, your partner could not able to push that repository to anywhere else. But still you can get work done from your partner as "PATCHES".

Using the following command you can create a shallow repository with truncated/limited history.

git clone --depth 1 url_of_your_remote_repository

Limitation of shallow repository

* you can't clone or fetch from the shallow repository(That means you can't make a new repository using this shallow copy)

* you could not able to view whole history using git log command.

* Its workable solution, your partner can make some changes and make it as "patches" and send it to you partner if you wants your partner changes. Then apply that patches in your current working tree/ whatever branches you want to apply.

How to install gems group specific in your rails application

In Ruby on Rails application Gemfile, while you defining gems we can make that gem in a particular group. For example some gems needs to be loaded only on test environment so on that time we can mention those kind of gems into one specified groups. so your gemfile should like this

gem 'aws-s3'
gem 'paperclip'
group :test do
  gem 'rspec'
  gem 'waitr'
  gem 'faker'
end

If you are planning to define just one gem into a particular group only you can use without blocks like this

gem 'rest-client', :group => :development
gem 'cucuber-rails', :groups => [:development,:test]  (cucuber-rails gems comes under both group)

Similarly you can make it :development,:default(group name) or give a meaningful names whatever you want as group name.

Finally you have to tell while running the "bundle install" command just ignore what are the groups to be ignored using --without.

bundle install --without development
bundle install --without development test (ignore both groups and install others)
The above command ignore whatever gems we mentioned in the development group. So it will install rest of the default group(if you are not mention any group  those gems comes under defaults group)gems and test group gems.

Note:
bundle install --without development (ignore development group gems)
bundle install (still bundle remembers --without development so result is still ignore development groups it will not install all gems)

bundle install --without nothing (just clearing cache, now all the gems to be loaded into the ruby loadpath)