How to compare multiple column with multiple field array in Select query?

Question

I get stuck a bit at query creation at API server, I just want to respond with modified tracks data. I have tracks table with few columns like this.

tracks(id, audio_fingerprint, name, creation_date, modified_date)

Now I just want tracks which are updated after it's last fetched timestamp (Array of audio fingerprint and last fetched timestamp passed as API request parameter).

SELECT * from tracks WHERE (audio_fingerprint, modified_date) IN (Array(audioFingerprint, > lastFetchedTimestamp));

(^^ It is invalid query, just used for understanding).

Thanks

klin · Accepted Answer · 2017-06-15 12:11:47Z

2

Example data:

create table tracks (audio_fingerprint text, modified_date date);
insert into tracks values
    ('a', '2017-01-10'),
    ('b', '2017-01-10'),
    ('a', '2017-02-10'),
    ('b', '2017-02-10'),
    ('c', '2017-02-01');

Place your arguments in a with query and join it with your table:

with given_values (fingerprint, last_fetched) as (
values
    ('a', '2017-01-01'::date),
    ('b', '2017-02-01')
)

select * 
from tracks t
join given_values v
on t.audio_fingerprint = v.fingerprint
and t.modified_date > v.last_fetched;

 audio_fingerprint | modified_date | fingerprint | last_fetched 
-------------------+---------------+-------------+--------------
 a                 | 2017-01-10    | a           | 2017-01-01
 a                 | 2017-02-10    | a           | 2017-01-01
 b                 | 2017-02-10    | b           | 2017-02-01
(3 rows)

Instead of CTE you can also use a derived table:

select * 
from tracks t
join (
    values
        ('a', '2017-01-01'::date),
        ('b', '2017-02-01')
    ) v(fingerprint, last_fetched)
on t.audio_fingerprint = v.fingerprint
and t.modified_date > v.last_fetched;

edited Jun 15, 2017 at 12:11

answered Jun 13, 2017 at 22:35

klin

123k15 gold badges240 silver badges262 bronze badges

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

6 Comments

Bipin Vayalu Over a year ago

It's working in psql but still not getting how to set audio_fingerprint and modified_date data list from java to psql statement. checking out about it...

Bipin Vayalu Over a year ago

Using WITH (CTE) with jpa native query giving token error at with. I have many dependent tables are on tracks table, those data also need to be loaded with track data. Any idea how to manage it?

Bipin Vayalu Over a year ago

Here is the issue detail: Getting NoViableAltException: unexpected token: WITH with JPA - stackoverflow.com/questions/44567017/…

Bipin Vayalu Over a year ago

JPA does not support sub-selects/derived table in the FROM clause (as I got in on SO reply), I have around 5-7 dependent table with multi-level dependency. Is there any good alternative to it?

Bipin Vayalu Over a year ago

Using Native query we can resolve this issue but then I need to use SqlResultSetMapping for each Entity, fields and it's dependent entity. Bit lengthy and complex too for later changes.

|

Super Mario · Accepted Answer · 2017-06-13 21:44:14Z

0

Maybe LEAD or LAG can help you?

answered Jun 13, 2017 at 21:44

Super Mario

93912 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to compare multiple column with multiple field array in Select query?

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related