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 has a few columns I need:
collections table has only one column I need:
slug. It has a direct relationship with
articles.collection_id = collections.id.
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.
page_views table has a three columns I need:
referrer. It has a direct relationship with
page_views.article_id = articles.id. I'll need to
SUM up the
I'm trying to display a table result that looks like this:
|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:
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?
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...