File for modifying the SQL query for create 'post'?

charnelpezai · September 16, 2022, 7:14am

Hi guys, to give some background, I’m trying to add another column to the table schema of posts table, I want to add another column which is meta_tag_id. I got to a point where I can pass the data that I want to store in the parameters of PostController, check the log below for reference(this is from the logs of d/rails s):

Started POST "/posts" for 127.0.0.1 at 2022-09-16 06:08:19 +0000
Processing by PostsController#create as */*
  Parameters: {"raw"=>"Another dummy reply, for testing purposes.", "unlist_topic"=>"false", "category"=>"5", "topic_id"=>"23", "is_warning"=>"false", "archetype"=>"regular", "typing_duration_msecs"=>"4500", "composer_open_duration_msecs"=>"11534", "featured_link"=>"", "shared_draft"=>"false", "draft_key"=>"topic_23", "meta_tag_id"=>"summary", "nested_post"=>"true"}

Now, what I want to achieve is to insert the meta_tag_id parameter in another column of the posts table, for reference, here is a sample logs of the SQL query when inserting a record in my posts table:

Post Create (1.1ms)  INSERT INTO "posts" ("user_id", "topic_id", "post_number", "raw", "cooked", "created_at", "updated_at", "sort_order", "last_editor_id", "last_version_at", "word_count", "baked_at", "baked_version") VALUES (1, 23, 7, 'Another dummy reply, for testing purposes.', '<p>Another dummy reply, for testing purposes.</p>', '2022-09-16 06:08:22.283658', '2022-09-16 06:08:22.283658', 7, 1, '2022-09-16 06:08:22.293786', 6, '2022-09-16 06:08:22.283601', 2) RETURNING "id"

I would be grateful if you guys could point me in the right direction. I’m not familiar with ruby, so I’m having a hard time finding the find that I should modify to achieve the adding of a new column and incorporating the meta_tag_id variable to the values of the query. Thank you in advance guys!

merefield · September 16, 2022, 7:27am

Modifying such a key table directly is probably risky, consider instead using the PostCustomField model, it exists for exactly this kind of purpose: to attach bespoke custom data to Posts, usually in a plugin.

github.com

discourse/discourse/blob/main/app/models/post_custom_field.rb

# frozen_string_literal: true

class PostCustomField < ActiveRecord::Base
  belongs_to :post
end

# == Schema Information
#
# Table name: post_custom_fields
#
#  id         :integer          not null, primary key
#  post_id    :integer          not null
#  name       :string(256)      not null
#  value      :text
#  created_at :datetime         not null
#  updated_at :datetime         not null
#
# Indexes
#
#  idx_post_custom_fields_akismet                (post_id) WHERE (((name)::text = 'AKISMET_STATE'::text) AND (value = 'needs_review'::text))

This file has been truncated. show original

If you have performance issues then maybe you can revisit, but this is a much simpler and safer solution.

charnelpezai · September 16, 2022, 12:45pm

Thank you for the reply, okay so my meta_tag_id will be stored in the post_custom_field table by using the PostCustomField class? Then to access the stored data on the post stream, I should get the data of the post_custom_field table also and incorporate the meta_tag_ids to the posts? Am I getting the correct use case of the PostCustomField class?

RGJ · September 16, 2022, 12:49pm

See How to add custom fields to models

There is no example for Posts but there are examples for Topics and Categories and they work the same way.

merefield · September 16, 2022, 12:56pm

Here are examples of their use:

github.com

discourse/discourse-assign/blob/7e85101b89e298b14e1cef50c37ee3de8ba63327/plugin.rb#L835


      
            end
          end
          
          
on(:post_destroyed) do |post|
            if Assignment.exists?(target_type: "Post", target_id: post.id, active: true)
              Assignment.where(target_type: "Post", target_id: post.id).update_all(active: false)
              MessageBus.publish("/topic/#{post.topic_id}", reload_topic: true, refresh_stream: true)
            end
          
          
  # small actions have to be destroyed as link is incorrect
            PostCustomField
              .where(name: "action_code_post_id", value: post.id)
              .find_each do |post_custom_field|
                next if post_custom_field.post == nil
                if ![Post.types[:small_action], Post.types[:whisper]].include?(
                     post_custom_field.post.post_type,
                   )
                  next
                end
                post_custom_field.post.destroy
              end

and

github.com

VinkasHQ/discourse-autobot/blob/5777c6878d1115c3233ce79701cb4a8c35f61742/lib/autobot/post_creator.rb#L111


      
            end
          end
          
          
def create!
            post = get_existing_post || post_creator.create!
            update_since_id
            post
          end
          
          
def get_existing_post
            existing_post_ids = PostCustomField
                                  .where(name: "autobot_source_url", value: source_url)
                                  .where(name: "autobot_campaign_id", value: campaign["id"])
                                  .pluck(:post_id)
          
          
  return if existing_post_ids.blank?
          
          
  Post.unscoped.find(existing_post_ids).last
          end
          
          
private

Make sure you are familiar with Rails Active Record Model concepts, because you’d normally want to do everything via Ruby on Rails rather than directly on the database.

charnelpezai · September 16, 2022, 1:12pm

Thanks for the replies, I will look at the references and examples that you guys given. Oh, I’m not familiar with active model concept, or in ruby in general, yet. This is very helpful, thanks again guys.

merefield · September 16, 2022, 1:27pm

It’s Lego, for databases

Topic		Replies	Views
Tag Custom Fields? Dev	9	461	April 8, 2024
How to properly alter the database? Dev	7	2468	April 11, 2017
How to add a super basic topic custom field Dev	11	2427	March 26, 2021
Create custom Automations Developer Guides automation , how-to	13	2267	December 10, 2023
New columns in directory (/users) Dev	6	2618	October 16, 2019

File for modifying the SQL query for create 'post'?

Related topics