Search code examples
sqlpostgresqljoinherokupg

LEFT OUTER JOIN loses original userID

Stepping up my PostgreSQL knowledge. Here's a working query:

SELECT * 
FROM associates a 
    LEFT OUTER JOIN ip b 
         ON a.userID = b.userID AND b.iPAddress = '124.xxx.xxx.xxx' 
    LEFT OUTER JOIN browser c 
         ON a.userID = c.userID AND c.userAgent = 'Mozilla / whatever Gecko' 
    LEFT OUTER JOIN location d 
         ON a.userID = d.userID AND d.country = 'US' 
    LEFT OUTER JOIN session e 
         ON a.userID = e.userID AND e.lastAction >= now() - interval '24 hours' 
WHERE a.extensionID = 'xxxxx' 
LIMIT 1

Running this, assuming that all conditions are met, returns a nice hash which starts with {'userid' => 12345, ... } which I may want to use later (for example, if a session has expired, I'll need it to make a new one)

However, in case of any mismatch - for example - if the calling user's IP is changed - will result in the same hash but with lost data and more importantly, the hash will start with {'userid' => nil}

Said userID is obviously being used to match other tables, so why is it lost and more importantly, how to preserve it?

It makes sense, sort of: if there is a mismatch, nothing was "selected." Is there a way to preserve the original a.userID somehow?

I should mention I'm using Heroku's PostgreSQL via pg gem.

Solution

  • Wondering if may be picking up user id from another table (b.userID, c.userId, ...). Try selecting explicitly and changing name.

    SELECT a.userID As MainUserId, * FROM associates a ...

    So in the "*" All Fields, you have not one but five things which are called userId. You have a.userId, b.userId, c.userId, d.userId, and e.userId. So when you refer to userId it's ambiguous as to which one it refers to. If it's a.userId then you are right, it should never be blank. If one of the other, it could be blank because missing form the left join.

    To make sure you're referring to the a.userId that you want, select it explicitly so you can reference it with no ambiguity. IN the sample code I put, I select it in addition to "everything" and name it explicitly so you can be sure that you're referring to the a.userId that is "reliable".