Skip to content

Instantly share code, notes, and snippets.

@simonw

simonw/full-prompt.md Secret

Created February 11, 2024 23:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save simonw/91ec0c784535ebf9a1ce575b34e0c79f to your computer and use it in GitHub Desktop.
Save simonw/91ec0c784535ebf9a1ce575b34e0c79f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
full-prompt.md

2024-02-11T23:27:08 conversation: 01hpd7yhx0z9mbr38hfwp18qsq

Model: gpt-4-turbo-preview

Prompt:

    read_ribbons.user_id,
    comments.id as comment_id,
    read_ribbons.story_id as story_id,
    comments.parent_comment_id,
    comments.created_at as comment_created_at,
    parent_comments.user_id as parent_comment_author_id,
    comments.user_id as comment_author_id,
    stories.user_id as story_author_id,
    (read_ribbons.updated_at < comments.created_at) as is_unread
FROM
    read_ribbons
JOIN
    comments ON comments.story_id = read_ribbons.story_id
JOIN
    stories ON stories.id = comments.story_id
LEFT JOIN
    comments parent_comments ON parent_comments.id = comments.parent_comment_id
WHERE
    read_ribbons.is_following = 1
    AND comments.user_id != read_ribbons.user_id
    AND
        (parent_comments.user_id = read_ribbons.user_id
         OR (parent_comments.user_id IS NULL
         AND stories.user_id = read_ribbons.user_id))
    AND (comments.upvotes - comments.downvotes) >= 0
    AND (parent_comments.upvotes - parent_comments.downvotes) >= 0;
--
    read_ribbons.user_id,
    comments.id as comment_id,
    read_ribbons.story_id as story_id,
    comments.parent_comment_id,
    comments.created_at as comment_created_at,
    parent_comments.user_id as parent_comment_author_id,
    comments.user_id as comment_author_id,
    stories.user_id as story_author_id,
    (read_ribbons.updated_at < comments.created_at) as is_unread,
    votes.vote as current_vote_vote,
    votes.vote as current_vote_reason
FROM
    read_ribbons
JOIN
    comments ON comments.story_id = read_ribbons.story_id
JOIN
    votes ON votes.comment_id = comments.id
JOIN
    stories ON stories.id = comments.story_id
LEFT JOIN
--
    read_ribbons.is_following = 1
    AND comments.user_id != read_ribbons.user_id
    AND votes.user_id = read_ribbons.user_id
    AND
        (parent_comments.user_id = read_ribbons.user_id
         OR (parent_comments.user_id IS NULL
         AND stories.user_id = read_ribbons.user_id))
    AND (comments.upvotes - comments.downvotes) >= 0
    AND (
      parent_comments.id IS NULL
      OR (parent_comments.upvotes - parent_comments.downvotes) >= 0
    );
--
    read_ribbons.user_id,
    comments.id as comment_id,
    read_ribbons.story_id as story_id,
    comments.parent_comment_id,
    comments.created_at as comment_created_at,
    parent_comments.user_id as parent_comment_author_id,
    comments.user_id as comment_author_id,
    stories.user_id as story_author_id,
    (read_ribbons.updated_at < comments.created_at) as is_unread,
    (select votes.vote from votes where votes.user_id = read_ribbons.user_id and votes.comment_id = comments.id) as current_vote_vote,
    (select votes.reason from votes where votes.user_id = read_ribbons.user_id and votes.comment_id = comments.id) as current_vote_reason
FROM
    read_ribbons
JOIN
    comments ON comments.story_id = read_ribbons.story_id
JOIN
    stories ON stories.id = comments.story_id
LEFT JOIN
    comments parent_comments ON parent_comments.id = comments.parent_comment_id
WHERE
    read_ribbons.is_following = 1
    AND comments.user_id != read_ribbons.user_id
    AND comments.is_deleted = FALSE
    AND comments.is_moderated = FALSE
    AND
        (parent_comments.user_id = read_ribbons.user_id
         OR (parent_comments.user_id IS NULL
         AND stories.user_id = read_ribbons.user_id))
    AND (comments.upvotes - comments.downvotes) >= 0
    AND (
      parent_comments.id IS NULL
      OR (parent_comments.upvotes - parent_comments.downvotes) >= 0
    )
