0

I have a table that looks like this:

stuff
    id
    content
    score
    children[]

I'd like to run a query that selects all the children for a given id, and then right away gets the full records for all these children sorted by score. The children field holds an array of ids.

I need to do something like this:

SELECT children as array 
FROM stuff 
where id=1

then

SELECT * FROM stuff 
WHERE id=(any id from previous select) 
SORT BY score DESC

Children has an array of ids that reference the same table, just like a person (id) can have children (an array of people). I want the full records for all the kids of a single parent, sorted by score.

Any suggestions on the best way to do this? I've looked into WITH RECURSIVE but I'm not sure that's workable.

closed as unclear what you're asking by mustaccio, Philᵀᴹ, ypercubeᵀᴹ, MDCCL, billinkc Oct 5 '16 at 18:42

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Like the author of the answer says: please add some detail (all tables involved, some example data and the desired output. – dezso Sep 17 '16 at 7:21
  • 1
    select * from stuff where id = any((select array_agg(children) from stuff where id = 1)) – a_horse_with_no_name Sep 17 '16 at 13:22
  • 2
    Seems like your design is backwards. Normally you don't have an array column storing children IDs. Instead, it's much more common to have a (scalar-valued) column storing the parent ID, assuming each row can have at most one parent. (If rows need to have more than one parent, a junction table is a common solution.) – Andriy M Oct 3 '16 at 10:00
  • 2
    In any event, if this is a multi-level hierarchy, where children you invoke can have children of their own and you want to return everything down the hierarchy, then using a recursive CTE is a typical method to solve this. – Andriy M Oct 3 '16 at 10:03
1

I have to admit, I'm not sure what you're asking for. You've left out important items in your schema, like what are the "full records" you wish to pull?

If you have control over the schema, perhaps you would be better off altering it. It seems like your trying to store test scores, but store an array of children for each score received. Now it may be just me, but perhaps you might be better off splitting this into multiple tables.

stuff
  stuff-id
  content

children
  child-id
  child-fname
  child-lname
  child-seat
  child-wild
  gum-chewer-bit

scores
  child-id
  stuff-id
  score

In this setup you can construct a query that matches records on the stuff-id and uses a join to pull data from the other tables.

If you DON'T have access to the schema, it may still be possible using IN (). Without having a clearer understanding of the schema my suggestion is to use join queries while passing that array to IN. That being said, I'm not sure it could work that way as I'm not sure you can construct the join in this fashion (hence the multiple tables and not using an array field).

Now with a stored procedure, the answer is a definite "yes, you can".

You would loop through each stuff-id, split out the array values (or pass them directly/indirectly with IN), loop through the child records, and flatten it out into a single record per child. With stored procedures you have quite a bit of control over each record you "suspend" (old Firebird dude here) with 'Returns Sets'.

To sort by score, you would achieve this by sorting the output of your stored procedure.

Don't give up either. Postgresql is really amazing in what you can do, and I recommend camping in their online manuals.

Not the answer you're looking for? Browse other questions tagged or ask your own question.