Как правильно установить отношение one-to-many relationship и обеспечить эффективную функциональность операции orderBy?

Question

[gabrielac9]

столкнулась с трудностями при работе с отношениями в PostgreSQL.

Мой стек: Nestjs, typeorm, postgres.

У меня есть две сущности: 'category' и 'categoryTranslation'. Очевидно, что 'categoryTranslation' служит для хранения переводов категорий и является важной частью многоязычной функциональности. Эти сущности разработаны с учетом простоты.

@Entity({ name: 'category' })
export class CategoryEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column('varchar')
  name: string;

  @Column('varchar')
  description: string;

  @OneToMany(
    _type => CategoryTranslationEntity,
    categoryTranslation => categoryTranslation.category,
  )
  categoryTranslation: CategoryTranslationEntity[];
}

@Entity({ name: 'category_translation' })
export class CategoryTranslationEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column('varchar')
  name: string;

  @Column('varchar')
  description: string;

  @Column('integer', { name: 'language_id' })
  languageId: number;

  @Column('integer', { name: 'category_id' })
  categoryId: number;

  @ManyToOne(_type => CategoryEntity, category => category.id, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'category_id' })
  category: CategoryEntity;
}

Как видно из структуры, сущность "categoryTranslation" отражает поля сущности "category", подлежащие переводу. Более того, она включает в себя дополнительные атрибуты languageId и categoryId, где categoryId служит в качестве ссылки.

Моим намерением было инициировать запрос с фронтенда для получения списка категорий. Я отправляю параметр languageId в заголовке, после чего выполняю операцию innerJoin и select с условием (которое, я признаю, может быть не совсем точным). После этого я получаю данные в следующем формате:

CategoryEntity {
        id: 65,
        name: 'Pumpkin pie',
        description: 'Description for Pumpkin pie',
        categoryTranslation: [ [CategoryTranslationEntity] ]
      },
      CategoryEntity {
        id: 72,
        name: 'Pickles',
        description: 'Description for Pickles',
        categoryTranslation: [ [CategoryTranslationEntity] ]
      }

Перед отправкой данных обратно на фронтенд, я использую функцию map, которая заменяет атрибуты name и description сущности 'categoryEntity' на соответствующие атрибуты объекта 'categoryTranslationEntity'. В случаях, когда перевод отсутствует в 'categoryTranslation' или остается пустым, я сохраняю английский перевод в качестве запасного варианта.

Текущая проблема, с которой я сталкиваюсь, связана с трудностями в работе с функциональностями orderBy и textFilter. С моего понимания, использование операции innerJoin и выборки с третьим аргументом в качестве условия может привести к неожиданному поведению, что, как я понимаю, и происходит в моей ситуации. Исходя из этого контекста и понимания общей ситуации и кодовой базы, вы могли бы предоставить рекомендации по более эффективным стратегиям для управления моими сущностями перевода?

Comments

[fenrir]

после чего выполняю операцию innerJoin и select с условием

может привести к неожиданному поведению, что, как я понимаю, и происходит в моей ситуации

Хрустальный шар сегодня не работает. Что вы считаете неожиданным поведением решительно не ясно, как и зачем вообще orderBy.
Приложите запрос, что получаете и что хотите получить.

[Akina]

В случаях, когда перевод отсутствует в 'categoryTranslation' или остается пустым, я сохраняю английский перевод в качестве запасного варианта.

В MySQL для этих целей существует функция COALESCE().

после чего выполняю операцию innerJoin

Для одной записи??? глупо. Используйте коррелированный подзапрос в списке вывода.

использование операции innerJoin и выборки с третьим аргументом в качестве условия может привести к неожиданному поведению

O_O
О чём вообще эта фраза?

[gabrielac9]

Akina, Мой вопрос скорее состоял в том, как мне лучше сделать и я приложила метод который я использую))
Спасибо за наводку по поводу COALESCE) буду искать больше информации по этому))

[gabrielac9]

Akina, подскажите пожалуйста каким обазом получая такой формат данных из базы данных, мы можем методом  COALESCE поменять значения?

{
  "id": 2,
  "name": "Body Comfort",
  "parentId": null,
  "active": true,
  "order": 2,
  "categoryTranslation": [
    {
      "id": 133,
      "name": "Reconfortant",
      "languageId": 5,
      "categoryId": 2,
      "createdAt": "2023-03-17T13:12:21.681Z",
      "updatedAt": "2023-03-17T13:12:21.681Z",
      "createdById": 2
    }
  ]
}

Query:

return await this.categoryRepository
      .createQueryBuilder('category')
      .leftJoinAndSelect(
        'category.categoryTranslation',
        'categoryTranslation',
        'categoryTranslation.languageId = :languageId',
        { languageId },
      )
      .getMany();

[Akina]

На уровне SQL запрос должен выглядеть так (схематично)

SELECT ..., COALESCE(national.text, english.text) AS text, ...
FROM category
JOIN category_translation AS english ON ... AND english.language = 'en'
LEFT JOIN category_translation AS national ON ... AND national.language = :needed_language
WHERE ...

А уж в этот ваш язык переводите самостоятельно. Скорее всего потребуется RAW-выражение.

Предполагается, что англоязычный вариант присутствует для абсолютно любого термина.