Retrieving data from forum database and displaying edited posts

PowerShell Help
1

Hello,

I am writing a powershell script that retrieves data from a forum database, and I am trying to figure out the following.

There is a single table that contains all the threads. In each thread there are several posts.

The posts that are stored in the table are identified by an ID and each time an edit is made the ID is increased and the PID value is replaced by the value of the previous ID.

Here is an example:

In line 2 I have the first post that is not edited with ID 8107 and PID 8093.

If I edit the post a new record will be created in the database with the following values:

ID 8108 PID 8107. Line 6.

If I edit the post again:

ID 8197 PID 8108. Line 5 and so on.

 

A/A ID PID Updated Posted Details
1 8114 8093 N/A 14/02/2020 5:26 2nd Post
2 8107 8093 N/A 14/02/2020 2:03 1st post
3 8203 8197 16/02/2020 17:09 14/02/2020 2:03 1st post edit 3
4 8201 8114 15/02/2020 15:37 14/02/2020 5:26 2nd post edited
5 8197 8108 15/02/2020 13:49 14/02/2020 2:03 1st post edit 2
6 8108 8107 14/02/2020 2:04 14/02/2020 2:03 1st post edit 1
7 8188 8099 N/A 14/02/2020 1:53 3rd post
 

So, I want to display only the lines 3, 4 and 7 which contain all the data that I need. Lines 3 and 4 are the most recent answers and line 7 is another post.

 

2

Not sure if that is a normal data structure for a forum, but it seems super inefficient to have to do a recursive lookup to associate a parent and sub-posts.

$posts = @"
A/A,ID,PID,Updated,Posted,Details
1,8114,8093,N/A,14/02/2020 5:26,2nd Post
2,8107,8093,N/A,14/02/2020 2:03,1st post
3,8203,8197,16/02/2020 17:09,14/02/2020 2:03,1st post edit 3
4,8201,8114,15/02/2020 15:37,14/02/2020 5:26,2nd post edited
5,8197,8108,15/02/2020 13:49,14/02/2020 2:03,1st  post edit 2
6,8108,8107,14/02/2020 2:04,14/02/2020 2:03,1st  post edit 1
7,8188,8099,N/A,14/02/2020 1:53,3rd post
"@ | ConvertFrom-CSV

$parentPosts = $posts | Where{$posts.ID -notcontains $_.PID}

$results = foreach ( $post in $parentPosts ) {
    $subPosts = @()
    $lookupId = $post.ID
    $i = 1
    do {

        Write-Host ('Searching a for subposts on {0}' -f $post.ID)
        $subPost = $posts | Where{$_.PID -eq $lookupId}
        
        if ($subPost) {
            Write-Host ('Found sub post {0}' -f $subPost.ID)
            $subPosts += $subPost | Select *, @{Name='SubPostSequence';Expression={$i}}
            $lookupId = $subPost.Id
            $i++
        }
    }
    until (!$subPost)

    $post | Select *,
                   @{Name='SubPosts';Expression={$subPosts}}
}

Output:

Searching a for subposts on 8114
Found sub post 8201
Searching a for subposts on 8201
Searching a for subposts on 8107
Found sub post 8108
Searching a for subposts on 8108
Found sub post 8197
Searching a for subposts on 8197
Found sub post 8203
Searching a for subposts on 8203
Searching a for subposts on 8188

PS C:\Users\rasim> $results


A/A      : 1
ID       : 8114
PID      : 8093
Updated  : N/A
Posted   : 14/02/2020 5:26
Details  : 2nd Post
SubPosts : @{A/A=4; ID=8201; PID=8114; Updated=15/02/2020 15:37; Posted=14/02/2020 5:26; Details=2nd post edited; SubPostSequence=1}

A/A      : 2
ID       : 8107
PID      : 8093
Updated  : N/A
Posted   : 14/02/2020 2:03
Details  : 1st post
SubPosts : {@{A/A=6; ID=8108; PID=8107; Updated=14/02/2020 2:04; Posted=14/02/2020 2:03; Details=1st  post edit 1; SubPostSequence=1}, @{A/A=5; ID=8197; PID=8108; Updated=15/02/2020 13:49; Posted=14/02/2020 2:03; Details=1st  post edit 2; 
           SubPostSequence=2}, @{A/A=3; ID=8203; PID=8197; Updated=16/02/2020 17:09; Posted=14/02/2020 2:03; Details=1st post edit 3; SubPostSequence=3}}

A/A      : 7
ID       : 8188
PID      : 8099
Updated  : N/A
Posted   : 14/02/2020 1:53
Details  : 3rd post
SubPosts : 




PS C:\Users\rasim> $results[1].SubPosts


A/A             : 6
ID              : 8108
PID             : 8107
Updated         : 14/02/2020 2:04
Posted          : 14/02/2020 2:03
Details         : 1st  post edit 1
SubPostSequence : 1

A/A             : 5
ID              : 8197
PID             : 8108
Updated         : 15/02/2020 13:49
Posted          : 14/02/2020 2:03
Details         : 1st  post edit 2
SubPostSequence : 2

A/A             : 3
ID              : 8203
PID             : 8197
Updated         : 16/02/2020 17:09
Posted          : 14/02/2020 2:03
Details         : 1st post edit 3
SubPostSequence : 3




PS C:\Users\rasim> $results[1].SubPosts | Sort SubPostSequence -Descending


A/A             : 3
ID              : 8203
PID             : 8197
Updated         : 16/02/2020 17:09
Posted          : 14/02/2020 2:03
Details         : 1st post edit 3
SubPostSequence : 3

A/A             : 5
ID              : 8197
PID             : 8108
Updated         : 15/02/2020 13:49
Posted          : 14/02/2020 2:03
Details         : 1st  post edit 2
SubPostSequence : 2

A/A             : 6
ID              : 8108
PID             : 8107
Updated         : 14/02/2020 2:04
Posted          : 14/02/2020 2:03
Details         : 1st  post edit 1
SubPostSequence : 1

If you convert the Updated\Posted date strings into [datetime], you could do some date sorts (last post\last updated post), but I’d rather just have a simple numeric sequence for sorting. The -Descending just sorts based on the latest to oldest. The data to me is very odd, be curious to see what the actual query is in the data layer to return a forum thread.

@Amala12: Please start your own thread to get your question answered

3

Thanks Rob!

That worked perfectly.

[quote quote=203919]Not sure if that is a normal data structure for a forum, but it seems super inefficient to have to do a recursive lookup to associate a parent and sub-posts.

The data to me is very odd, be curious to see what the actual query is in the data layer to return a forum thread.
[/quote]

I honestly don’t know.