CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Praveen Raj005
Praveen Raj005

Reputation: 73

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

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

Upvotes: 0

Views: 796

Answers (1)

Ramin Faracov
Ramin Faracov

Reputation: 3303

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

Upvotes: 1

Related Questions