Problem with Alias and WHERE

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

How can I fix this issue?

Sincerely,

Debbie

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

so what’s actually going on here?

any chance you can show the entire query?

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… :frowning:

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.

Not sure of any of this makes sense?!

Debbie

In the second one which is the Deleted Sent.

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.

Not sure if any of this makes sense?! :-/

Sincerely,

Debbie

my brain just exploded

why would you write an app to allow someone to PM himself?

actually, you have it in both of the SELECTs

it is absolutely not needed in the second, because -1 is the only value it’ll ever have there

and i doubt it’s needed in the first, since you wouldn’t have a valid -1 recipient id, would you

so i just made your alias problem disappear :slight_smile:

Yeah, I know, I have been sent as your punishment…

I am treating my PM module more like E-mail.

You can e-mail yourself, right?

I do it all of the time…


FROM:	DoubleDee

TO:	r937

CC:	DoubleDee

DATE:	1/24/2014

SUBJECT:	Re: My query...

Sincerely,

Debbie

I don’t follow you…

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.


id	read_on			pm_important	username_from	subject			created_on
---	--------		-------------	--------------	--------		-----------
90	2014-01-22 13:51:58	1		username1	TO: u1, FROM: u1	2014-01-21 21:40:49
90	-1			0		username1	TO: u1, FROM: u1	2014-01-21 21:40:49

Sincerely,

Debbie

just because you can, doesn’t mean it makes a lot of sense

i personally do something different – i make sure my mail program stores a copy of the email in the Sent folder

vwalah, so much simpler

allow me to point out that this here is your first SELECT in the UNION query –

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)

so if the “read_on” alias to the “r.recipient_read_on” column is either NULL or has a DATE, why are you forcing it to be -1 ???

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)

so i just made your alias probelm disappear :slight_smile:

And I “cc” myself, because I am more likely to see a message entitled “Re: Call Rudy @ 4:00pm Friday” if it is in my Inbox.

Likewise, it is easier to find things like “Re: Submittal to ACME Corp for Sr. BSA spot” if I “cc” myself and it is in my Inbox.

Hardly a sin?! :cool:

Debbie

I guess to eliminate that record.

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?

Debbie

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…

Debbie