0

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?

2
  • If you want to "select only the first transcription", you have to define "first" exactly to begin with. Also, always disclose your version of Postgres, please. May 19, 2023 at 0:18
  • Vercel postgres version 15.
    – Lance
    May 19, 2023 at 1:08

1 Answer 1

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:

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.

2
  • Thank you. By "select only the first transcription matching the request, for each word" I meant since there are multiple transcriptions per word, how do you say only use the first of the matching ones to perform the sorting? By "first", I mean you have to sort the transcriptions within the system for the word (say there are 2 transcriptions in system 1 for each word), then pick the first one. That way you are not using both transcriptions to sort, which seems confusing.
    – Lance
    May 19, 2023 at 1:05
  • @Lance. Sure, but which of several translations is the "first"? Did I guess it in my answer? May 19, 2023 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.