0

Things go inside other things. More than one can go inside and each one can be inside many different containers. This is for a on-line shopping site where assorted things that we get from our suppliers are bundled together, given a web-page and then sold as that collective. It all used to be tracked with a bunch of horrible CSV files but I'm setting up a proper database now.

create table collections(
    sku character varying(50),
    components text[]);

create table products(
    ordering_id character varying(50),
    inside text[] );

inside's elements are skus from collections and components elements are ordering_ids from products.

The contents of these easily fill in both columns in the products table and sku in collections. Now I want to fill in components: preferably with some tricky SQL statement. The best I could come up with is:

with new_values as ( select ordering_id, inside from products )
    update collections set components = components ||   
        nv.ordering_id::text  from new_values nv
    where collections.sku::text = any( nv.inside );

but that only fills in the first product that's found inside each collections. Why is that? Do I need to do some sort of JOIN to get this to work?

Sample data:

collections

sku    components
10A    {}
20A    {}

products

ordering_id    inside
1234            {10A}
5678            {10A, 20A}

should leave products unchanged but collections should now be

collections

sku    components
10A    {1234,5678}
20A    {5678}

Unfortunately what I get from the SQL that I came up with above is

sku    components
10A    {1234}
20A    {5678}
  • Please, add some sample data and the desired result. – McNets Jun 17 '17 at 18:06
  • Ok, added them. – Nadreck Jun 18 '17 at 8:20
2

The following aggregates the products as you want it:

select c.sku, array_agg(p.ordering_id) as ids
from collections c
  join products p on c.sku = any(p.inside)
group by c.sku;

The above returns:

sku | ids        
----+------------
10A | {1234,5678}
20A | {5678}     

Now this can be used as the source of an update statement:

update collections 
   set components = t.ids
from (
    select c.sku, array_agg(p.ordering_id) as ids
    from collections c
      join products p on c.sku = any(p.inside)
    group by c.sku
) t
where collections.sku = t.sku;
  • I figured it had something to do with a JOIN. My JOIN-fu is weak. – Nadreck Jun 18 '17 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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