--
    read_ribbons.user_id,
    comments.id as comment_id,
    read_ribbons.story_id as story_id,
    comments.parent_comment_id,
    comments.created_at as comment_created_at,
    parent_comments.user_id as parent_comment_author_id,
    comments.user_id as comment_author_id,
    stories.user_id as story_author_id,
    (read_ribbons.updated_at < comments.created_at) as is_unread,
    (select votes.vote from votes where votes.user_id = read_ribbons.user_id and votes.comment_id = comments.id) as current_vote_vote,
    (select votes.reason from votes where votes.user_id = read_ribbons.user_id and votes.comment_id = comments.id) as current_vote_reason
FROM
    read_ribbons
JOIN
    comments ON comments.story_id = read_ribbons.story_id
JOIN
    stories ON stories.id = comments.story_id
LEFT JOIN
    comments parent_comments ON parent_comments.id = comments.parent_comment_id
WHERE
    read_ribbons.is_following = 1
    AND comments.user_id != read_ribbons.user_id
    AND comments.is_deleted = FALSE
    AND comments.is_moderated = FALSE
    AND
        (parent_comments.user_id = read_ribbons.user_id
         OR (parent_comments.user_id IS NULL
         AND stories.user_id = read_ribbons.user_id))
    AND (comments.upvotes - comments.downvotes) >= 0
    AND (
      parent_comments.id IS NULL
      OR (parent_comments.upvotes - parent_comments.downvotes) >= 0
    )
--
  create_table "read_ribbons", id: { type: :bigint, unsigned: true }, charset: "utf8mb4", collation: "utf8mb4_general_ci", force: :cascade do |t|
    t.boolean "is_following", default: true
    t.datetime "created_at", precision: nil, null: false
    t.datetime "updated_at", precision: nil, null: false
    t.bigint "user_id", null: false, unsigned: true
    t.bigint "story_id", null: false, unsigned: true
    t.index ["story_id"], name: "index_read_ribbons_on_story_id"
    t.index ["user_id"], name: "index_read_ribbons_on_user_id"
  end

  create_table "saved_stories", id: { type: :bigint, unsigned: true }, charset: "utf8mb4", collation: "utf8mb4_general_ci", force: :cascade do |t|
    t.datetime "created_at", precision: nil, null: false
    t.datetime "updated_at", precision: nil, null: false
--
  add_foreign_key "read_ribbons", "stories", name: "read_ribbons_story_id_fk"
  add_foreign_key "read_ribbons", "users", name: "read_ribbons_user_id_fk"
  add_foreign_key "saved_stories", "stories", name: "saved_stories_story_id_fk"
  add_foreign_key "saved_stories", "users", name: "saved_stories_user_id_fk"
  add_foreign_key "stories", "domains"
  add_foreign_key "stories", "stories", column: "merged_story_id", name: "stories_merged_story_id_fk"
  add_foreign_key "stories", "users", name: "stories_user_id_fk"
