select array value like foreign key way join multiple table

Question

I have table like below

table

CREATE TABLE IF NOT EXISTS "Article"(
"ArticleId" SERIAL NOT NULL,    
"GenresIdList" integer[],
...
PRIMARY KEY ("ArticleId")
);

CREATE TABLE IF NOT EXISTS "Tag0"(
"TagId" SERIAL NOT NULL,
"Name" varchar,
...
PRIMARY KEY ("TagId")
);

ArticleId | GenresIdList  
1 | {1} |  
2 | {1} |  
3 | {1,2} |   
4 | {1,2,3} | 

TagId | Name
1 | hiphop
2 | rock

When user input data inputGenres I want get below result:
if inputGenres = ['hiphop','rock','classical']; then will get no rows in Article
if inputGenres = ['hiphop','rock']; get Article rows 3 and 4

but because I select two table separate then even I use && in select article table when inputGenres = ['hiphop','rock','classical']; when convert to id array I will become [1,2] because there is no classical, then I will get rows 3 and 4.
How to solve this?

ps. I have to design table like this, only store id not store name in 'Article'. so I hope not redesign table

code (with nodejs)

// convert inputGenres to tag0TagIdList
var tag0TagIdList = [];

var db = dbClient;
var query = 'SELECT * FROM "Tag0" WHERE "Name" IN (';
for (var i = 0; i < inputGenres.length; i++) {
  if (i > 0) {
    query += ',';
  }
  query += '$' + (i + 1);
}
query += ') ORDER BY "Name" ASC';
var params = inputGenres;

var selectTag0 = yield crudDatabase(db,query,params);

for (var i = 0; i < selectTag0.result.rows.length; i++) {
  tag0TagIdList.push(selectTag0.result.rows[i].TagId);
}
// end: convert inputGenres to tag0TagIdList


var db = dbClient;
var query = 'SELECT * FROM "Article" WHERE "GenresIdList" && $1';
var params = [tag0TagIdList];
var selectArticle = yield crudDatabase(db,query,params);

LongBeard_Boldy · Accepted Answer · 2015-12-04 14:16:37Z

1

var tag0TagIdList = [];

var db = dbClient;
var query = 'select * from "Article" where "GenresIdList" @> (select array_agg ("TagId")  from  unnest (array[';
for (var i = 0; i < inputGenres.length; i++) {
  if (i > 0) {
    query += ',';
  }
  query += '$' + (i + 1);
}
query += ']) as input_tags left join "Tag0" on ( "Name" = input_tags))';

I don't know java much, but this should return what you want.

query example:

SELECT * FROM "Article"
WHERE
"GenresIdList" @> (
    SELECT
        array_agg ( "TagId" )
    FROM
        unnest (ARRAY [ 'hiphop', 'rock' ] ) AS input_tags
    LEFT JOIN "Tag0" ON (
        "Name" = input_tags ) )

edited Dec 4, 2015 at 14:16

answered Dec 4, 2015 at 14:09

LongBeard_Boldy

9128 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select array value like foreign key way join multiple 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