Skip to content

Instantly share code, notes, and snippets.

@rintaun
Created September 30, 2018 17:25
Show Gist options
  • Save rintaun/e8571ecba8c811646e3c27fa21bf95f5 to your computer and use it in GitHub Desktop.
Save rintaun/e8571ecba8c811646e3c27fa21bf95f5 to your computer and use it in GitHub Desktop.
Prepared query for counting the subset of tags for stories with the given tags
class Story < Sequel::Model
STORY_COUNT_BY_TAGS =
select { [tag, count(tag)] }
.from(
select { unnest(tags).as(tag) }
.from(:stories)
.where(
Sequel
.pg_array(:tags)
.contains(Sequel.cast(:$filter, :'text[]').pg_array)
)
)
.exclude(tag: Sequel.cast(:$filter, 'text[]').pg_array.any)
.group(:tag)
.order(:count).reverse
.prepare(:all, :tag_counts)
# SELECT "tag", count("tag")
# FROM (
# SELECT unnest("tags") AS "tag"
# FROM "stories"
# WHERE ("tags" @> CAST($1 AS text[]))
# ) AS "t1"
# WHERE ("tag" != ANY(CAST($1 AS text[])))
# GROUP BY "tag"
# ORDER BY "count" DESC
def self.count_by_tags(*filter)
STORY_COUNT_BY_TAGS
.call(filter: filter)
.each_with_object({}) do |result, hash|
hash[result[:tag]] = result[:count]
end
end
end
@rintaun
Copy link
Author

rintaun commented Sep 30, 2018

Incidentally, if there is a cleaner way to do the where and exclude calls, I'm all ears :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment