[TYPO3-dev] Order result of query based on comma-seperated string

[bernd wilke]

on Thu, 02 Oct 2008 08:20:43 +0200, Sacha Vorbeck wrote:

> Hello Oliver,
> 
>> The 5th parameter of SELECT_query() can be set to the sort column of
>> the table. But I guess you'd like to have a very specific sorting as
>> provided in the string $bookIDs ("3,1,4,2")?
> 
> exactly.
> 
> Therefore you can use the
>> "ORDER BY FIELD()" in MySQL - however this query is not very efficient
>> compared to database normalization...
> 
>>      'FIELD(uid, ' . $bookIDs . ')',
> 
> thank you very much. Efficiency is not that important here as there are
> only a few records. But of course it would be better to otimize things
> right from the start. So what would be the best/most efficient way to
> code this? Do the sorting in PHP?

I think that depends.

depends on available memory and size of fields.

one solution (based on comma-separated strings) could be the reading of 
all records in memory and then access by index

tabel structure:
data.commalist-field -> refdata

select * from refdata where uid in (data.commalist)

while ($rec=sql_result())
	$arr[$rec['uid']]=$rec
$indlist=explode(',',$data['commalist']);
foreach ($indlist as $ind) 
	dowork($arr[$ind]);
(time-consumation in PHP)

the pure solution would be real mn-records with a sorting and some joins

based on a table structure:
data <-mn-data_refdata -> refdata

select * from refdata, mn-data_refdata 
  where refdata.uid = mn-data_refdata.foreignuid 
  order by mn-data_refdata.sorting
(time-consumation in SQL)


this might be some overhead (database-memory) for references to some 1-5 
records. but more efficient for references to some hundred records

can someone record the times for differnt solutions?

bernd
-- 
http://www.pi-phi.de/t3v4/cheatsheet.html