Cy on Nostr: Or, I could... not group by author, and just keep a running count of how many times ...
Or, I could... not group by author, and just keep a running count of how many times an author has popped up. This would mean potentially selecting hundreds of posts from the database that are immediately discarded, and I couldn't use a limit clause, or an offset really. However the first strategy means a separate query per author. Extremely complex and expensive queries would make that slow as balls, but extremely frequent shitposters would make a single query slow as balls, since I have to flip through it looking for the good rows, and the database cannot determine this. I think. Maybe.
What's the sqlite3 function for a running count? :/
Published at
2024-12-14 19:21:01Event JSON
{
"id": "b7a4b2c310e52835b913adf75477a6639e53d0e389878aadbb228092a8fc7993",
"pubkey": "bd9bf6b093eae31b2d42eeab8d71227ea77991ac6a1c8e6861d64e8ba508c327",
"created_at": 1734204061,
"kind": 1,
"tags": [
[
"p",
"bd9bf6b093eae31b2d42eeab8d71227ea77991ac6a1c8e6861d64e8ba508c327",
"wss://relay.mostr.pub"
],
[
"e",
"e06677af356fbe68ba387dd2ff151a42e1f3d86b3312fffef24d3f23836a8ce3",
"wss://relay.mostr.pub",
"reply"
],
[
"proxy",
"https://fedicy.us.to/cy/p/1734204061.363265",
"activitypub"
]
],
"content": "Or, I could... not group by author, and just keep a running count of how many times an author has popped up. This would mean potentially selecting hundreds of posts from the database that are immediately discarded, and I couldn't use a limit clause, or an offset really. However the first strategy means a separate query per author. Extremely complex and expensive queries would make that slow as balls, but extremely frequent shitposters would make a single query slow as balls, since I have to flip through it looking for the good rows, and the database cannot determine this. I think. Maybe.\n\nWhat's the sqlite3 function for a running count? :/\n",
"sig": "5975feb4951a085f5405ed5efac71e674ed421a66b23c7c995e47f3ec575145647dd5f0d390d4544651a2a803892d4fafe6fef756122a8ea314e9d5c1919b4b8"
}
