How to use advanced query without nil output when selecting a count in Rails

Question

I'm working through a practice project to create a short-url.

I have the following classes/tables:

shortened_urls which holds a short url, long url, and user.
user which holds user info.
visit which is a joins table for who visited what links.
tag_topics which holds categories for tagging URLs.
tagging which is a joins table that links shortened URLs to a tag.

I'm trying to

"write a method, TagTopic#popular_links that returns the 5 most visited links for that 'TagTopic' along with the number of times each link has been clicked."

I started with:

def popular_links
    shortened_urls.joins(:visits)
end

which gave the following output in the videos tag. For example:

  ShortenedUrl Load (0.8ms)  SELECT "shortened_urls".* FROM "shortened_urls" INNER JOIN "taggings" ON "shortened_urls"."id" = "taggings"."shortened_url_id" INNER JOIN "visits" ON "visits"."shortened_url_id" = "shortened_urls"."id" WHERE "taggings"."tag_topic_id" = $1  [["tag_topic_id", 2]]
[#<ShortenedUrl:0x00007fc7caee5298
  id: 2,
  short_url: "G6hSOkznliIwHxMUV1BPug",
  long_url: "imdb.com",
  user_id: 2,
  created_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00,
  updated_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00>,
 #<ShortenedUrl:0x00007fc7cbd04e00
  id: 3,
  short_url: "kFf-yYHHUZZFBYEy__K9sw",
  long_url: "youtube.com",
  user_id: 3,
  created_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00,
  updated_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00>,
 #<ShortenedUrl:0x00007fc7cbd04ba8
  id: 3,
  short_url: "kFf-yYHHUZZFBYEy__K9sw",
  long_url: "youtube.com",
  user_id: 3,
  created_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00,
  updated_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00>,
 #<ShortenedUrl:0x00007fc7cbd04928
  id: 3,
  short_url: "kFf-yYHHUZZFBYEy__K9sw",
  long_url: "youtube.com",
  user_id: 3,
  created_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00,
  updated_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00>,
 #<ShortenedUrl:0x00007fc7cbd04798
  id: 3,
  short_url: "kFf-yYHHUZZFBYEy__K9sw",
  long_url: "youtube.com",
  user_id: 3,
  created_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00,
  updated_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00>,
 #<ShortenedUrl:0x00007fc7cbd045b8
  id: 4,
  short_url: "DZa0WvFrMcRjL2EQAFdWyg",
  long_url: "plex.tv",
  user_id: 3,
  created_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00,
  updated_at: Wed, 18 Mar 2020 21:07:36 UTC +00:00>]

But when I try doing any selecting or counting, (this was originally the solution provided):

def popular_links
    shortened_urls.joins(:visits)
      .group(:short_url, :long_url)
      .order('COUNT(visits.id) DESC')
      .select('long_url, short_url, COUNT(visits.id) as number_of_visits')
      .limit(5)
end

I get the following output:

 ShortenedUrl Load (2.1ms)  SELECT long_url, short_url, COUNT(visits.id) as number_of_visits FROM "shortened_urls" INNER JOIN "taggings" ON "shortened_urls"."id" = "taggings"."shortened_url_id" INNER JOIN "visits" ON "visits"."shortened_url_id" = "shortened_urls"."id" WHERE "taggings"."tag_topic_id" = $1 GROUP BY "shortened_urls"."short_url", "shortened_urls"."long_url" ORDER BY COUNT(visits.id) DESC LIMIT $2  [["tag_topic_id", 2], ["LIMIT", 5]]
[#<ShortenedUrl:0x00007fc7d21431c8
  id: nil,
  short_url: "kFf-yYHHUZZFBYEy__K9sw",
  long_url: "youtube.com">,
 #<ShortenedUrl:0x00007fc7d20ebd38
  id: nil,
  short_url: "DZa0WvFrMcRjL2EQAFdWyg",
  long_url: "plex.tv">,
 #<ShortenedUrl:0x00007fc7d20ebc48
  id: nil,
  short_url: "G6hSOkznliIwHxMUV1BPug",
  long_url: "imdb.com">]

There is no "count," and the id is nil. The URLs will select though. I tried re-arranging, or doing different selections, but it never seems to return a count. From looking at the select all on the joins table, it should be able to count. I'm not sure if I'm querying wrong or what.

max · Accepted Answer · 2020-03-20 19:58:25Z

2

There is no "count"

What you are seeing is actually what happens when you inspect a model - not the actual result rows from the database. It will only ever show the attributes of the model that are created by reading the database schema. Its just a debugging aid.

If you actually iterate though the records the attribute is actually there.

popular_links.each do |url|
  puts url.number_of_visits
end

Protip is to write unit tests instead of just staring blindly at the console.

and the id shows as nil.

That's because you are not selecting the id.

.select(:id, :long_url, :short_url, 'COUNT(visits.id) as number_of_visits')

edited Mar 20, 2020 at 19:58

answered Mar 20, 2020 at 19:47

max

103k15 gold badges113 silver badges177 bronze badges

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

Collectives™ on Stack Overflow

How to use advanced query without nil output when selecting a count in Rails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related