How to return different rows from the same table on single query as a list

Question

I have a table which has a boolean column, this column is used to filter some responses. I'm in the need to return a response as a tuple as {claimed, unclaimed} (Imagine the table is called winnings)

While working on it I've done two separate queries to return claimed then unclaimed rows and manually constructing the response, then I went with returning all rows without checking the boolean column and splitting it outside of the query. Now I'm wondering if there's a way I can run a single query on the same table and return both claimed and unclaimed as separate results mainly for performance hoping it runs better. I've tried doing it with joins but its returning a list of two items tuples like:

[{claimed, unclaimed}, {claimed, unclaimed}]...

While I want:

{claimed, unclaimed}

# OR

[{claimed, unclaimed}]

At most, no more tuples. Note that I'm not running the raw queries but using a library so excuse if the terminology is not right.

This is the last query I ran:

SELECT w0."claimed", w1."claimed"
FROM "winnings" AS w0
INNER JOIN "winnings" AS w1 ON TRUE
WHERE (w0."claimed" AND NOT (w1."claimed"))
LIMIT 10;

EDIT: More details.

When I run the query from above this is the result I get:

=> SELECT w0."claimed", w1."claimed" FROM "winnings" AS w0 INNER JOIN "winnings" AS w1 ON TRUE WHERE (w0."claimed" AND NOT (w1."claimed")) LIMIT 10;

 claimed | claimed 
---------+---------
 t       | f
 t       | f
 t       | f
 t       | f
 t       | f
 t       | f
 t       | f
 t       | f
 t       | f
 t       | f
(10 rows)

This is converted to the following on Elixir which is the language I'm using:

[
  true: false,
  true: false,
  true: false,
  true: false,
  true: false,
  true: false,
  true: false,
  true: false,
  true: false,
  true: false
]

This is a keyword list which internally is a list of tuples as [{true, false}, {true, false}] - I want: [{[true, true], [false, false]}]

Means that I want 2 lists, each list with their respective rows, only claimed on one and only unclaimed on the other one.

I don't really mind the type it outputs as long as it includes two lists with their rows how I said.

Sorry, I don't understand. A query returns a table, i.e. columns and rows. Can you please show an example, say, the winning table with six rows and then show the result you expect from these rows. — Thorsten Kettner
@ThorstenKettner sorry I wasn't clear enough. I just posted a more detailed example of the result I'm expecting. — Aguxez
So this is mainly about how to select data in a certain format in Elixir. I've tagged your request with elixir hence. Hope someone knowing that language can help. — Thorsten Kettner
Thanks @ThorstenKettner - I didn't put that in place the first time because I thought it was lang agnostic as I can simply do the same on SQL within the language or I could translate whatever SQL query is given to me to Elixir. — Aguxez
It’s impossible to understand what are you trying to achieve. The query you execute is literally the same as SELECT true, false FROM winnings. Please share 10 lines of the table and the exact output you want to get back. — Aleksei Matiushkin

Leonardo Dagnino Leonardo Dagnino · Accepted Answer · 2020-06-03T23:54:27

To get the first column from a list of rows, you can use Enum.map/2 to get the first element of each tuple:

Enum.map(rows, &elem(&1, 0))

If you're a newcomer to elixir, the & syntax may be a bit confusing. That code is shorthand for

Enum.map(rows, fn field -> elem(field, 0) end)

You could make that into a function that does that for all your columns like this:

def columnize(rows = [first_row | _]) when is_tuple(first_row) do                 
  for column <- 1..tuple_size(first_row), do: Enum.map(rows, &elem(&1, column - 1))
end

def columnize([]) do
  []
end

hd/1 is a function used to get the first tuple in the list. The = [first_row | _] part guarantees the argument is a list with at least one element; the when is_tuple(first_row) assures at least the first row is a tuple.

If you'd like to know more about the for, it's a comprehension.

Note that this function assumes all rows have the same number of columns and are tuples (which should be true in the results of a query; but may not be in other cases), and will throw an ArgumentError if the first row is wider than any other row. It will also cause errors if other elements of the list are not tuples.

This feels very unidiomatic though. Is this maybe an XY problem?

How to return different rows from the same table on single query as a list

1 Answers