SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need help with a query

    Hello everyone. I've got this rather complex (well, for me) query I can't get to work right. Here's what I need to do.

    Select the photo from the album, where:

    1. the access to this photo is below 3 or the photo belongs to me.
    2. the access to the album where this photo is located is below 3 or the album belongs to us.

    The album access rule comes first when checking, so the photo access matters only if the album access value is ok for us to access it.

    --------------
    Ok, so we got passes those rules, now deal with access = 2.

    3. if photo access = 2, select only if we are listed in the friends table.
    4. if album access = 2, select only if we are listed in the friends table.

    Same rule ordering.

    5. select if photo access = 1
    6. select if album access = 1

    Same rule ordering.

    Access values (if you haven't guessed yet) - 1/2/3 = public/protected/private

    Is this achievable in one SQL call? Or should I just sort the things out programically?

    Please post some pseudo SQL code if you feel it is possible, or say so if it's not. Thank you.

  2. #2
    SitePoint Zealot scojo1's Avatar
    Join Date
    Aug 2005
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I'm a little cofused (which isn't too hard to do)

    you said
    The album access rule comes first when checking, so the photo access matters only if the album access value is ok for us to access it.
    to me this makes it sound like there can be photos in an album with access 3 with access less than 3. Is this true?

    Another thought/question:
    Does every photo have to belong to an album?

  3. #3
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply!

    Yes, if the photo access = 1, but the album access is set to 3, we can't view the photo. Photos are just childs, where the parent is the album. As for your second question, yes, every photo resides in an album, and only one (cannot be associated with more than one album).

  4. #4
    SitePoint Zealot scojo1's Avatar
    Join Date
    Aug 2005
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT photo FROM album WHERE (photo_access < 3 OR photo_owner = me) OR
                                  (album_access < 3 OR album_owner = me) OR
                                  (photo_access = 2 AND friend = yes) OR 
                                  (album_access = 2 AND friend = yes) OR
                                  (photo_access = 1 AND album_access = 1)
    I hope this helps, if this isn't at all what you're looking for, let me know and I'll keep at it

    **I'm not really sure how you're setting up album friends so friend = yes is probably wrong, but hopefully it makes sense

  5. #5
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks scojo1, I really appreciate your help, but for some reason, it doesn't seem to work, tested in all modes now.

    Btw, I've modified the query a bit, because information's lies in different tables, so it now is:
    Code:
    SELECT 
       photos.id, photos.album_id, photos.file_name 
     FROM
       photos, albums, friends 
     WHERE 
      (photos.access < 3 OR albums.user_id = "'.$this->user->getUserId().'") 
     OR 
      (albums.access < 3 OR albums.user_id = "'.$this->user->getUserId().'") 
     OR 
      (photos.access = 2 AND "'.$this->user->getUserId().'" IN (SELECT friends_id FROM friends)) 
     OR
      (albums.access = 2 AND "'.$this->user->getUserId().'" IN (SELECT friends_id FROM friends)) 
     OR (photos.access = 1 AND albums.access = 1)
     ORDER BY RAND() LIMIT 1
    I feel JOINS should be used here, as friends table can be empty. (but subqueries are converted to joins internaly right? Anyways, this IN (SELECT ..) should do the job, don't know why it doesn't work.)

  6. #6
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    P.S by doesn't work I mean - returns nothing all the time

  7. #7
    SitePoint Zealot scojo1's Avatar
    Join Date
    Aug 2005
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by n0other
    Anyways, this IN (SELECT ..) should do the job, don't know why it doesn't work.)
    I agree with you here, I don't know why it won't work..... anyone else out there have any ideas?

  8. #8
    SitePoint Zealot scojo1's Avatar
    Join Date
    Aug 2005
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried breaking the query up in to more simple queries before putting them all together?

  9. #9
    SitePoint Zealot scojo1's Avatar
    Join Date
    Aug 2005
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    also, I agree that you need joins

    I seem to have glossed over that for some reason but even without joins, am I correct in assuming that if the query was somewhat correct, it would return results, just erroneous (too many) results?

  10. #10
    SitePoint Addict n0other's Avatar
    Join Date
    Feb 2005
    Posts
    290
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes it should, I'll have another look at this abit later. The conditions somewhere must be not met and that results into an empty result set.

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    n0oother's original description is misleading because it causes you to think about the rules in the papttern he laid out, when in fact that's the wrong way to do it. think about it this way:

    1) if the album is public, you can view the album. if the album is protected, you can see it if you're a friend. if the album is private, you can see it if you're the owner.

    --and--

    2) if the photo is public, you can view the photo. if the photo is protected, you can see it if you're a friend. if the photo is private, you can see it if you're the owner.

    notice the --and-- in there? that guarantees that if you don't already have access to the album through some manner, then you can't see the photos.

    using that logic, the query is simple:
    Code:
    select distinct
           p.title
         , p.description
         , p.url
         , ...
      from photos p
      join albums a
        on a.album_id = p.album_id
    left outer
      join friends f
        on p.owner_id = f.parent_id
       and f.child_id = $my_id
     where album_id = $select_album
       and (    a.access = 1
            or (a.access = 2 and f.child_id is not null)
            or (a.access = 3 and a.owner_id = $my_id) )
       and (    p.access = 1
            or (p.access = 2 and f.child_id is not null)
            or (p.access = 3 and p.owner_id = $my_id) )


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
  •