Select string based on integer column

Question

I have a class constant in my Rails app for mapping integers from a column to strings:

TYPES = [[1, "Beginner"], [2, "Intermediate"], [3, "Advanced"]]

I can use this to convert the integers to a human-readable strings for instances of the model (e.g., TYPES.to_h[type_id]), but I'd also like to use it to SELECT the human-readable string for large numbers of records at once. I could use a CASE statement:

SELECT *, CASE WHEN type_id = 1 THEN 'Beginner'
     WHEN type_id = 2 THEN 'Intermediate'
     WHEN type_id = 3 THEN 'Advanced'
END AS type_string FROM users;

However, this becomes very verbose, difficult to manage, and time-consuming as the TYPES array gets even slightly longer. Is there a better, more efficient way to select the type string from the type_id?

Are you looking something like enums. What enum does for you is it associates a symbol list for you to reference which will be stored in the database as an integer. So :cow could be 0 and :dog could be 1 in an enum of [:cow, :dog] . Reference link 6ftdan.com/allyourdev/2015/03/31/rails-4s-awesome-enums — praaveen V R
– praaveen V R, Commented Sep 1, 2015 at 16:59
@muistooshort I actually need to ORDER BY the strings for an admin view, which is the only case I need the strings in a db query. Should I be thinking about this in a different way? — eirikir
– eirikir, Commented Sep 1, 2015 at 17:17

Juan Carlos Oropeza · Accepted Answer · 2015-09-01 16:43:13Z

1

Your need create the mapping on your db instead of rails.

Create table types as (
   type_id Int,
   name varchar(100)
)

With this you can add/delete/update types and your code wont break

Then join both table with this one

SELECT u.*, t.name
FROM users u
inner join types t 
    on u.type_id = t.type_id

If need the mapping in rails you do a

select * from types

and initialize your TYPES variable.

answered Sep 1, 2015 at 16:43

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eirikir Over a year ago

So this is actually how I initially set it up, but my team is requesting a change to using the class constant so the logic isn't dependent on the state of the database. I'm resistant to this however, and maybe initializing the TYPES variable from the database provides a good middle ground, but I'd still like a solution to my initial problem for comparison...

Mark Swardstrom · Accepted Answer · 2015-09-01 20:34:51Z

1

You can always make another model (be careful using the reserved word 'type' in rails). ActiveRecord is great for these things. Don't store this data in code, especially if you need to sort it or it may grow. You can make a UI for managing the experience levels in an admin namespace that's restricted to common users.

This example uses the class name ExperienceLevel.

class ExperienceLevel < ActiveRecord::Base

  has_many :users, foreign_key: "type_id"

  # has an attribute called 'name'

end

Add the association to User

class User < ActiveRecord::Base

  belongs_to :experience_level, foreign_key: "type_id"

end

And then refer to it in rails views as you'd expect

@user.experience_level.name

edited Sep 1, 2015 at 20:34

answered Sep 1, 2015 at 16:56

Mark Swardstrom

18.3k6 gold badges74 silver badges75 bronze badges

Collectives™ on Stack Overflow

Select string based on integer column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related