STI MySQL fun select as with case

Question

I have the following STI setup:

class Cage < ActiveRecord::Base
  has_many :animals
end

class Animal < ActiveRecord::Base
  belongs_to :cage
end

class Cat < Animal
  # name = "Tom"
end

class Mouse < Animal
  # name = "Jerry"
end

I would like to do a select on the Animals table that returns the following structure:

+----+-----------------------+
| ID | cat_name | mouse_name |
+----+-----------------------+
| 1  | Tom      | Jerry      | 
+----+-----------------------+

Ideally I would like something like this:

Cage \
  .joins(:animals)
  .select("
    cages.id,
    animals.name as case animals.type 
      when 'Mouse' then mouse_name 
      when 'Cat' then cat_name end
  ") \
  .group('cages.id')

But that doesn't work so I did this:

Cage \
  .joins(:animals)
  .select("
    cages.id,
    case animals.type when 'Mouse' then animals.name end mouse_name end,
    case animals.type when 'Cat' then animals.name cat_name end
  ") \
  .group('cages.id')

Is there a way to make the first query work and if not is there a better/alternative way to write the second query?

Answer 1

In your first query you're trying to make two name columns with one CASE expression. That won't work. If you want two cols in your resultset, you have to select two different things.

Your second query is closer, but you're doing a group-by without any aggregate functions in the select, so you could get a random result from each group. I suggest:

...
GROUP_CONCAT(IF(animals.type='Cat',animals.name,NULL)) AS cat_name,
GROUP_CONCAT(IF(animals.type='Mouse',animals.name,NULL)) AS mouse_name
...

If I'm misunderstanding and you actually want both names in one result column, you could do:

GROUP_CONCAT( 
    CONCAT(animals.type,' name is: ',animals.name) 
    SEPARATOR ', '
) AS animal_names