How I do solve this impossible SQL query?
Tags: sql, postgres, help
Published: 2019-04-18 21:14:11

---
title: How I do solve this impossible SQL query?
published: true
tags: sql, postgresql, help
---

So I'm trying to gather some data from dev.to's database. Unfortunately, SQL is not my strong suit and I've been mostly working on the Ruby + Rails side of the app. Impossible is probably not the right word, but it seems not-so-possible to me since I've been working on this for 4 hours now. 😭

I have a few tables I'm trying to get data from:
- `articles` table,
- `collections` table,
- `reactions` table, and
- `page_views` table.

The `articles` table has a few columns I need: `id`, `title`, `published_at`, `positive_reactions_count`, and `cached_tag_list`.

The `collections` table has only one column I need: `slug`. It has a direct relationship with `articles` where `articles.collection_id = collections.id`.

The `reactions` table polymorphic relationship with the `articles` table, where a `reaction` has a `reactable_id = articles.id` and `reactable_type = 'Article'`. I'll need to `SUM` up the total number of likes, unicorns, and reading list reactions for each post.

The `page_views` table has a three columns I need: `counts_for_number_of_views`, `time_tracked_in_seconds`, `referrer`. It has a direct relationship with `articles` where `page_views.article_id = articles.id`. I'll need to `SUM` up the `counts_for_number_of_views` and `AVG` the `time_tracked_in_seconds`.

I'm trying to display a table result that looks like this:

article_id | title | published_at | cached_tag_list | series_title| total_reactions | total_likes | total_unicorns | total_bookmarks | total_views | average_time_spent_in_seconds | referrer
--- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | ---
1 | this post | 2019-04-18 | sql,postgresql,help | "SQL help" | 1 | 1 | 1 | 5 | 30 | https://dev.to

So far, I have an invalid query -- and some other awful variations that I don't understand -- that looks like this:
```sql
SELECT articles.id,
      articles.title,
      articles.published_at,
      articles.positive_reactions_count AS total_reactions,
      articles.cached_tag_list,
      collections.slug AS series_title,
      (SELECT COUNT(reactions.id) from reactions where reactions.category = 'like' AND reactions.reactable_id = articles.id AND reactions.reactable_type = 'Article') AS total_likes,
      (SELECT COUNT(reactions.id) from reactions where reactions.category = 'unicorn' AND reactions.reactable_id = articles.id AND reactions.reactable_type = 'Article') AS total_unicorns,
      (SELECT COUNT(reactions.id) from reactions where reactions.category = 'readinglist' AND reactions.reactable_id = articles.id AND reactions.reactable_type = 'Article') AS total_bookmarks,
      (SELECT SUM(page_views.counts_for_number_of_views) from page_views where page_views.article_id = articles.id) AS total_views,
      (SELECT AVG(page_views.time_tracked_in_seconds) from page_views where page_views.article_id = articles.id) AS total_views,
FROM articles
FULL JOIN collections ON collections.id = articles.collection_id
INNER JOIN reactions ON reactions.reactable_id = articles.id AND reactions.reactable_type = 'Article'
INNER JOIN page_views ON page_views.article_id = articles.id
WHERE articles.user_id = my_id
  AND articles.published = TRUE
```

I think the query and the example result table helps with what I'm trying to portray. This is definitely out of my league but perhaps it's much easier than I think it is?

## Update: 

I added an additional `AND articles.id = my_article_id` to make the query faster. Looks like it runs! However, I'm getting 952 rows for some reason. I think my JOIN usage might be incorrect...