SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY this OR something that may not exist

    Hi guys,

    I need to order a result set based on a count, which is working fine, but there may be times when an override comes in to effect and I need to get that to take precedence. I'm not entirely sure how to go about it though. I thought I'd got it, but then I realised that it was actually sorting by no order at all, unless the override value was set, at which point it fell in to place where it was supposed to.

    Basically it works like this:
    Code MySQL:
    SET @i = 0;
    SELECT t1.id
    	 , t1.name
    	 , IF (t2.pos, t2.pos, @i:=@i+1) pos
    	 , count(*) counter
      FROM t1
     LEFT
      JOIN t2
    	ON t1.id = t2.id
    GROUP BY
    	t1.groupid
    ORDER BY
    	pos ASC,
    	counter DESC
    LIMIT 10;
    I think that's right. What's basically happening is that the override value is positioning the row where it's supposed to in the resultset, but the rest of the data is out of order. Help!

  2. #2
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm wondering if I'm going to need to run this in two queries. Is that the only solution?

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Stumped you?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    Stumped you?
    apparently

    but you forgot to explain what you're trying to do

    how does the override work? or perhaps i should ask how should it work? and what's an override, anyway?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    OK, well basically what we want to do is to display our top-selling products, however every so often we may want to override this by either removing something (say if it's out of stock) or inject something new that we want to get people's attention. The actual best-selling bit is dead easy, but the override system is stumping me a little.

    Does that help?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    removing something is the easy part -- if it's gone, it won't show up in the sorted result set, right?

    adding something, yes, i can see that you might want to add something into the result set that maybe doesn't have the required sales yet

    so how did you want this override to work?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oh, the removing part is done and dusted, no problem at all. I have to provide a shed-load of parameters and I have to do something based on these parameters. That's not a problem though; PHP is handling that part.

    Basically, what happens is that there's a field in the override table that determines whether this is an include or exclude. If it's an include, there will be a value in a position field (actually `or_pos`) that says whereabouts in the results this item should appear. I can get it to appear IN the results, no problem, but I then have to perform a load of sorting in PHP to get it to work how it should. Not a major problem, but I've implemented a simple caching system whereby each query that is looking at old data (ie not todays) is saved in a file as a serialised PHP array. When the SQL is generated, it is MD5'd and we check to see if a file exists already. If it does then it's loaded and the array is just unserialised and returned.

    Does that make sense and help?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    Does that make sense and help?
    sorry, no, but i'll take a different approach

    suppose you set an or_pos value for some product

    your top-selling products query should simply retrieve the top sellers, and you can then UNION a second query to get the "positioned" products

    so you'll get 10 rows plus however many positioned products there are, and surely you can handle that in php, yes?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I did wonder about using a union TBH, but a quick shot at it yesterday didn't quite work how I expected it to. I'll give it another shot in a bit and let you know how it goes. I think I know what I did wrong TBH. I think I just needed someone to suggest it for me to invest more time in it


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
  •