--
      select `read_ribbons`.`user_id` AS `user_id`,`comments`.`id` AS `comment_id`,`read_ribbons`.`story_id` AS `story_id`,`comments`.`parent_comment_id` AS `parent_comment_id`,`comments`.`created_at` AS `comment_created_at`,`parent_comments`.`user_id` AS `parent_comment_author_id`,`comments`.`user_id` AS `comment_author_id`,`stories`.`user_id` AS `story_author_id`,`read_ribbons`.`updated_at` < `comments`.`created_at` AS `is_unread`,(select `votes`.`vote` from `votes` where `votes`.`user_id` = `read_ribbons`.`user_id` and `votes`.`comment_id` = `comments`.`id`) AS `current_vote_vote`,(select `votes`.`reason` from `votes` where `votes`.`user_id` = `read_ribbons`.`user_id` and `votes`.`comment_id` = `comments`.`id`) AS `current_vote_reason` from (((`read_ribbons` join `comments` on(`comments`.`story_id` = `read_ribbons`.`story_id`)) join `stories` on(`stories`.`id` = `comments`.`story_id`)) left join `comments` `parent_comments` on(`parent_comments`.`id` = `comments`.`parent_comment_id`)) where `read_ribbons`.`is_following` = 1 and `comments`.`user_id` <> `read_ribbons`.`user_id` and `comments`.`is_deleted` = 0 and `comments`.`is_moderated` = 0 and (`parent_comments`.`user_id` = `read_ribbons`.`user_id` or `parent_comments`.`user_id` is null and `stories`.`user_id` = `read_ribbons`.`user_id`) and `stories`.`score` >= 0 and `comments`.`score` >= 0 and (`parent_comments`.`id` is null or `parent_comments`.`score` >= 0 and `parent_comments`.`is_moderated` = 0 and `parent_comments`.`is_deleted` = 0) and !exists(select 1 from (`votes` `f` join `comments` `c` on(`f`.`comment_id` = `c`.`id`)) where `f`.`vote` < 0 and `f`.`user_id` = `parent_comments`.`user_id` and `c`.`user_id` = `comments`.`user_id` and `f`.`story_id` = `comments`.`story_id` limit 1)
  SQL
end
--
    read_ribbons.user_id,
    comments.id as comment_id,
    read_ribbons.story_id as story_id,
    comments.parent_comment_id,
    comments.created_at as comment_created_at,
    parent_comments.user_id as parent_comment_author_id,
    comments.user_id as comment_author_id,
    stories.user_id as story_author_id,
    (read_ribbons.updated_at < comments.created_at) as is_unread
FROM
    read_ribbons
JOIN
    comments ON comments.story_id = read_ribbons.story_id
JOIN
    stories ON stories.id = comments.story_id
LEFT JOIN
    comments parent_comments ON parent_comments.id = comments.parent_comment_id
WHERE
    read_ribbons.is_following = 1
    AND comments.user_id != read_ribbons.user_id
    AND
        (parent_comments.user_id = read_ribbons.user_id
         OR (parent_comments.user_id IS NULL
         AND stories.user_id = read_ribbons.user_id))
    AND (comments.upvotes - comments.downvotes) >= 0
    AND (
      parent_comments.id IS NULL
      OR (parent_comments.upvotes - parent_comments.downvotes) >= 0
    );
--
    read_ribbons.user_id,
    comments.id as comment_id,
    read_ribbons.story_id as story_id,
    comments.parent_comment_id,
    comments.created_at as comment_created_at,
    parent_comments.user_id as parent_comment_author_id,
    comments.user_id as comment_author_id,
    stories.user_id as story_author_id,
    (read_ribbons.updated_at < comments.created_at) as is_unread,
    (select votes.vote from votes where votes.user_id = read_ribbons.user_id and votes.comment_id = comments.id) as current_vote_vote,
    (select votes.reason from votes where votes.user_id = read_ribbons.user_id and votes.comment_id = comments.id) as current_vote_reason
FROM
    read_ribbons
JOIN
    comments ON comments.story_id = read_ribbons.story_id
JOIN
    stories ON stories.id = comments.story_id
LEFT JOIN
    comments parent_comments ON parent_comments.id = comments.parent_comment_id
WHERE
    read_ribbons.is_following = 1
    AND comments.user_id != read_ribbons.user_id
    AND
        (parent_comments.user_id = read_ribbons.user_id
         OR (parent_comments.user_id IS NULL
         AND stories.user_id = read_ribbons.user_id))
    AND (comments.upvotes - comments.downvotes) >= 0
    AND (
      parent_comments.id IS NULL
      OR (parent_comments.upvotes - parent_comments.downvotes) >= 0
    )
--
    read_ribbons.user_id,
    comments.id as comment_id,
    read_ribbons.story_id as story_id,
    comments.parent_comment_id,
    comments.created_at as comment_created_at,
    parent_comments.user_id as parent_comment_author_id,
    comments.user_id as comment_author_id,
    stories.user_id as story_author_id,
    (read_ribbons.updated_at < comments.created_at) as is_unread,
    votes.vote as current_vote_vote,
    votes.reason as current_vote_reason
