Well, this is somewhat of a follow up to my previous post regarding self
joins. Now what I'm hoping to do is "virtually" combine the results
from two different record sets into one apparent record set.
Here is the skeleton of my application's data structure. There is a
table called "folders" and a table called "files".
They look like:| files | folders
============= ============
x| fileid x| folderid| filename | foldername| folderid | parentid| dsply_order | dsply_order
files.folderid is fk to folders.folderid, folders.parentid is field for
self joining to folderid.
Now my difficulty is that I want to see results for the two tables
together. As you know, folders can co-exist with files inside of a
folder. Therefore if I know my current folder is 23, then I'd like to
SELECT * FROM files WHERE files.folderid = 23 AND SELECT * FROM folders
WHERE folders.parentid = 23 and then take the combined record sets and
ORDER BY dsply_order.
The only way that I know how to do this is to do two queries and put the
results into an array and sort it outside of postgres. However my goal
is to make life simpler for the application developers and just give
them a view that has the data they need.
As a side note, I'd probably need to add a field that would indicate 1
if the file came from files otherwise count(folders.*) WHERE parentid =
folderid so that I can see if the folder is empty.
As another side note, this operation will be performed quite frequently
and should be fast.
As I think about it, it seems that the only logical way would be to do
this at the application level, not inside postgres. Please correct me
if I'm wrong.
--
Matthew Nuzum
www.bearfruit.org
cobalt@bearfruit.org
