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