How to track the count/name of people following a category

madchen

I'd like to get info around users who have checked some boxes in their "notification preferences" to follow some categories

R_J

@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.CategoryID

R_J

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();

madchen

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?

River

@madchen said:
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

R_J

@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.CategoryID

madchen

Thanks! It works just perfect!