Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Options
How to track the count/name of people following a category
madchen
New
I'd like to get info around users who have checked some boxes in their "notification preferences" to follow some categories
Tagged:
0
Best Answer
-
Options
R_J
Ex-Fanboy Munich Admin
@madchen said:
Is it possible to get that info with a SQL query?Well, sure. This one would be a start (look at it, it reads very similar to plain SQL)
$prefs = Gdn::sql() ->select('Name') ->select('Name', 'count', 'Preferences') ->from('UserMeta') ->where('Value', 1) ->like('Name', 'Preferences.%') ->groupBy('Name') ->getSelect(); ->resultArray();But if you want to do it in plain SQL, you might be interested to see the name of the categories, too. Try this one:
SELECT um.Name, COUNT(um.Name) AS `Preferences`, c.CategoryID, c.Name AS 'Category' FROM GDN_UserMeta um LEFT OUTER JOIN GDN_Category c ON SUBSTR(um.Name, -1, 1) = c.CategoryID WHERE um.Value = 1 AND um.Name LIKE 'Preferences.%' GROUP BY c.Name, um.Name, c.CategoryID6
Answers
You have that info in UserMeta.
$prefs = Gdn::sql() ->select('Name') ->select('Name', 'count', 'Preferences') ->from('UserMeta') ->where('Value', 1) ->like('Name', 'Preferences.%') ->groupBy('Name') ->get() ->resultArray();If you want to get the notifications of a specific user, you would have to do something like that:
$prefs = Gdn::sql() ->select('Name') ->from('UserMeta') ->where('UserID', $userID) ->where('Value', 1) ->like('Name', 'Preferences.%') ->get() ->resultArray();Thanks for your quick reply.
Unfortunately I can't see any table called "UserMeta"
Is it possible to get that info with a SQL query?
Every vanilla database in 2.2.1 and 2.3 has a usermeta table. it may have a 2 or 3 letter prefix before the name if you use phpmyadmin.
the code in vanilla automatically adds a prefix.
as you can see
https://github.com/vanilla/vanilla/blob/ec5b13f6713dae42cd835267cea328d8e8559432/applications/dashboard/settings/structure.php
Pragmatism is all I have to offer. Avoiding the sidelines and providing centerline pro-tips.
Well, sure. This one would be a start (look at it, it reads very similar to plain SQL)
$prefs = Gdn::sql() ->select('Name') ->select('Name', 'count', 'Preferences') ->from('UserMeta') ->where('Value', 1) ->like('Name', 'Preferences.%') ->groupBy('Name') ->getSelect(); ->resultArray();But if you want to do it in plain SQL, you might be interested to see the name of the categories, too. Try this one:
SELECT um.Name, COUNT(um.Name) AS `Preferences`, c.CategoryID, c.Name AS 'Category' FROM GDN_UserMeta um LEFT OUTER JOIN GDN_Category c ON SUBSTR(um.Name, -1, 1) = c.CategoryID WHERE um.Value = 1 AND um.Name LIKE 'Preferences.%' GROUP BY c.Name, um.Name, c.CategoryIDThanks! It works just perfect!