Embed Notice

HTML Code

<blockquote style="position: relative; padding-left: 55px;"><section><a href="https://fluffytail.org/objects/98477400-7d0e-408a-a77d-482218e44251">Phantasm (phnt@fluffytail.org)'s status on Monday, 04-Aug-2025 14:33:42 JST</a><a href="https://fluffytail.org/users/phnt" title="phnt@fluffytail.org"><img src="https://gnusocial.jp/avatar/158739-48-20230808204705.webp" width="48" height="48" alt="Phantasm" style="position: absolute; left: 0; top: 0;">Phantasm</a><div><a href="https://nicecrew.digital/objects/7fb5db3c-be13-4a44-8131-3e5b8a42e061" rel="in-reply-to">in reply to</a><ul><li></ul></div></section><article><p><a href="https://nicecrew.digital/users/tyler">@tyler</a> Fixed the query 2 hours ago and it has been crunching away. At least the infinite disk usage growth is gone now and it seems to report proper statistics.</p><p>Here's the query, if you are interested:</p>WITH cte AS (
    SELECT 
        data-&gt;&gt;'actor' AS actor,
        data-&gt;&gt;'content' AS content
    FROM 
        objects
)
SELECT 
    arr.word,
    SUM( 
        (SELECT count(*) 
         FROM regexp_matches(cte.content, '[[:&lt;:]]' || arr.word || '[[:&gt;:]]', 'gi') 
        ) 
    ) AS total_occurrences,
    <a href="http://cte.actor">cte.actor</a> AS actor
FROM 
    cte
CROSS JOIN 
    unnest(ARRAY['jew', 'jews', 'jewish']) AS arr(word)
GROUP BY 
    arr.word, <a href="http://cte.actor">cte.actor</a>
ORDER BY 
    arr.word, total_occurrences DESC;</article><footer><a rel="bookmark" href="https://gnusocial.jp/conversation/5449264#notice-10695172">In conversation</a><time datetime="2025-08-04T14:33:42+09:00" title="Monday, 04-Aug-2025 14:33:42 JST">about 4 months ago</time> <span>from <span><a href="https://gnusocial.jp/notice/10695172" rel="external" title="Sent from gnusocial.jp via ActivityPub">gnusocial.jp</a></span></span><a href="https://gnusocial.jp/notice/10695172">permalink</a></footer></blockquote>

Corresponding Notice

Embed this notice
Phantasm (phnt@fluffytail.org)'s status on Monday, 04-Aug-2025 14:33:42 JSTPhantasm
in reply to
- Tyler
@tyler Fixed the query 2 hours ago and it has been crunching away. At least the infinite disk usage growth is gone now and it seems to report proper statistics.
Here's the query, if you are interested:
WITH cte AS ( SELECT data->>'actor' AS actor, data->>'content' AS content FROM objects ) SELECT arr.word, SUM( (SELECT count(*) FROM regexp_matches(cte.content, '[[:<:]]' || arr.word || '[[:>:]]', 'gi') ) ) AS total_occurrences, cte.actor AS actor FROM cte CROSS JOIN unnest(ARRAY['jew', 'jews', 'jewish']) AS arr(word) GROUP BY arr.word, cte.actor ORDER BY arr.word, total_occurrences DESC;
In conversationabout 4 months ago from gnusocial.jppermalink

Public

Embed Notice

HTML Code

Corresponding Notice