Get list of software that should be informed on settings change - focustm/focust GitHub Wiki

WITH bound_user_settings AS (
    SELECT
        bu.id AS bound_user_id,
        COALESCE(
            bu.setting_id,
            org.setting_id,
            p.default_settings_id
        ) AS effective_setting_id
    FROM
        bound_user bu
    LEFT JOIN
        organization org ON bu.organization_id = org.id
    LEFT JOIN
        client c ON org.client_id = c.id
    LEFT JOIN
        plan p ON c.plan_id = p.id
)
SELECT
    at.id
FROM
    access_token at
JOIN
    bound_user_settings bus ON at.bound_user_id = bus.bound_user_id
WHERE
    at.login_type = 4
    AND bus.effective_setting_id = 5;  -- here 5 is settings.id