[MySQL] MAX e MIN su Categorie e Forums

[zacca94]

Se qualcosa non è chiaro domandate per favore.

Query originale:

SELECT f.*, c.* FROM #__categories c
LEFT JOIN #__forums f ON f.forum_cat_id = c.cat_id
WHERE c.cat_language = %d
ORDER BY c.cat_disp_position, c.cat_id, f.forum_disp_position, f.forum_id ASC

Risultati (forum_disp_position è l'order):

Categoria 1
---- Forum 1 --- Order 1
---- Forum 2 --- Order 2
---- Forum 3 --- Order 3

Categoria 2
---- Forum 4 --- Order 1
---- Forum 5 --- Order 2
---- Forum 6 --- Order 3

Categoria 3
---- Forum 7 --- Order 1
---- Forum 8 --- Order 2
---- Forum 9 --- Order 3

Io vorrei estrarre il MAX e MIN di forum_disp_position in modo tale da poter inserire delle freccette per spostare "sotto e sopra" i forum e mi serve sapere qual'è il MAX di un forum e il MIN di un forum di una determinata categoria per non stampare la freccetta SU quando si tratta del primo forum e GIU' quando si tratta dell'ultimo... (immagine: http://s23.postimg.org/xshp06rcr/forum.jpg) Le tabelle sono:

CREATE TABLE IF NOT EXISTS `#__categories` (
`cat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cat_name` varchar(80) NOT NULL DEFAULT '',
`cat_disp_position` int(10) NOT NULL DEFAULT '0',
`cat_language` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `#__forums` (
`forum_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`forum_name` varchar(80) NOT NULL DEFAULT '',
`forum_disp_position` int(10) NOT NULL DEFAULT '0',
`forum_cat_id` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`forum_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;




Se provo con questa query:
SELECT f.*, c.*, MAX(f.forum_disp_position) AS max_order, MIN(f.forum_disp_position) as min_order
FROM #__categories c
LEFT JOIN #__forums f ON f.forum_cat_id = c.cat_id
WHERE c.cat_language = %d
GROUP BY f.forum_id
ORDER BY c.cat_disp_position, c.cat_id, f.forum_disp_position, f.forum_id ASC

Il dump mi restituisce:

Array
(
    [forum_id] => 4
    [forum_name] => 1 Forum
    [forum_disp_position] => 1
    [forum_cat_id] => 1
    [cat_id] => 1
    [cat_name] => 1 Categoria
    [cat_disp_position] => 1
    [cat_language] => 1
    [max_order] => 1
    [min_order] => 1
)


Array
(
    [forum_id] => 5
    [forum_name] => 2 forum
    [forum_disp_position] => 2
    [forum_cat_id] => 1
    [cat_id] => 1
    [cat_name] => 1 Categoria
    [cat_disp_position] => 1
    [cat_language] => 1
    [max_order] => 2
    [min_order] => 2
)


Array
(
    [forum_id] => 6
    [forum_name] => 3 forum
    [forum_disp_position] => 3
    [forum_cat_id] => 1
    [cat_id] => 1
    [cat_name] => 1 Categoria
    [cat_disp_position] => 1
    [cat_language] => 1
    [max_order] => 3
    [min_order] => 3
)


Array
(
    [forum_id] => 7
    [forum_name] => 4 forum
    [forum_disp_position] => 1
    [forum_cat_id] => 2
    [cat_id] => 2
    [cat_name] => 2 Categoria
    [cat_disp_position] => 2
    [cat_language] => 1
    [max_order] => 1
    [min_order] => 1
)


Array
(
    [forum_id] => 8
    [forum_name] => 5 forum
    [forum_disp_position] => 2
    [forum_cat_id] => 2
    [cat_id] => 2
    [cat_name] => 2 Categoria
    [cat_disp_position] => 2
    [cat_language] => 1
    [max_order] => 2
    [min_order] => 2
)


Array
(
    [forum_id] => 9
    [forum_name] => 6 forum
    [forum_disp_position] => 3
    [forum_cat_id] => 2
    [cat_id] => 2
    [cat_name] => 2 Categoria
    [cat_disp_position] => 2
    [cat_language] => 1
    [max_order] => 3
    [min_order] => 3
)


Array
(
    [forum_id] => 10
    [forum_name] => 7 forum
    [forum_disp_position] => 1
    [forum_cat_id] => 3
    [cat_id] => 3
    [cat_name] => 3 Categoria
    [cat_disp_position] => 3
    [cat_language] => 1
    [max_order] => 1
    [min_order] => 1
)


Array
(
    [forum_id] => 11
    [forum_name] => 8 forum
    [forum_disp_position] => 2
    [forum_cat_id] => 3
    [cat_id] => 3
    [cat_name] => 3 Categoria
    [cat_disp_position] => 3
    [cat_language] => 1
    [max_order] => 2
    [min_order] => 2
)


Array
(
    [forum_id] => 12
    [forum_name] => 9 forum
    [forum_disp_position] => 3
    [forum_cat_id] => 3
    [cat_id] => 3
    [cat_name] => 3 Categoria
    [cat_disp_position] => 3
    [cat_language] => 1
    [max_order] => 3
    [min_order] => 3
)

Mentre io voglio che il "forum 1", "forum 2" e "forum 3" mi stampino sempre max_order = 3 e min_order = 1 e non 1, 2 e 3.

[zacca94]

up