When building Humadroid’s dashboard, I needed to show both company announcements and employee shoutouts in a single feed. At first, I took what seemed like the easy route - using notifications as a bridge between these different types of content. Each announcement or shoutout would create a notification, and I’d just query those. Simple enough, right?

Well, not quite. I soon realized this approach had a major flaw: new users couldn’t see older announcements or shoutouts. They’d join the company and find an empty dashboard. Not exactly the welcoming experience I was aiming for!

My first instinct was to reach for something fancy like Elasticsearch. After all, that’s what the “big players” use, right? But then I caught myself - was I really solving the right problem here? Sometimes as developers, we overcomplicate things when simpler solutions exist.

That’s when I remembered good old PostgreSQL’s UNION queries. Sure, my tables had different structures - announcements belong to groups while shoutouts are between individual employees - but PostgreSQL makes handling these differences surprisingly straightforward.

Here’s the neat trick I learned: you can use SELECT x AS y to rename columns and SELECT null AS column_name to handle missing fields. Here’s what the final query looks like:

SELECT 'Announcement' as dashboard_item_type, 
       id AS dashboard_item_id, 
       content, 
       group_id, 
       recipient_id, 
       author_id, 
       null as user_id, 
       created_at 
FROM announcements
UNION ALL
SELECT 'Shoutout' as dashboard_item_type, 
       id AS dashboard_item_id, 
       content, 
       null as group_id, 
       null as recipient_id, 
       author_id, 
       user_id, 
       created_at 
FROM shoutouts;

To make this even cleaner in Rails, I used the scenic gem to create a view. After running bin/rails generate scenic:view dashboard_items, I just needed a simple model to tie it all together:

class DashboardItem < AccountScopedRecord
  belongs_to :dashboard_item, polymorphic: true
  
  def item
    dashboard_item
  end
end

The lesson here? Sometimes the best solution isn’t the most sophisticated one. It’s easy to get caught up in using the latest and greatest tools, but often the built-in database features can do exactly what we need - we just have to give them a chance.

PS: If you’re curious about how this works in practice, feel free to reach out. I’m always happy to chat about database quirks and Rails tricks!