ORDER BY this OR something that may not exist

[Antnee]

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:

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!

[Antnee]

I'm wondering if I'm going to need to run this in two queries. Is that the only solution?

[Antnee]

Stumped you?

[r937]

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?

[Antnee]

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?

[r937]

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?

[Antnee]

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?

[r937]

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?

[Antnee]

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