MySQL GROUP_CONCAT function

I knew about this MySQL function called "GROUP_CONCAT" lately and I think it is really a very useful function to use

To make simple for you to understand, I will use a small example to illustrate on

Consider we have a table called listings which has many to many relationship with another table called geographies knowing that the join table name is listings_geographies

Now look at this query

select listings.id, listings.display_name, geographies.name as geography_name from listings LEFT OUTER JOIN listings_geographies ON listings.id = listings_geographies.listing_id LEFT OUTER JOIN geographies ON listings_geographies.geography_id = geographies.id WHERE status = 3 GROUP BY listings.id

In my example I have only two listings: the first has 3 geographies and the second has 2 geographies. By running this command, you will get 2 records but the field called "geography_name" will have one of the three values and not all the values

What if you want to grab all the three values and add them in 1 field so that you get 2 records but with "geography_name" field containing all geographies names values. If you want that, then you can rely on this nice function "GROUP_CONCAT"

The above function can be re-written to be this one

select listings.id, listings.display_name, GROUP_CONCAT(geographies.name SEPARATOR ' ') as geography_name from listings LEFT OUTER JOIN listings_geographies ON listings.id = listings_geographies.listing_id LEFT OUTER JOIN geographies ON listings_geographies.geography_id = geographies.id WHERE status = 3 GROUP BY listings.id

That's it
I needed this function as I was using Sphinx and I wanted to have a query written to grab all record info with its associations in 1 record in order to be indexed

Enjoy! :)

“Distinct vs Group By” SQL Talk

I am writing this blog entry to tell people about main differences between these two instructions because any misunderstanding to the true difference between them can cause big problems

That what happened to me actually
I didn’t not know the true difference and as always I have chosen the easy way and rely on Distinct instead of Group By

Let me show an example which I was working on
and that one really helped me understand the difference between these two

Suppose you have a Deal which is associated with several geographies
Each geography has a level representing its level in the tree of geographies. The higher the level, the lower the geography exists in the tree.

The objective was to get all deals in several geographies which I was given their IDS and sort them by depth/level from lowest to highest

I started writing my query to be like that
which is wrong by the way

select Distinct(deals.id) from deals INNER JOIN deals_geographies ON deals.id = deals_geographies.deal_id INNER JOIN geographies ON geographies.id = deals_geographies.geography_id WHERE geographies.id IN (id1, id2, id3) ORDER BY geographies.level DESC

it sounds good at the beginning. If you see it right then you are facing the same problem I was facing before. So please continue reading

The problem here is that inner joining will perform a Cartesian product between deals and geographies and as we have several geographies for each deal, we will get several rows for each deal

When you say DISTINCT without defining the criteria to the DB engine, it will choose any row which could get you the highest geography this deal has or it can get you the lowest geography this deal has

This is an Ambiguous Selection
but it is your fault that you did it that way

Lets see now how group by will solve this

select deals.*, MAX(geographies.level) as max_level from deals INNER JOIN deals_geographies ON deals.id = deals_geographies.deal_id INNER JOIN geographies ON geographies.id = deals_geographies.geography_id WHERE geographies.id IN (id1, id2, id3) ORDER BY max_level DESC group by deals.id

Now I told the DB engine what should the criteria it should use to remove multiple deal rows and which row it should leave which I wanted it to be the one with the largest geography level as this is my desired condition

My last conclusion is that you can use DISTINCT in one of these cases only

1. you have a 1 to 1 relation between two or several tables and thus no ambiguity will be there
2. you only care about 1 table fields and you are just using the other table for no more but Filtration which was not the case above as I needed it for ordering

btw, there is a rumor I heard saying that DISTINCT is not standard SQL. Not sure of this info but you can check that yourself and maybe comment and tell me

Hope this blog was useful and enlightening :)

Useful RVM resources

I think these few resources are useful for any RVM newbie as me :)

Read them in that order

• http://rvm.beginrescueend.com/rvm/basics/
• http://rvm.beginrescueend.com/rubies/default/
• http://rvm.beginrescueend.com/gemsets/basics/
• http://rvm.beginrescueend.com/rvm/best-practices/

For me, I made use of the first and third links although I see all of them useful but these ones helped me install and use several ruby versions with several gemsets which is why I used RVM at first place

Hope I was useful
and happy RVM journey

Installing RVM System Wide

Today I got introduced to one very nice solution developed by Wayne E. Seguin. It is called RVM and it is recommended for ruby developers working on Debian machines.

What is RVM ?

Simply RVM allows users to install multiple ruby versions and switch between them easily. It also allows having multiple sets of gems for different projects easily.

Installation Steps

Before getting into details, I would like to say that any steps mentioned here are grabbed from these two links. I only collected parts from them and added them in an easy way

http://rvm.beginrescueend.com/rvm/install/
http://rvm.beginrescueend.com/deployment/system-wide/
http://rvm.beginrescueend.com/rubies/installing/

