Postgres extracting a polymorphic column

Question

I have the following table called feeds:

 from_type | from_id 
-----------+---------
 user      |       1
 project   |       1
 user      |       2
 program   |       1
 program   |       2
 project   |       1
 challenge |       1
 project   |       3
 community |       1

and I'd like to transform it to this:

 from_type | user_id | project_id | program_id | challenge_id | community_id
-----------+---------+------------+------------+--------------+-------------
 user      |       1 |            |            |              |         
 project   |         |          1 |            |              |         
 user      |       2 |            |            |              |         
 program   |         |            |          1 |              |         
 program   |         |            |          2 |              |         
 project   |         |          1 |            |              |         
 challenge |         |            |            |            1 |         
 project   |         |          3 |            |              |         
 community |         |            |            |              |           1

My reason for doing so is to have the reverse migration if we need to roll back. I managed to transform the bottom version to the top version with a coalesce + update statement, but I'm less sure how to perform the reverse operation.

Here's the up migration, what should the down migration look like?

class PolymorphicFeedTable < ActiveRecord::Migration[5.2]
  def up
    execute <<-SQL
      UPDATE feeds SET
        from_id = coalesce(user_id, project_id, community_id, challenge_id, program_id, need_id);
    SQL
  end

  def down
    execute <<-SQL
      ?
    SQL
  end
end

mu is too short · Accepted Answer · 2020-04-11 20:19:27Z

2

If you unroll your up method:

def up
  %w[challenge community need program project user].each do |type|
    execute("update feeds set from_id = #{type}_id where from_type = '#{type}'")
  end
end

then you can see the way back, just reverse the assignment:

def down
  %w[challenge community need program project user].each do |type|
    execute("update feeds set #{type}_id = from_id where from_type = '#{type}'")
    # ------------------------^^^^^^^^^^^^^^^^^^^^
  end
end

That assumes that your table data isn't broken (i.e. that your from_type values match the X_id columns).

You could connection.quote(type) instead of simply wrapping #{type} in single quotes by hand but you know that the types are safe beforehand so that's not necessary. Similarly for connection.quote_column_name and the #{type}_id interpolations.

answered Apr 11, 2020 at 20:19

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

3 Comments

Kori John Roys Over a year ago

Thanks! I was hoping to do it as a single SQL statement, but I suppose that's not actually necessary, there are only a couple thousand records. And it's safe to assume the data is clean (we verified the data in production before approving this migration/refactor).

mu is too short Over a year ago

@KoriJohnRoys You could use dynamic SQL to do it in one query (i.e. SQL's version of eval) but I don't think it is worth the hassle for a down-migration that will probably never be used.

Kori John Roys Over a year ago

I concur, for a down migration that I hope will never be used. 😅

Collectives™ on Stack Overflow

Postgres extracting a polymorphic column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related