[mysql] - order by field() non tiene ordinamento casuale

[nicola75ss]

Ciao a tutti. Sono alle prese con un problema almeno per me inspiegabile.

Posto un dump di esempio

DROP TABLE IF EXISTS `domande`;

CREATE TABLE `domande` (
  `id_domanda` int(11) NOT NULL auto_increment,
  `domanda` varchar(255) default NULL,
  PRIMARY KEY  (`id_domanda`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

/*Data for the table `domande` */

insert into `domande` (`id_domanda`,`domanda`) values (1,'a');
insert into `domande` (`id_domanda`,`domanda`) values (2,'b');
insert into `domande` (`id_domanda`,`domanda`) values (3,'c');

/*Table structure for table `risposte` */

DROP TABLE IF EXISTS `risposte`;

CREATE TABLE `risposte` (
  `id_risposta` int(11) NOT NULL auto_increment,
  `id_domanda` int(11) default NULL,
  `risposta` varchar(255) default NULL,
  `corretta` tinyint(4) default NULL,
  `answer` tinyint(4) default NULL,
  PRIMARY KEY  (`id_risposta`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

/*Data for the table `risposte` */

insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (1,1,'aaaa',1,1);
insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (2,1,'bbbb',0,0);
insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (3,1,'cccc',0,0);
insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (4,1,'dddd',0,0);
insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (5,2,'eeee',1,1);
insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (6,2,'ffff',0,0);
insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (7,2,'gggg',0,0);
insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (8,2,'hhhh',1,0);
insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (9,3,'iiii',1,1);
insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (10,3,'llll',0,0);
insert into `risposte` (`id_risposta`,`id_domanda`,`risposta`,`corretta`,`answer`) values (11,3,'mmmm',0,0);

Vorrei ottenere un ordinamento delle domande casuale, quindi ad esempio prima tutte le domande con id_domanda = 3, poi 1, poi 2 e così via.

Ho scritto queste due query:

set @cas = (select group_concat(id_domanda order by rand()) from domande);
select * from risposte
order by field(id_domanda,@cas)

che però danno un risultato sballato, nel senso che gli id_domanda risultano mischiati.
Se invece alla variabile @cas sostituisco la stringa che viene generata dalla stessa query

select * from risposte
order by field(id_domanda,3,1,2)

l'ordine viene rispettato. Come mai?

Ciao e grazie.

edit. Integrando la query col php e usando la funzione shuffle() nell'order by field() tutto funziona a dovere ma vorrei capire il perchè di questo comportamento.

[Kimerio]

Con cosa le hai fatte le prove? phpMyAdmin e/o mysql_query() sotto PHP?

[nicola75ss]

Prima di integrare le query nel codice php, le testo con sqlyog .

[Kimerio]

Può essere che non mantenga la variabile nella "sessione" di query?
Mi spiego con un esempio: se provi questa query

SET @pippo = 'PIPPO';
SELECT @pippo;

che ti viene fuori?

[nicola75ss]

Intanto grazie per il tuo interessamento.

Mi restituisce correttamente 'PIPPO'. Premetto che uso sempre sqlyog e non ho mai avuto alcun problema con l'impiego di variabili.

[Kimerio]

Spetta spetta... se mi documentassi meglio farei più bella figura

A quanto vedo la funzione field() accetta più parametri, (secondo me) se gli passi la variabile @cas come fai tu, il compilatore vede qualcosa del genere field(id_domanda, '3,2,1') e non field(id_domanda, 3, 2, 1) (notare l'assenza degli apici)

Che dici? C'ho azzeccato?

[nicola75ss]

Originariamente inviato da Kimerio
Spetta spetta... se mi documentassi meglio farei più bella figura

A quanto vedo la funzione field() accetta più parametri, (secondo me) se gli passi la variabile @cas come fai tu, il compilatore vede qualcosa del genere field(id_domanda, '3,2,1') e non field(id_domanda, 3, 2, 1) (notare l'assenza degli apici)

Che dici? C'ho azzeccato?

Adesso faccio un pò di ulteriori verifiche e ti faccio sapere. Intanto grazie.

[nicola75ss]

Rimango perplesso.

Seguendo questa procedura ho il comportamento che mi aspetterei

$query1 = mysql_query("set @cas = (select group_concat(id_domanda order by rand()) from domande)") or die(mysql_error());
$query2 = mysql_query("select @cas"); // se aggiungo questa query funziona, senza no.
$row = mysql_fetch_row($query2);
$query3 = mysql_query("select * from risposte order by field(id_domanda,$row[0])");

while ($row = mysql_fetch_assoc($query3)) {
  echo $row['id_risposta']. ' '. $row['id_domanda'].' '.$row['risposta'].'
';
} 

ma non capisco la ragione per la quale debba aggiungere la seconda query e non possa usare direttamente la mia variabile.

[nicola75ss]

Nel caso possa servire a qualcun altro la soluzione sql che ho trovato è questa

set @cas = (select group_concat(id_domanda order by rand()) from domande);
set @ordine = concat("select * from risposte order by field(id_domanda,",@cas,")");
prepare stmt FROM @ordine;
execute stmt;
deallocate prepare stmt;

edit. O meglio ancora così

set @cas = (select group_concat(id_domanda order by rand()) from domande);
set @ordine = concat("select * from risposte order by field(id_domanda,",@cas,"),rand(),id_risposta");
prepare stmt from @ordine;
execute stmt;
deallocate prepare stmt;

per rendere casuale non solo l'ordine delle domande ma anche quello di comparsa delle possibili risposte.

[Kimerio]

Originariamente inviato da nicola75ss
Rimango perplesso.

Seguendo questa procedura ho il comportamento che mi aspetterei

$query1 = mysql_query("set @cas = (select group_concat(id_domanda order by rand()) from domande)") or die(mysql_error());
$query2 = mysql_query("select @cas"); // se aggiungo questa query funziona, senza no.
$row = mysql_fetch_row($query2);
$query3 = mysql_query("select * from risposte order by field(id_domanda,$row[0])");

while ($row = mysql_fetch_assoc($query3)) {
  echo $row['id_risposta']. ' '. $row['id_domanda'].' '.$row['risposta'].'
';
} 

ma non capisco la ragione per la quale debba aggiungere la seconda query e non possa usare direttamente la mia variabile.

Se non fai la seconda query, ma passi la variabile direttammente alla funzione field(), la variabile verrà presa come singolo valore anche se conterrà valori separati da virgola, cioè verrà vista come singola stringa.
Quando passi una query alla funzione mysql_query() tu passi una stringa che viene interpretata così com'è, se passi la query con $row[0] il php prima tradurrà la variabile in '1, 2, 3' e poi passerà la query al server MySql.

E' più chiaro?