CODEWITHSUNDEEP
pythonsqldjangopostgresqldjango-models
MoRe
MoRe

Reputation: 1538

Filter SQL elements with adjacent ID

I don't really know how to properly state this question in the title.

Suppose I have a table Word like the following:

| id  | text   |
| --- | ---    |
|  0  | Hello  |
|  1  | Adam   |
|  2  | Hello  |
|  3  | Max    |
|  4  | foo    |
|  5  | bar    |

Is it possible to query this table based on text and receive the objects whose primary key (id) is exactly one off?

So, if I do

Word.objects.filter(text='Hello')

I get a QuerySet containing the rows

| id  | text   |
| --- | ---    |
|  0  | Hello  |
|  2  | Hello  |

but I want the rows

| id  | text   |
| --- | ---    |
|  1  | Adam   |
|  3  | Max    |

I guess I could do

word_ids = Word.objects.filter(text='Hello').values_list('id', flat=True)
word_ids = [w_id + 1 for w_id in word_ids]  # or use a numpy array for this
Word.objects.filter(id__in=word_ids)

but that doesn't seem overly efficient. Is there a straight SQL way to do this in one call? Preferably directly using Django's QuerySets?

EDIT: The idea is that in fact I want to filter those words that are in the second QuerySet. Something like:

Word.objects.filter(text__of__previous__word='Hello', text='Max')

Upvotes: 1

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

If by "1 off" you mean that the difference is exactly 1, then you can do:

select w.*
from w
where w.id in (select w2.id + 1 from words w2 where w2.text = 'Hello');

lag() is also a very reasonable solution. This seems like a direct interpretation of your question. If you have gaps (and the intention is + 1), then lag() is a bit trickier.

Upvotes: 1

S-Man
S-Man

Reputation: 23716

In plain Postgres you could use the lag window function (https://www.postgresql.org/docs/current/static/functions-window.html)

SELECT 
    id, 
    name 
FROM (
    SELECT 
        *, 
        lag(name) OVER (ORDER BY id) as prev_name
    FROM test
) s
WHERE prev_name = 'Hello'

The lag function adds a column with the text of the previous row. So you can filter by this text in a subquery.

demo:db<>fiddle

I am not really into Django but the documentation means, in version 2.0 the functionality for window function has been added.

Upvotes: 2

Related Questions