Mauro Morales

software developer

Tag: SQL

  • ActiveRecord Except

    August 19th was Whyday, and to commemorate it, I decided to write a gem called activerecord-except.


    activerecord-except is a Ruby gem that extends the functionality of ActiveRecord, allowing you to select all the fields from a table, except the ones that you specify. For example, if you have a table users.

    development_db=# \d users
          Table "public.users"
                Column             | 
     id                            | 
     username                      | 
     password                      | 
     email                         | 
     first_name                    | 
     last_name                     | 
     phone                         | 
     created_at                    | 
     updated_at                    | 

    And you want to get all the fields except for the password, you’d have to pass each of them in your select clause like so,

    Instead, using activerecord-except, can simplify your statement by saying only the field you don’t want, in this case, the password one



    Under the hood, the except clause makes use of the traditional selectclause. So our previous example will produce the following query

    SELECT "users"."id",
      FROM "users"

    This is because the SQL language doesn’t provide such functionality out of the box.

    I don’t know what is the reason for this. I can only speculate that it’s to be more explicit and not be caught by surprise if a field in a table gets added/deleted/changed. However, * is also wildly used. In Rails for example, it is what you get, when you don’t specify a select clause in your query.

    The way I managed to make it work is by adding a method toActiveRecord::Relation which asks the model for all its attributes and rejecting those that match with the ones passed as arguments.

         .reject { |attr| fields.include?(attr) }

    Note: As you can see, I’m using _default_attributes which starts with an underscore. This can mean that the method is not intended to be relied upon.

    Whether or not you might want to use in production, I leave up to you, where I really see the benefit of activerecord-except is for writing one-off scripts to extract data, because it makes them much easier to read.


    You can install it from rubygems or you can check the source code either on Sourcehut or GitHub.

  • Installing SQL Developer on Ubuntu 9.04

    One of the mayor reasons why I still use my Windows box is because I havent found a subtitute for TOAD. I know I could make it work some how using wine but I just didn’t feel like it. Since Oracle is so Linux supportive I looked for something on their website and for my surprise I found SQLdeveloper. So far, so good! I like it and I am going to start using it for work. Here are the steps I followed to make it work in my Ubuntu 9.04 box:

    1. Install Java JDK sudo apt-get install sun-java6-jdk
    2. Download Oracle SQL Developer for other platforms from Oracle’s website.
    3. Unzipped the package in my /home/{user}/Programs/sqldeveloper
    4. Run the .shsudo sh /home/{user}/Programs/sqldeveloper/
    5. When asked for my Java path wrote the following (be sure about your java version):/usr/lib/jvm/java-6-sun-
    6. Enjoy!

    Since I enjoy launching commands from my Applications menu this is what I did:

    1. System > Preferences > Main Menu
    2. Go to the Programming tab
    3. New Item
    4. Name: SQLdeveloper
    5. Command: sh /home/{user}/Programs/sqldeveloper/
    6. OK

    Now I can go to my Applications > Programming and click on my SQLdeveloper icon.

    If you have any questions please comment about it or feel free to contact me.

    This post was originally published on my Tumblr blog