How do joins and query array of ids as a string into another table?

Question

I'm storing the multiple ids as string in another table. Now I need to join id's table and query the joined table

Model

user.rb

#columns
id:
name: string

# associations
has_one :user_store

store.rb

#columns
id:
name: string

user_store.rb

#columns
id: integer
user_id: integer
store_ids: string

#association
belongs_to :user

I store the multiple store ids in user_store's store_ids column

example UserStore.first =>

#<UserStore:0x00005636e9ae7358
 id: 1,
 user_id: 1,
 stores: "31, 32, 33, 34, 35",
 created_at: Wed, 25 Aug 2021 19:24:37.292280000 UTC +00:00,
 updated_at: Wed, 25 Aug 2021 19:24:37.292280000 UTC +00:00>

How do I query to Store table from UserStore?

Ruby - 3.0.1

Rails - 6.1.3.2

Postgres - 10

"I'm storing the multiple ids as string in another table." - Don't do that. Create a join table instead to avoid violating first normal form. This will let you simply join the table with a sane query. medium.com/pragmatic-programmers/… — max
– max, Commented Oct 29, 2021 at 11:20

tdy · Accepted Answer · 2021-11-01 04:21:03Z

You can use the Postgres function string_to_array to convert this "31,32,33,34,35" string type to array type and use the unnest function to convert array elements to records.

For example:

CREATE TABLE user_store (
    id serial4 NOT NULL,
    user_id int4 NOT NULL,
    store_id int4 NOT NULL,
    created_at timestamp NOT NULL,
    updated_at timestamp NULL
);

CREATE OR REPLACE FUNCTION insert_user_store(p_user_id integer, p_store_id character varying)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
begin
     
    insert into user_store 
    (
        user_id,
        store_id,
        created_at 
    )
    select 
        p_user_id, 
        t1.store_ids::integer, 
        now()
    from 
        unnest(string_to_array(p_store_id, ',')) t1 (store_ids);
    
    return true; 
    
exception 
when others then 
    return false; 

END;
$function$
;

Calling this function for testing:

select * from test.insert_user_store(1, '31,32,33,34,35');

Result:

user_id	store_id	created_at
1	31	2021-11-01 07:31:22.351
1	32	2021-11-01 07:31:22.351
1	33	2021-11-01 07:31:22.351
1	34	2021-11-01 07:31:22.351
1	35	2021-11-01 07:31:22.351

Collectives™ on Stack Overflow

How do joins and query array of ids as a string into another table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related