Structuring "Complex" conditions in SQL



  • I'm writing a SQL query where a certain parameter depends on the logged in user. I can't realistically have the logic for the logged in user outside of the query or change the database schema. The condition I'm working on looks like:

    and ec.clientId in ( ... )
    

    Well duh, right. The trouble is that I'm going to have to calculate an adjunction, and I don't know how to structure that in SQL.

    In particular, if A is logged in, I want to see all of his clients. If B is logged in, I want to see all of her clients. There's a few of these. Let's call them (A, B, C, D). And if X (or another administrator, and there's a few of them), I want to see all of A, B, C, and D's clients.
    So the obvious thing would be to do something like

    and ec.clientId in (select ... where :CurrentLoggedInUser in (A, X, Y, Z) 
                  union select ... where :CurrentLoggedInUser in (B, X, Y, Z) 
                  union select ... where :CurrentLoggedInUser in (C, X, Y, Z) 
                  union select ... where :CurrentLoggedInUser in (D, X, Y, Z)) 
    

    Notice I'm adjoining X,Y,Z to the list of people who trigger each search result. Is this sensible? I don't really like repeating this logic. Especially since I have to do it for nearly every query I write. (X,Y,Z) represent administrators/supervisors here, and they need to see things like which appointments the sales people kept and missed, etc.

    I also can't really change the database schema.


  • Discourse touched me in a no-no place

    @Captain said:

    Notice I'm adjoining X,Y,Z to the list of people who trigger each search result.

    Why?



  • Because the administrators/supervisors need to see these results, but the sales people shouldn't see each other's.


  • area_deu

    Yeah, why? Shouldn't one time be enough? Why not

    and ec.clientId in (select ... where :CurrentLoggedInUser in (A, B, C, D, X, Y, Z) )


  • Java Dev

    From what you've given us, the solution seems obvious - and ec.clientId in (select ... where :currentLoggedInUser in (A,B,C,D,X,Y,Z). Which conflicts with yoour story. So I think we need more of the query.


  • Discourse touched me in a no-no place

    But you're combining them all with union anyway.

    Either I'm missing something here, or:

    and ec.clientId in (select ... where :CurrentLoggedInUser in (A, X, Y, Z) 
                  union select ... where :CurrentLoggedInUser in (B, X, Y, Z) 
                  union select ... where :CurrentLoggedInUser in (C, X, Y, Z) 
                  union select ... where :CurrentLoggedInUser in (D, X, Y, Z))
    

    and

    and ec.clientId in (select ... where :CurrentLoggedInUser in (A, X, Y, Z) 
                  union select ... where :CurrentLoggedInUser = B
                  union select ... where :CurrentLoggedInUser = C
                  union select ... where :CurrentLoggedInUser = D)
    

    Are the same. You're returning A, B, C, D, X, Y, Z to the parent query.



  • Because if I do that, I'm just pushing the problem into (...).


  • Discourse touched me in a no-no place

    I don't think you're being clear enough about what this problem is.


  • area_deu

    Then I'll need more of the query because at the moment I don't get the problem.



  • For the record, I'm totally lost here also.

    I'm also confused at how your software is structured such that:

    1. the database has to know what user is logged-in to fulfill the query, but

    2. the database doesn't have a user account for that user.

    Generally speaking, either all of your users are database users, or your program uses a single "master" user to do all queries. I've never seen a "mix" of the two techniques before.



  • The problem is that (...) depends on who is logged in. Administrators are not sales people. We only want to get sales person A's clients if the person running the query is A or an administrator.

    So it's more like

    and ec.loginId in (select (clients_for_A) where :CurrentLoggedInUser in (A, X, Y, Z)
                 union select (clients_for_B) where :CurrentLoggedInUser in (B, X, Y, Z)
                 union select (clients_for_C) where :CurrentLoggedInUser in (C, X, Y, Z)
                 union select (clients_for_D) where :CurrentLoggedInUser in (D, X, Y, Z)

  • Discourse touched me in a no-no place

    Do X, Y and Z always refer to the same users, or groups of user or whatever?



  • It's like a CRM system. The "logged in user" isn't really a database user. It's the front end's user.


  • I survived the hour long Uno hand

    We usually solve this with one query to get everything, for admins, and one query to get the logged-in user's clients, intelligently choosing which list by user type (passed to a proc, because we do procs). You may be prematurely optimizing this.



  • X, Y, and Z are three administrators. Always the same ones.


  • Discourse touched me in a no-no place

    @Captain said:

    X, Y, and Z are three administrators. Always the same ones.

    Then you need to include them once and union it to the logged in user, if you really must do it with unions.



  • @Yamikuronue said:

    We usually solve this …

    This.



  • This, except I'd go further. If your application has only one database user that does everything on its behalf, just write the dumb getters and setters and let the application decide what should or should not be returned, since the application's the only one who knows for sure.

    Or phrased more succinctly, let the entity responsible for managing the users also manage what data is available to those users.

    Mixing and matching like you're doing (attempting to do?) now seems like it'll just cause extreme confusion further down the line.



  • I don't control the application's source code. All I can do is write reports against the data it stores, and some parameters the application lets me pass in to queries.


  • area_deu

    @blakeyrat said:

    This, except I'd go further. If your application has only one database user that does everything on its behalf, just write the dumb getters and setters and let the application decide what should or should not be returned, since the application's the only one who knows for sure.

    So... push all data to the app server and let it sort it out itself? Might work or not, depending on how much data it is.



  • Ok well here's a strange and innovative idea: go find the guy who did write the application, and ask him how it should work.

    @ChrisH said:

    So... push all data to the app server and let it sort it out itself? Might work or not, depending on how much data it is.

    Well generally-speaking, if it's reporting we're talking about, then user permissions are a non-factor in that. The idea of doing reporting outside the application but still relying on the application's permission system is pretty weird to me. But hey, whatever.


  • area_deu

    Can you write a function that gets the client_ids depending on the user? With a giant IF :user = 'A' ELSE IF :user = 'B' block if you must?



  • I already understand the system's architecture. There's a database. And a front end. And the front end lets me pass queries to the database. And it auto-populates them with parameters like :CurrentLoggedInUser and that's the only way I can figure out who is running the query.

    Yes, I know it's a pain in the ass. And that is why I'm trying to lower my burden by writing queries against this system in a sensible way. That's why I'm trying to structure the query logic as well as I can.

    I don't need you to tell me it's a pain in the ass. It's not helping.



  • I can't modify the database schema. I wish I could, a lot.

    If I could, I would just make a table for permissions and id's and query against that. Instead, I'm having to reify the relationships in every query I write.


  • area_deu

    Can you create a separate helper database? Just grasping for straws here.

    Can you do temp tables in your query, or does it have to be a single select?



  • Can you add a sproc or function that'll return a table with permission information, so at least you have that in one central location?



  • Can you create a separate helper database? Just grasping for straws here.

    I don't think so. I don't think I could get the application to query against it.

    Can you do temp tables in your query, or does it have to be a single select?

    That's an interesting question. If I could, I could just do the query in two parts: make the temp table, and then use it in the query. I'd still have like 30 copies of the temp table in my source code, but at least it wouldn't be a weird condition in 30 queries. Looking into that now.

    @blakeyrat: Can you add a sproc or function that'll return a table with permission information, so at least you have that in one central location?

    I don't think so, but I like where this (and Chris's temp table suggestion) is going, though.


  • area_deu

    If not temp tables, then maybe Common Table Expressions? (Don't know what RDBMS you're on)



  • Wait, your queries are O(n) based on the number of users in the system? For the length of the input you give to the database?



  • The login table is indexed on login id, so I presume that queries that rely on the login id are o(log n). There's only like 50 relevant users, though.



  • I was referring to this:

    @Captain said:

    and ec.loginId in (select (clients_for_A) where :CurrentLoggedInUser in (A, X, Y, Z)
    union select (clients_for_B) where :CurrentLoggedInUser in (B, X, Y, Z)
    union select (clients_for_C) where :CurrentLoggedInUser in (C, X, Y, Z)
    union select (clients_for_D) where :CurrentLoggedInUser in (D, X, Y, Z)

    Your query is going to get longer every time you add a user. Also, why is the data you're querying part of the query? That's somehow worse than hardcoding it.



  • Your query is going to get longer every time you add a user.

    Yes, and I'd love suggestions for fixing that.

    Also, why is the data you're querying part of the query?

    The problem is that I can't change the underlying database. Some of our sales people are not licensed, and their work needs to be signed off by a licensed sales person. So really, it's not clients_for_A, it's more like unlicensed_sales_people_that_A_signs_for, and it's just a short list (typically one).

    If I could change the database, I'd just have a signoff's table with a single-to-multi relationship and do something like:

    select signs.for from signs where isAdministrator(:LoggedInUser) or signs.by = :LoggedInUser


  • How about where :LoggedInUser in (X, Y, Z) or (put actual condition here)?



  • Can I suggest that most of the confusion is due to the level of obfuscation you're building into your pseudo-code sample.

    Is there really anything super-sensitive about the original query (possibly obfuscate just the sales table names) that precludes you showing us the majority of the query?



  • I don't know enough about your database schema, can't you join against your user table to determine if the person is an admin? Your query would then look something like this:

    select ec.*
    from [table] ec
    join [Users] u ON u.UserId = ec.clientId
    where u.AccountName = :CurrentLoggedInUser 
    and (ec.clientId = u.UserId OR u.IsAdmin)


  • Not all of the users in the login table need to be signed for by anybody. Right now, the query is like:

    select exam.patunique as patunique,
           exam.date      as date,
           patient.last   as last
      from examcontacts
      inner join exam on exam.examunique = examcontacts.examunique
      inner join layouttablinkmaster on exam.layoutid = layouttablinkmaster.layoutid
      inner join patient on exam.patunique = patient.patunique
      where layotutablinkmaster.tabid in ( 2070 \\ service plan
                                        , 2186 \\ service plan 3
                                        )
       and examcontact.loginid in ( select case when :LogUniqueParam in ( 41 \\ X
                                                                        , 5  \\ Y
                                                                        , 6  \\ Z
                                                                        )
    
                                                then ('SS1', 'BH1', 'LO1')
                                                else :LogUniqueParam = 9 // A
                                                then ('SS1')
                                                else :LogUniqueParam = 31 // B
                                                then ('BH1')
                                                else :LogUniqueParam = 14 // C
                                                then ('LO1')
                                                end
                                      from Login // I wish there was a dummy table.
                                  )
    

    This is not doing all the filtering I'll need for the whole query, but it should give some context. (Paging @skotl) The particularly relevant portion is the subquery that gives me the list of examcontacts to look up.



  • OK... which table has the "clients"? The patient table?

    Edit: And how is the logged-in user related to the client/patient?



  • Logged in user is a login. Administrators are logins. Logins are for staff, and logins need to sign off on logins, but nothing in the login table tells us who signs off for who. Patients are more-or-less irrelevant. Forget about clients and sales people. The many-to-one relationship is login-to-login.

    A logged in user will be either a staff member (like a licensed clinician, or an unlicensed clinician, or an administrator). The unlicensed clinicians need a licensed clinician's signature. The licensed clinicians want to see what they need to sign. Administrators want to see the backlog (i.e., all the unsigned records).

    I can't just do isAdministrator(:LoggedInUser) or <real condition>, because other staff members will have records that don't need to be signed by anybody.



  • [spoiler]And is "login" an actual SQL login, or a table called "Login" (I'm confused about your point re dummy table)?[/spoiler] <---Edited out as you changed you most recent response and explained this :)

    Am I correct in thinking that X, Y, Z will always be in the select list for the sub-query?
    If so, then you could probably simplify it somewhat with;

    ( select 'SS1', 'BH1', 'LO1',
    CASE figure rest out
    END)

    That leaves "the rest". So is it literally a lookup; 9=SS1, 31=BH1, etc? Is there nowhere you can stuff a spare table in there?
    Or create a new database just for the lookups and select from that (select from other-database.dbo.loginlookups)?



  • Am I correct in thinking that X, Y, Z will always be in the select list for the sub-query?
    If so, then you could probably simplify it somewhat with;

    It's the "other way around." I pick one (or all) of SS1, BH1, or LO1 depending on who the logged in user is. X, Y, Z should never be an examcontact, and are not in the examcontact table.

    So is it literally a lookup; 9=SS1, 31=BH1, etc? Is there nowhere you can stuff a spare table in there?
    Or create a new database just for the lookups and select from that (select from other-database.dbo.loginlookups)?

    Yes, it's a lookup. Unfortunately, I can't change the database schema. @ChrisH suggested temp tables to at least make the query easier to read.


  • ♿ (Parody)

    @ChrisH said:

    If not temp tables, then maybe Common Table Expressions?

    This was my first thought. I use these all the time. In Oracle, at least, they are an awesome way to break up a query. They are the SQL analog to regular code where you break things into smaller functions, but work inside a single query.

    Advantages:

    • You can put the complex conditions in one place and get the benefits in multiple places later in the query
    • I/O is done once in the query. This is often a major bottleneck for me when I'm dealing with lots of tables.
    • I often query a CTE in another CTE to further break down / filter the data. Since the data is all in memory at that point, it's usually very fast. Much faster than separate reads.

    That said:

    @Captain said:

    >Your query is going to get longer every time you add a user.

    Yes, and I'd love suggestions for fixing that.

    Since the metadata about the users (who's licensed and who will sign off) doesn't appear to be in the DB, I don't see how you can possibly avoid modifying the query as the organization changes.



  • Your query and reasoning don't make basic sense at all.

    First, adding (x, y, z) into each part of union is redundant, duplicate rows are eliminated after the fact, so you are just abusing your database for no good reason.

    Second, supply more information. What tables do you query in ...? Is ... the same across all the SELECT's in your union? Man, can't you just make a join and call it a day?



  • @Captain said:

    The problem is that (...) depends on who is logged in. Administrators are not sales people. We only want to get sales person A's clients if the person running the query is A or an administrator.

    Study your schema well: it usually lets you find out what data is visible to each user. You can leverage that and not resort to stupid hardcoding.


  • area_deu

    @wft said:

    First, adding (x, y, z) into each part of union is redundant, duplicate rows are eliminated after the fact, so you are just abusing your database for no good reason.

    From what I got, it's not redundant. It's a pseudo-dynamic query because the columns / expressions selected change with the logged on user.
    It's not the same query repeated with different where clauses. Every query is different.


  • Notification Spam Recipient

    Are cte's/temp tables allowed?

    EDIT: if they are use them to map all possible user/client combinations



  • @ChrisH said:

    the columns / expressions selected change with the logged on user.

    The permission information is usually right there in the database. The ERP/CRM/whatchacallit doesn't use magic pixie dust to get that information. Thus you can ultimately have your username as the only parameter you need to substitute.



  • So what is preventing one from doing "union (X, Y, Z) union (A) union (B)" where the last two are dynamic and the first one is not? In SQL, UNION is UNION DISTINCT, and I don't see UNION ALL in the query. The duplicate rows are going to be eliminated anyway, except that they will be cleaned after the fact, except that the query gets complicated.



  • The crm doesn't get that information at all. It is not in the schema.


  • area_deu

    UNION ALL
    select shitB where :user in (B, X, Y, Z) -- B and admins need to see shit B
    UNION ALL
    select shitC where :user in (C, X, Y, Z) -- C and admins need to see shit C ```
    
    No duplicate rows at all.


  • @Captain said:

    The crm doesn't get that information at all. It is not in the schema.
    How does it discern, then?


Log in to reply