FROM
    read_ribbons
JOIN
    comments ON comments.story_id = read_ribbons.story_id
JOIN
    votes ON votes.comment_id = comments.id
JOIN
    stories ON stories.id = comments.story_id
LEFT JOIN
--
    read_ribbons.is_following = 1
    AND comments.user_id != read_ribbons.user_id
    AND votes.user_id = read_ribbons.user_id
    AND
        (parent_comments.user_id = read_ribbons.user_id
         OR (parent_comments.user_id IS NULL
         AND stories.user_id = read_ribbons.user_id))
    AND (comments.upvotes - comments.downvotes) >= 0
    AND (
      parent_comments.id IS NULL
      OR (parent_comments.upvotes - parent_comments.downvotes) >= 0
    );
--
    read_ribbons.user_id,
    comments.id as comment_id,
    read_ribbons.story_id as story_id,
    comments.parent_comment_id,
    comments.created_at as comment_created_at,
    parent_comments.user_id as parent_comment_author_id,
    comments.user_id as comment_author_id,
    stories.user_id as story_author_id,
    (read_ribbons.updated_at < comments.created_at) as is_unread,
    (select votes.vote from votes where votes.user_id = read_ribbons.user_id and votes.comment_id = comments.id) as current_vote_vote,
    (select votes.reason from votes where votes.user_id = read_ribbons.user_id and votes.comment_id = comments.id) as current_vote_reason
FROM
    read_ribbons
JOIN
    comments ON comments.story_id = read_ribbons.story_id
JOIN
    stories ON stories.id = comments.story_id
LEFT JOIN
    comments parent_comments ON parent_comments.id = comments.parent_comment_id
WHERE
    read_ribbons.is_following = 1
    AND comments.user_id != read_ribbons.user_id
    AND comments.is_deleted = FALSE
    AND comments.is_moderated = FALSE
    AND (
      parent_comments.user_id = read_ribbons.user_id
      OR (parent_comments.user_id IS NULL
      AND stories.user_id = read_ribbons.user_id)
    )
    -- neither the story nor comment have negative scores
    AND stories.score >= 0
    AND comments.score >= 0
    AND (
--
    read_ribbons.user_id,
    comments.id as comment_id,
    read_ribbons.story_id as story_id,
    comments.parent_comment_id,
    comments.created_at as comment_created_at,
    parent_comments.user_id as parent_comment_author_id,
    comments.user_id as comment_author_id,
    stories.user_id as story_author_id,
    (read_ribbons.updated_at < comments.created_at) as is_unread,
    (select votes.vote from votes where votes.user_id = read_ribbons.user_id and votes.comment_id = comments.id) as current_vote_vote,
    (select votes.reason from votes where votes.user_id = read_ribbons.user_id and votes.comment_id = comments.id) as current_vote_reason
FROM
    read_ribbons
JOIN
    comments ON comments.story_id = read_ribbons.story_id
JOIN
    stories ON stories.id = comments.story_id
LEFT JOIN
    comments parent_comments ON parent_comments.id = comments.parent_comment_id
WHERE
    read_ribbons.is_following = 1
    AND comments.user_id != read_ribbons.user_id
    AND comments.is_deleted = FALSE
    AND comments.is_moderated = FALSE
    AND (
      parent_comments.user_id = read_ribbons.user_id
      OR (parent_comments.user_id IS NULL
      AND stories.user_id = read_ribbons.user_id)
    )
    AND (comments.upvotes - comments.downvotes) >= 0 -- comment doesn't have negative score
    AND (
      parent_comments.id IS NULL
      OR (
--
  after_action :update_read_ribbons, only: [:unread]
  after_action :clear_unread_replies_cache, only: [:comments, :stories]
  after_action :zero_unread_replies_cache, only: [:all, :unread]

  def all
    @title = "All Your Replies"
--
  def update_read_ribbons
    story_ids = @replies.pluck(:story_id).uniq
    ReadRibbon
      .where(user_id: @user.id, story_id: story_ids)
      .update_all(updated_at: Time.current)
  end
--
    change_column :read_ribbons, :id, :bigint, unsigned: true, unique: true, null: false, auto_increment: true
    change_column :saved_stories, :id, :bigint, unsigned: true, unique: true, null: false, auto_increment: true
    change_column :stories, :id, :bigint, unsigned: true, unique: true, null: false, auto_increment: true
    change_column :suggested_taggings, :id, :bigint, unsigned: true, unique: true, null: false, auto_increment: true
    change_column :suggested_titles, :id, :bigint, unsigned: true, unique: true, null: false, auto_increment: true
    change_column :tag_filters, :id, :bigint, unsigned: true, unique: true, null: false, auto_increment: true
--
    change_column :read_ribbons, :story_id, :bigint, unsigned: true, null: false
    change_column :read_ribbons, :user_id, :bigint, unsigned: true, null: false
    change_column :saved_stories, :story_id, :bigint, unsigned: true, null: false
    change_column :saved_stories, :user_id, :bigint, unsigned: true, null: false
    change_column :stories, :merged_story_id, :bigint, unsigned: true
    change_column :stories, :user_id, :bigint, unsigned: true, null: false
    change_column :suggested_taggings, :story_id, :bigint, unsigned: true, null: false
--
    add_foreign_key "read_ribbons", "stories", name: "read_ribbons_story_id_fk"
    add_foreign_key "read_ribbons", "users", name: "read_ribbons_user_id_fk"
    add_foreign_key "saved_stories", "stories", name: "saved_stories_story_id_fk"
    add_foreign_key "saved_stories", "users", name: "saved_stories_user_id_fk"
    add_foreign_key "stories", "stories", column: "merged_story_id", name: "stories_merged_story_id_fk"
    add_foreign_key "stories", "users", name: "stories_user_id_fk"
    add_foreign_key "suggested_taggings", "stories", name: "suggested_taggings_story_id_fk"
--
    create_table :read_ribbons do |t|
      t.boolean :is_following, default: true
      t.timestamps
    end

    add_reference :read_ribbons, :user, index: true
    add_reference :read_ribbons, :story, index: true

    create_view :replying_comments
  end
end

System:

explain what read ribbons are

Response:

Based on the SQL queries and the schema definitions you've provided, it seems "read ribbons" is a concept related to tracking user interactions with stories or posts in a social platform or content management system, possibly a forum, news aggregator, or a blog commenting system.

Here's an explanation of key components related to read ribbons and their functionality as inferred from the provided SQL snippets:

  1. Purpose: Read ribbons seem to serve the purpose of tracking whether users have read or followed specific stories or posts. This feature is likely used to manage and display unread content to users, enhancing their experience by making it easier to catch up on what's new or changed since their last visit.

  2. Database Structure:

    • The read_ribbons table has a few important fields: is_following (a boolean indicating if a user is following a story), created_at and updated_at (timestamps for tracking when the read ribbon was created or last updated), user_id, and story_id (foreign keys linking to the users and stories tables respectively). It essentially creates a many-to-many relationship between users and stories, with the additional context about following status and read status.
    • There are indices on user_id and story_id for performance optimization, ensuring that queries to find which stories a user is following (or who is following a story) are efficient.

  3. Functionality:

    • Queries involving the read_ribbons table seem to be focused on fetching unread comments for the user, distinguishing between unread and read comments based on the updated_at timestamp in comparison to the created_at timestamp of comments.
    • The concept of "following" a story is important here. A user appears to automatically follow stories they interact with (e.g., through commenting), and their read ribbons are updated when they read new comments to ensure accurate tracking of what they've seen.
    • Additional conditions in the queries include checks for comment and story scores (to filter out negatively scored items), comment moderation status, and ensuring that users do not see their comments as unread.

  4. Actions: The operations performed with read ribbons include updating the updated_at timestamp when a user views new content, and filtering comments and stories to present users with relevant, unread discussions. Additionally, after certain actions like viewing unread replies, corresponding "after actions" (such as update_read_ribbons) are triggered to keep the read ribbon statuses up to date.

To sum up, read ribbons are a feature used for managing and tracking user engagement with stories or posts, distinguishing between read and unread content to improve user experience on platforms where content is frequently updated or added.

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