I have a query which returns all PM’s in the Trash Folder.
It is a UNION of Query #1 which returns “Deleted Incoming” messages, and Query #2 which returns “Deleted Sent” messages.
In Query #1, I have a column/alias called…
r.recipient_read_on AS read_on
In Query #2, there is no concept of a “Read Sent Message”, so I stubbed it out like this…
-1 AS read_on
When I run the UNION Query, everything works great, but here is the catch…
Now I want to filter the record-set on whether there is a “-1” for “read_on” - which would tell me if it is a “Sent Message”, but apparently I cannot use an Alias in the WHERE statement.
This fails…
WHERE pm.id = 90
AND pm.sender_id = 11
AND pm.sender_deleted_on IS NOT NULL
AND pm.sender_purged_on IS NULL
AND read_on = -1
you’re right, you cannot use a column alias in the WHERE clause
but you have a UNION query, so the question becomes…
in which of the SELECTs is this read_on = -1 WHERE condition? it really can’t be in the second one, can it, since you alrteady know that the second query produces nothing but -1 rows
and would it really be in the first query of the UNION? i don’t think so either
Not to be stupid, but why use the UNION at all? If you’re just looking for sent, why not just use the 2nd half of the union (which is where the read_on = -1 comes from)?
To be honest, my head is spinning with this Private Messaging module I took on, and I’m rather “gun shy” divulging too much after being berated by oddz the other day… (Apparently I have no clue of what I am doing.)
But you raise a valid point that makes me think I have to make more changes…
The short answer is that when a user goes to view a PM, the URL looks like this…
http://local.debbie/account/view-pm/trash/90
…where “90” is the $msgID. That combined with the $memberID yields two records when the Sender/Receiver of a PM are the same person. (All of the issues I am having relate to making sure my code works when that scenario happens. If the Sender and Reciver are different, it’s easy.)
So I needed a way to figure out which record to grab, and so I was using the “-1” in the Sent Read On column to figure out which one to grab.
Well, this query is like 60 lines, and contains a good portion of my schema so it’s sorta sensitive.
Here is more though…
Query 1: Deleted Incoming
Query 2: Deleted Sent
SELECT pm.id, r.recipient_read_on AS read_on, pm.sender_id,...
FROM ...
WHERE pm.id = 90
AND...
AND read_on = -1
GROUP BY (pm.id)
UNION
SELECT pm.id, -1 AS read_on, pm.sender_id,...
FROM...
WHERE pm.id = 90
AND pm.sender_id = 11
AND...
AND read_on = -1
GROUP BY (pm.id)
Originally I needed this UNION query, but have since figured a way to append an “s” or “i” onto the $msdID in the URL so I know whether the user wants to take action on the Sent or Incoming message.
I guess I could break this up into two separate queries…
Still, this is not an uncommon problem, so it would be interesting to know how to address it.
For a RECEIVED message, the “read_on” alias to the “r.recipient_read_on” column is either NULL or has a DATE.
For a SENT message, there is no concept of “read on”, and I needed to slot things out so my UNION would work, so I assign a ‘-1’ to a faux column called “read_on”. (Ur, an alias called “read_on”)
And I am trying to use that as a flag to tell me which of the two records returned I want.
Below is some sample data. Notice how there is no way to tell the Sent PM from the Received PM when the Sender/Receiver are the same Member.
In my URL, I pass “90”, and in the Session, I have a $memberID = “11” (not shown), but that isn’t enough info to figure out which record to grab?!
Does that matter? Originally I said, “No”, but after looking closer, the Incoming PM is “important” while the Sent PM is not, so it does matter which record I return.
And I figured using my “read_on” alias was a way to get what I needed.
so if read_on in the second SELECT is always -1 because that’s what you assign to it, then why do you need that WHERE Condition to check if it’s -1 (hint: you don’t)
Since I have two records that are nearly identical (see earlier example), if I wanted the PM that was SENT, then the above code would eliminate it.
And the corresponding code for the second query should leave me with just the SENT PM.
I admit, that this is sorta loopy. (Dare I say, that I am somewhat regretting how I “normalized” my PM’s…)
I think it is better - as David hinted to - that I have two queries for a Deleted Message: One if it is a Deleted incoming, and a second if it is a Deleted Sent.
Now that I am appending “s” or “i” to my query string, I have the ability to know whether the request is for one or the other, and so my UNION query is probably no longer needed.
But as I said above, if I needed to do what I asked in my OP, how could you do it?
Actually “I” made the problem go away because of how I appended the “s” or “i” onto “90” in the query string. (Although this still makes me feel “dirty”…)
Without know if the user wants to take action on the “Sent” or the “Incoming” version of the same PM, you would need so clue to help you figure out which record out of the two to return.
My workaround was the “-1” in the “read_on” alias.
I guess you and David got me to thinking which lead to a group solution.
Still feeling guilty about my Data Model and Queries…