SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    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..
    Code:
    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...
    Code:
    -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...
    Code:
    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    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)?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    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...
    Code:
    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

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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?
    In the second one which is the Deleted Sent.


    Quote Originally Posted by r937 View Post
    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?
    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
    Code MySQL:
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    That combined with the $memberID yields *two* records when the Sender/Receiver of a PM are the same person.
    my brain just exploded

    why would you write an app to allow someone to PM himself?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    In the second one which is the Deleted Sent.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    my brain just exploded
    Yeah, I know, I have been sent as your punishment...


    Quote Originally Posted by r937 View Post
    why would you write an app to allow someone to PM himself?
    I am treating my PM module more like E-mail.

    You can e-mail yourself, right?

    I do it all of the time...
    Code:
    FROM:	DoubleDee
    
    TO:	r937
    
    CC:	DoubleDee
    
    DATE:	1/24/2014
    
    SUBJECT:	Re: My query...
    Sincerely,


    Debbie

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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.

    Code:
    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

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    You can e-mail yourself, right?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    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.
    allow me to point out that this here is your first SELECT in the UNION query --
    Code:
    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 ????
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I don't follow you...

    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")
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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?!


    Debbie

  14. #14
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    allow me to point out that this here is your first SELECT in the UNION query --
    Code:
    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 ????
    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

  15. #15
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •