Skip site navigation (1) Skip section navigation (2)

Re: Reordering results for a report (follow-up)

From: Nathaniel Price <nprice(at)tesseract(dot)net>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>,pgsql-general(at)postgresql(dot)org, pgsql-php(at)postgresql(dot)org
Subject: Re: Reordering results for a report (follow-up)
Date: 2003-12-15 17:32:36
Message-ID: 3FDDF034.4010009@tesseract.net (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-php
On 12/13/2003 3:47 PM, Bruno Wolff III wrote :

>On Fri, Dec 12, 2003 at 14:40:34 -0800,
>  Nathaniel Price <nprice(at)tesseract(dot)net> wrote:
>  
>
>>Sorry, that's not really what I'm looking for. When I said that the sort 
>>order could be arbitrary, I meant /arbitrary/. As in "no amount of ORDER 
>>BYs will save me from this one" arbitrary; the records could be in any 
>>order the user specifies.
>>
>>The idea is that in PHP I'd be using an array to keep track of the 
>>custom order that the results should be returned in, using the array 
>>index to store the sorting order and the values to store the table's 
>>primary key, like so (note that this isn't the code I'm using, it's just 
>>an example):
>>
>><?php
>>$sort[0] = 3; //First record to return (3 is the primary key of the record)
>>$sort[1] = 1; //Second record to return
>>$sort[2] = 2; //Third record to return
>>... //and so on.
>>?>
>>    
>>
>
>It would be possible to use this array in a query, but this wouldn't
>work well for large data sets as you would be sending the whole array
>back and forth a lot. Presumably the reason you want to do the sort
>in the database is because the dataset is large enough that you don't
>want to buffer the data in PHP in the first place.
>  
>
I'm not sure how large the data sets will be, but yeah, I'm trying to 
keep as much processing and memory out of my PHP scripts as possible. 
That and PostgreSQL already has a well debugged sorting algorithm, so 
I'm trying not to reinvent the wheel. The other thing is that most 
likely the query will be a one time thing until the user "freezes" it 
into a final sorted order, and storing lots of data in the database just 
to handle one query seems like a pain in the rear to me.

>I think the temp table solution won't be that simple either. You will
>need connection pooling and some way to keep requests from the same
>web session using the same postgres session.
>  
>
If I created the table and then executed the query in the same script, I 
don't see why it wouldn't work. But I agree, it leaves something to be 
desired, and I am not familiar enough with how PHP handles persistant 
database connections to know what the side-effects would be.

>You may want to use regular tables which are named by session id and
>the have some clean up process that removes tables that are no longer
>needed.
>  
>
What I'll probably do is use the CASE syntax mentioned by Scott Marlowe 
to order the query until it is in a final form where it will be accessed 
frequently using the same ordering, and store /that/ ordering in the 
database in a more-or-less permanent form. If generating a query using 
CASE becomes too unwieldy, then I'll look at using a regular table with 
session ids to handle it.

Anyway, thanks for all of your help everyone. I think I've got enough 
ideas to start working on it.

-- 
___________________________
Nathaniel Price
http://www.tesserportal.net
Webmaster



In response to

pgsql-php by date

Next:From: srossDate: 2003-12-15 18:02:00
Subject: unsubscribe pgsql-php
Previous:From: scott.marloweDate: 2003-12-15 16:25:41
Subject: Re: [GENERAL] Reordering results for a report

pgsql-general by date

Next:From: CoLDate: 2003-12-15 17:33:44
Subject: Re: can't get tcl installed properly
Previous:From: Roderick A. AndersonDate: 2003-12-15 17:32:34
Subject: LIMIT modifier

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group