How to sort by joined table property in PostgreSQL?

Question

I am new to SQL, and am using an ORM for the most part (well, a SQL builder, not raw SQL). I have a simple dictionary app in Vercel Postgres version 15 with this sort of schema:

CREATE TABLE words (
  id serial PRIMARY KEY,
  transcription_count INTEGER NOT NULL,
  pronunciation_count INTEGER NOT NULL,
  verified BOOLEAN NOT NULL
);

CREATE TABLE transcriptions (
  id serial PRIMARY KEY,
  word_id INTEGER REFERENCES words,
  text TEXT NOT NULL,
  system INTEGER NOT NULL,
  length INTEGER NOT NULL
);

CREATE TABLE pronunciations (
  id serial PRIMARY KEY,
  word_id INTEGER REFERENCES words,
  text TEXT NOT NULL,
  syllable_count INTEGER NOT NULL
);

I want to find all words where the transcription text length is >= 5, for example, and sort on the text, but return the words. Each transcription belongs to a system, and there can be 1 or more transcriptions per system (such as, there are 2 spellings for the word "color/colour" given the "latin" system, as a rough example). Additionally, there can be multiple systems per word (Chinese you have pinyin and Chinese characters, as 2 possible systems, and in the Chinese system you have traditional and simplified as 2 transcriptions within the system, for example).

Here is what I'm thinking.

SELECT id from words
INNER JOIN transcriptions t ON t.word_id = words.id
WHERE t.length >= 5
AND t.system = 1
ORDER BY t.text ASC

Is that the correct type of query? Will that return duplicate words, because of the fact that I'm sorting by the joined table, and there is a 1<>many relationship? I'm unclear how that would work / what kinds of results that would return. Also, as a second query, how could I additionally specify "select only the first transcription matching the request, for each word", so it only sorts by 1 transcription per word?

Answer 1

The query basically works, but id was ambiguous, and the sort order, too:

SELECT w.id  -- table-qualify!
FROM   words w
JOIN   transcriptions t ON t.word_id = w.id
WHERE  t.length >= 5
AND    t.system = 1
ORDER  BY t.text, w.id, t.id;  -- add tiebreaker(s) to make unambiguous

"select only the first transcription matching the request, for each word"

Going out on a limb, I'll interpret this as:

"Take the shortest qualifying transcription per word (alphabetically first on ties), and list words sorted by the picked translation."

If all you want to return is word.id, we don't need to involve the table word at all:

SELECT word_id
FROM  (
   SELECT DISTINCT ON (word_id)
          word_id, text
   FROM   transcriptions t
   WHERE  length >= 5
   AND    system = 1
   ORDER  BY word_id, length, text  -- pick the shortest, then alphabetically first
   ) t
ORDER  BY text, word_id;  -- add tiebreaker to make unambiguous

Else:

SELECT w.*
FROM  (
   SELECT DISTINCT ON (word_id)
          word_id AS id, text
   FROM   transcriptions t
   WHERE  length >= 5
   AND    system = 1
   ORDER  BY word_id, length, text  -- pick the shortest, then alphabetically first
   ) t
JOIN   words w USING (id)
ORDER  BY t.text, w.id;  -- add tiebreaker to make unambiguous

DISTINCT ON is typically efficient for few translations per word. For many translations, other techniques may be better. See:

• Select first row in each GROUP BY group?

You'll also benefit from one or more indexes on length and system (and word_id and text), but which index(es) exactly, that depends on undisclosed details.