Well, that was fun.
What would have taken Rudy a heartbeat and a breath to put together …
On my localhost I had 4 Staff create a Staff Note starting with a made up “short code” on 4 different members.
~SHINING STAR~
This member has potential, lets keep an eye on them
Because the Profile Notes plugin created private messages I looked there - Nope
Staff Notes uses a different table
That table has no connection to the user the Staff Note was attached to except as the id as part of a string.
(some tricky stuff was needed, I’ll get better at Postgres yet)
This query
WITH psr AS (
SELECT regexp_matches(plugin_store_rows.key, '[0-9]+') AS psr_key_value
, plugin_store_rows.plugin_name AS psr_plugin_name
, plugin_store_rows.value AS psr_value
FROM plugin_store_rows
)
SELECT users.id
, users.username
, psr.psr_value
FROM users
JOIN psr
ON
users.id = CAST (array_to_string(psr.psr_key_value, '') AS int)
WHERE
psr.psr_plugin_name LIKE 'staff_notes'
AND
psr.psr_value LIKE '%~SHINING STAR~%'
gave me
id username psr_value
11 TestUser1 [{"id":"40ce59592f6b5c81a692845cdbb0f9d4","user_id":11,"raw":"~SHINING STAR~\nThis member has potential, lets keep an eye on them","created_by":2,"created_at":"2016-04-04T21:00:20.055-04:00"}]
31 RegularGal1 [{"id":"327c342266b2efa7d1530aba02811056","user_id":31,"raw":"~SHINING STAR~\nThis member has potential, lets keep an eye on them","created_by":9,"created_at":"2016-04-04T21:09:15.413-04:00"}]
7 RegularGuy1 [{"id":"eeb581c3ff772ffdc476ea09c0dce156","user_id":7,"raw":"~SHINING STAR~\nThis member has potential, lets keep an eye on them","created_by":3,"created_at":"2016-04-04T21:03:09.804-04:00"}]
30 MemberGal1 [{"id":"73da2850045782a9fdb9d9ec7341d3a0","user_id":30,"raw":"~SHINING STAR~\nThis member has potential, lets keep an eye on them","created_by":34,"created_at":"2016-04-04T21:05:59.993-04:00"}]