Code:
def import_likes
puts "\nimporting likes..."
# created mysql user_actions table in vb3 using PHP script and included that table with migration dump
sql = "select acting_user_id as user_id, target_post_id as post_id, created_at from user_actions"
results = mysql_query(sql)
puts "length() method form : #{results.count}\n\n"
puts "skip loading unique id map"
existing_map = {}
PostCustomField.where(name: 'import_id').pluck(:post_id, :value).each do |post_id, import_id|
existing_map[import_id] = post_id
#puts "postcustomfield existing_map post_id: #{post_id} import_id #{import_id}\n"
end
puts "loading data into temp table"
#manually created the temp like_data table so I could check the table after session ends
#DB.exec("create temp table like_data(user_id integer, post_id integer, created_at timestamp without time zone)")
puts "like_data temp table created"
PostAction.transaction do
results.each do |result|
result["user_id"] = user_id_from_imported_user_id(result["user_id"].to_s)
result["post_id"] = existing_map[result["post_id"].to_s]
next unless result["user_id"] && result["post_id"]
puts "insert like table user_id: #{result["user_id"]} post_id #{result["post_id"]}\n"
DB.exec("INSERT INTO like_data VALUES (:user_id,:post_id,:created_at)",
user_id: result["user_id"],
post_id: result["post_id"],
created_at: result["created_at"]
)
end
end
puts "creating missing post actions"
DB.exec <<~SQL
INSERT INTO post_actions (post_id, user_id, post_action_type_id, created_at, updated_at)
SELECT l.post_id, l.user_id, 2, l.created_at, l.created_at FROM like_data l
LEFT JOIN post_actions a ON a.post_id = l.post_id AND l.user_id = a.user_id AND a.post_action_type_id = 2
WHERE a.id IS NULL
SQL
puts "creating missing user actions"
DB.exec <<~SQL
INSERT INTO user_actions (user_id, action_type, target_topic_id, target_post_id, acting_user_id, created_at, updated_at)
SELECT pa.user_id, 1, p.topic_id, p.id, pa.user_id, pa.created_at, pa.created_at
FROM post_actions pa
JOIN posts p ON p.id = pa.post_id
LEFT JOIN user_actions ua ON action_type = 1 AND ua.target_post_id = pa.post_id AND ua.user_id = pa.user_id
WHERE ua.id IS NULL AND pa.post_action_type_id = 2
SQL
# reverse action
DB.exec <<~SQL
INSERT INTO user_actions (user_id, action_type, target_topic_id, target_post_id, acting_user_id, created_at, updated_at)
SELECT p.user_id, 2, p.topic_id, p.id, pa.user_id, pa.created_at, pa.created_at
FROM post_actions pa
JOIN posts p ON p.id = pa.post_id
LEFT JOIN user_actions ua ON action_type = 2 AND ua.target_post_id = pa.post_id AND
ua.acting_user_id = pa.user_id AND ua.user_id = p.user_id
WHERE ua.id IS NULL AND pa.post_action_type_id = 2
SQL
puts "updating like counts on posts"
DB.exec <<~SQL
UPDATE posts SET like_count = coalesce(cnt,0)
FROM (
SELECT post_id, count(*) cnt
FROM post_actions
WHERE post_action_type_id = 2 AND deleted_at IS NULL
GROUP BY post_id
) x
WHERE posts.like_count <> x.cnt AND posts.id = x.post_id
SQL
puts "updating like counts on topics"
DB.exec <<-SQL
UPDATE topics SET like_count = coalesce(cnt,0)
FROM (
SELECT topic_id, sum(like_count) cnt
FROM posts
WHERE deleted_at IS NULL
GROUP BY topic_id
) x
WHERE topics.like_count <> x.cnt AND topics.id = x.topic_id
SQL
end
end
03-18-2020