1. from your linux console run
2. bash < <( curl -L http://bit.ly/rvm-install-system-wide )
3. you now have rvm command installed here at /usr/local/lib/rvm
4. After installation you should add this line below to your profile
5. [[ -s "/usr/local/lib/rvm" ]] && . "/usr/local/lib/rvm"
6. To do so, i added the line above at the end of the /etc/profile file. But, you can add it in other places according to your needs. Read more about profiles here
7. then run this one
8. source /usr/local/lib/rvm

Now you are ready to install any versions of Ruby and Ruby Enterprise versions available

For me, I installed Ruby 1.8.7 and its enterprise version this way

1. rvm install 1.8.7
2. rvm install ree-1.8.7

That’s it
Hope you enjoy RVM as I hope I enjoy it as well

Forget root password of a Debian Machine & resetting it

I am writing this small post just because

• i forgot the root password of my debian machine
• didn’t find a through post that list all steps in one place and had to look at several ones at a time

The steps are as follows

I am quoting these lines from that post

Some Linux distribution, such as Ubuntu for instance, offer a specific boot menu entry where it is stated "Recovery Mode" or "Single-User Mode". If this is your case, selecting this menu entry will boot your machine into single user mode, you can carry on with the next part. If not, you might want to read this part.

Using GRUB, you can manually edit the proposed menu entry at boot time. To do so, when GRUB is presenting the menu list (you might need to press ESC first), follow those instructions:

• use the arrows to select the boot entry you want to modify.
• press e to edit the entry
• use the arrows to go to kernel line
• press e to edit this entry
• at the end of the line add the word single
• press ESC to go back to the parent menu
• press b to boot this kernel

The kernel should be booting as usual (except for the graphical splash screen you might be used to), and you will finally get a root prompt (sh#).

Here we are, we have gained root access to the filesystem, let's finally change the password.

According to the above words, we should be ok and we have access to the file system. At this state you run the command “passwd” and enter the new password.

If it worked with you then Thanks to the editor. If you got some problems like me, then keep reading.

Problem 1

After editing grub line and add the “single” keyword at the end of that line. I got it loading well until i was prompted for the root password for maintenance and give the ability to skip but by then i will be leaving runlevel 1 and entering runlevel 2 getting login/password prompt i am trying to skip.

To solve this problem do the following:

• Edit the grub line again and leave the keyword “single” there as before but add at the end as well these words “init=/bin/bash”
• exit edit mode
• press the button “b” while you have this modified grub line highlighted to start booting with this modified grub line

Voila, you have now access to shell as a root. run the command “passwd” and you should be fine entering the new desired password. If you got a problem, then continue reading.

Problem 2

Whenever i run the command “passwd” and re-enter the new password i get this error at the end

authentication token lock busy

If so, know that the problem is that you are accessing the system in read-only mode. In order to access it in read-write mode, do the following.

From the shell run this command

mount -o remount,rw /

after that run “passwd” command and this time you should have the ability to enter the new password

and live happily ever after

Factory Girl in Development

Some many developers use Factory Girl as a replacement to Fixtures and they depend on this gem in Test cases writing.

But Factory Girl can be very useful also in development and can be used in order to generate a bunch of dummy data with different specs for showing your work and reviewing all its details.

The problem we face is that Factory Girl can generate more records in tables that should have their data unchanged such as Countries table.

For example:

Factory.define :user do |f|
  f.association :country
end

Factory.define :job do |f|
  …
  f.association :user
end

Now, when we run this piece of code that generates 10 jobs

10.times { Factory.create(:job) }

we will get 10 countries auto-generated
violating the rule we wish to maintain which is having the countries table as it is

To workaround this problem without causing any changes in the code written previously, i came up with that solution

class Factory
  class << self
    alias_method :create_original, :create
    
    def create(name, overrides = {})
      if name.to_s == 'country'
        country = Country.first
        return country if country
      end

      create_original(name, overrides)
    end
  end
end

The logic introduced in the above script is simply adding a layer before creation that checks if the created object of certain type and if that type is desired to not be generated, we return the first entry we have in the DB else we do the normal creation

You can add this script in a file and load it in the development environment and use Factory Girl safely without fearing of generating data for tables that should remain as lookup tables

Enjoy :)

Factory Girl & Polymorphic Associations

Suppose you have a case like that

class Address < ActiveRecord::Base
  belongs_to :addressable, polymorphic => true
end

class Listing < ActiveRecord::Base
  has_one :address, as => :addressable
end

class Customer < ActiveRecord::Base
  has_one :address, as => :addressable
end

Now in your factories you will have something like that

Factory.define :listing do |f|
  f.association :address
end

since addressable is required, we choose it to be by default related to a customer unless else stated

Factory.define :address do |f|
  f.association :addressable, :factory => :customer
end

right now if you tried to use the Listing Factory, you will get an address associated with dummy customer and no effect on your side

How can this be fixed ?

Factory.define :listing do |f|
  f.after_build do |listing|
    listing.address = Factory.create(:address, :addressable => listing)
  end
end

This was the only solution i found after searching for a while
which is a good solution and doesn't need a lot of work to be done