[MySQL/PHP] Ingewikkelde sortering

[bindsa]

Ik loop tegen een probleem aan. Ik wil de volgende set sorteren:

kolom	id	type_id		foo	
	1	2		512
	2	1		312
	3	4		100
	4	2		312
	5	1		400
	6	1		346
	7	3		400	

Moet worden:

kolom	id	type_id		foo
	2	1		312
	4	2		312
	5	1		400
	7	3		400
	6	1		346	
	1	2		512
	3	4		100

Er moet dus als volgt gesorteerd worden:
1. Alle records met type_id = 1 moeten bovenaan (1 is niet de echte waarde, kan ook 38 zijn, het gaat om één specifiek getal)
2. Daarna wordt er gesorteerd op het hoe vaak 'foo' voorkomt, aflopend
3. MAAR: Als er records zijn met een type_id != 1 maar een foo waarde die ook bij een record met type_id = 1 voorkomt, dan moet dit record direct na dit record geplaatst worden.

De crux zit hem dus in die 3e beperking.

1. is te doen met:

SQL:

ORDER BY FIELD(type_id, '1')

1 & 2 is te doen met (waarbij count_kolom een kolom is met COUNT() waarden die middels een subquery gevormd wordt):

SQL:

ORDER BY FIELD(type_id, '1') DESC, count_kolom DESC

Alleen voor 1,2 en 3 heb ik nog geen oplossing. Ik heb het geprobeerd met usort() en dan een eigen functie, maar heb nog geen werkende compare functie gevonden.

Achterliggende probleem: Het gaat om een database van personen die ergens wonen. Ik wil een overzicht tonen waarin alle plaatsen op een rij staan, de meestvoorkomende eerst. Maar nu zijn er ook personen die betalen, hun woonplaatsen (en alle andere mensen die daar ook wonen) moet bovenaan staan, nog voor de meestvoorkomende.

[Brainstorm]

Een idee: voeg een kolom toe die aangeeft of 'foo' voorkomt in type 1. Je krijgt dan zo'n dataset:

kolom	id	type_id		foo	    has_id_one
	1	2		512	    0
	2	1		312	    1
	3	4		100	    0
	4	2		312	    1
	5	1		400	    1
	6	1		346	    0
	7	3		400	    1

Vervolgens sorteer je als eerste op deze pseudokolom:

code:

ORDER BY has_id_one DESC

Volgens mij kun je door middel van dit idee en wat je al had, bereiken wat je wilt? De implementatie hangt ook een beetje af van de daadwerkelijke structuur.

[bindsa]

Inderdaad het werkt, alleen wordt het een mega ranzige query:

SQL:

SELECT accounts.*, c2.cnt AS cnt, 
    IF(
        accounts.foo 
        IN(
            SELECT DISTINCT foo 
            FROM accounts 
            WHERE type_id = 1), 
    1, 0) AS has_id
FROM (accounts)
JOIN (
    SELECT foo, COUNT(foo) AS cnt 
    FROM accounts 
    GROUP BY foo) AS c2 
ON c2.foo = accounts.foo
ORDER BY vip_city desc, c2.cnt desc, FIELD(accounts.type_id, 1) desc

Ik heb het gevoeld dat dit efficiënter kan

[ Voor 10% gewijzigd door bindsa op 22-01-2012 21:51 ]

[pedorus]

Misschien iets met subqueries/joins, als in

SQL:

select ...
from
    (select ..., count(a2.id) as foocount
    from accounts a1 
        inner join accounts a2 on a1.foo=a2.foo 
    group by a1.id) as a
    left join accounts a3 on (a.foo=a3.foo and a3.type_id=1)
order by isnull(a3.id), a.foocount, a.foo, a.type_id, a.id

En dan eens kijken of indexen goed gebruikt worden.

[SvMp]

ORDER BY (type=1) werkt gewoon.