Joining WorkTypes to WorkGroups via Channels and Users to check authorization

Question

I need to select some "work types" from a channel. These "channels" are in "work groups" and both channels and work groups need to have permissions to view them (tables are ChannelUsers and WorkGroupUsers to check this). The views that can be selected are as such:

  this.views = {
    "*" : ["*"],
    "index" : [
            "Timers.worktype_id",
            "WorkTypes.worktypename",
            "Timers.start",
            "Timers.end",
            "Users.username",
            "Timers.closed",
            "Users.username",
            "Users.id AS user_id",
            "(Timers.end - Timers.start) AS timer_length"
          ],
    "get" : [
            "Timers.worktype_id", 
            "Timers.id AS timer_id", 
            "Timers.start", 
            "Timers.end", 
            "Timers.user_id",
            "Timers.work_id",
            "Timers.closed",
            "ChannelUsers.user_id AS user_id",
            "Users.username"
          ]

The query, however is getting pretty grossly long. Being my first webapp ever I'm not used to chaining this many joins together. Is this at all acceptable? Should I be using some more efficient method of checking authorisation?

let checkQuery = `
            SELECT 
              ${view}
            FROM Timers

            INNER JOIN WorkTypes
              ON Timers.worktype_id=WorkTypes.id
              AND WorkTypes.channel_id=?

            INNER JOIN ChannelUsers
              ON ChannelUsers.channel_id=WorkTypes.channel_id
              AND ChannelUsers.user_id=?

            INNER JOIN Channels
              ON Channels.id=ChannelUsers.channel_id

            INNER JOIN Users
              ON Users.id=?

            INNER JOIN WorkGroupUsers
              ON WorkGroupUsers.workgroup_id=Channels.workgroup_id
              AND WorkGroupUsers.user_id=?

            INNER JOIN WorkGroups
              ON WorkGroupUsers.workgroup_id=WorkGroups.id;
          `;
      checkQuery = mysql.format(checkQuery, [_channelID, _user.id, _user.id, _user.id]);

I've heard some conflicting opinions on "views" and "temporary tables" but nothing I'm certain could help here. Any opinions would be greatly appreciated!

(The views are separate as part of a system for "building" many-to-one relationships into the resulting object, i.e building all posts a user has made by their ID into the method result, I cut it here for simplicity).

Toby Speight · Accepted Answer · 2017-09-20 09:46:25Z

I am understanding that your goal is to return a set of data that a user is authorized to see. I further understand that ChannelUsers and WorkGroupUsers are only used to validate the authorization. (I see you do select ChannelUsers.user_id but I don't think you really need it - you can take it from Users.id instead, I believe).

Given that, I believe you should test an alternative query to see whether this yield better performance. The SQL would be similar to this:

        SELECT 
          ${view}
        FROM Timers
        INNER JOIN WorkTypes
          ON Timers.worktype_id=WorkTypes.id
          AND WorkTypes.channel_id=?
        INNER JOIN Channels
          ON Channels.id=ChannelUsers.channel_id
        INNER JOIN Users
          ON Users.id=?
        INNER JOIN WorkGroups
          ON WorkGroupUsers.workgroup_id=WorkGroups.id
        WHERE EXISTS (
            SELECT NULL
            FROM ChannelUsers
            WHERE ChannelUsers.channel_id=WorkTypes.channel_id
              AND ChannelUsers.user_id=Users.id
        ) AND EXISTS (
            SELECT NULL
            FROM WorkGroupUsers
            WHERE WorkGroupUsers.workgroup_id=Channels.workgroup_id
              AND WorkGroupUsers.user_id=Users.id
        );

The main advantage of using EXISTS is that it won't "duplicate" the results as a join would so you don't have to do a DISTINCT; it also stop searching as soon as it has found its match. If you have indices on ChannelUsers(channel_id, user_id) and WorkGroupUsers(workgroup_id, user_id this may help, as EXISTS can blow through the index and thus get an answer faster in that manner.

Profile the original SQL against this and see what you get. The results also will be less with the EXCEPT, so validate this is acceptable.

Thank you for your answer. If I may ask, is there effectively any difference from my "JOIN x ON query" vs "SELECT JOIN x WHERE query"? Why does a EXISTS stop a JOIN from creating duplicates? Thanks so much again! — Toby
– Toby, Commented Sep 20, 2017 at 9:08
@Toby a join returns a row for each combination of both tables that satisfy your condition. A select with 'where exists' returns a row from the initial table that satisfies the condition, it doesn't select from the second table. — Peteris
– Peteris, Commented Sep 20, 2017 at 12:13
@Peteris is correct. Regarding your other question about JOIN x ON ... versus JOIN x WHERE.... -- there is a difference; an ON filter is applied before the join, whereas WHERE is applied to after the join. However, for an INNER JOIN, you won't see any difference; you get the same results either way. It's only with OUTER JOIN where filtering on an ON predicate can yield useful results. — this
– this, Commented Sep 20, 2017 at 12:46

Stack Exchange Network

Joining WorkTypes to WorkGroups via Channels and Users to check authorization

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Joining WorkTypes to WorkGroups via Channels and Users to check authorization

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions