Any work around to query filter every ID in a list using in_() including duplicates?

Question

I am trying to do something like this with SQLAlchemy:

Cards.query.filter(Cards.id.in_(inventory_cards)).paginate(page=page, per_page=50)

But the in_() operator only checks if the id is in the list and ignore duplicates. I have seen similar questions but they are not very clear or they are from 2014.

I solution I tried is loop it using a list comprehension, but as the result is a list I cannot apply the pagination afterwards. (Apart of being much slower because is making a query for every id, and sometimes there are thousands of cards in the inventory)

If anyone know a good solution for this I would appreciate it A LOT.

Thank you very much.

Answer 1

I believe what you need is a JOIN, not a WHERE...IN clause.

Create a temporary table with your inventory cards and join on it. Each repetition of the id in the temporary table will cause all matching lines to be pulled from the Cards table.

I am writing the SQL, because I do not know the correct SQLAlchemy syntax.

CREATE TEMP TABLE inventory_cards (
  id int
);
INSERT INTO inventory_cards VALUES ((1), (2), (3), (1), (2));
SELECT * FROM Cards JOIN inventory_cards USING (id);

It seems that there is a values() function that can create a table literal, so my guess is that it would be similar to:

Cards.query.join(
  values(column("id", Integer)).data(inventory_cards)
).paginate(page=page, per_page=50)