SQL все строки в дочерних "категориях" и дочерних "категориях": рекурсивный?

Question

У меня возникают проблемы при написании запроса, который решает следующую проблему, которая, по моему мнению, нуждается в некоторой рекурсивности:

У меня есть стол с housesкаждый из которых имеет определенный тип house_type, pe house, bungalow и т. д. Типы house_typ наследуют друг от друга, также объявленные в таблице с именем house_types,

table: houses
id | house_type
1  | house
2  | bungalow
3  | villa
etcetera...

table: house_types
house_type | parent
house      | null
villa      | house
bungalow   | villa
etcetera...

В этой логике бунгало - это тоже вилла, а вилла - тоже дом. Поэтому, когда я хочу получить все виллы, дома 2 и 3 должны появиться, когда я хочу получить все дома, дома 1, 2 и 3 должны появиться, когда я хочу все бунгало, должен появиться только дом 3.

Является ли рекурсивный запрос ответом и как мне это решить. я использую knex/objection.js в node.js приложение.

Answer 1

Начнем с @gordon-linoffs, ответ потрясающий. Я просто здесь, чтобы добавить подробности, как это сделать с помощью knex / objection.js.

Это звучит как довольно неприятный дизайн БД. Я бы денормализовал данные типа, чтобы было проще выполнять запросы без рекурсивных общих табличных выражений (knex не поддерживает их в настоящее время).

В любом случае вот какой-нибудь исполняемый код, как это сделать objection.js модели и денормализация информации о типах на стороне JavaSript для возможности выполнения запросов, которые вы пытаетесь выполнить: https://runkit.com/mikaelle/stackru-43554373

Поскольку stackru любит, чтобы код также содержался в ответе, я скопирую и вставлю его здесь. Пример использует sqlite3 в качестве бэкэнда БД, но тот же код работает и с postgres.

const _ = require('lodash');
require("sqlite3");

const knex = require("knex")({ 
  client: 'sqlite3', 
  connection: ':memory:' 
});

const { Model } = require('objection');

// init schema and test data
await knex.schema.createTable('house_types', table => {
  table.string('house_type');
  table.string('parent').references('house_types.house_type');
});

await knex.schema.createTable('houses', table => {
  table.increments('id');
  table.string('house_type').references('house_types.house_type');
});

await knex('house_types').insert([
  { house_type: 'house', parent: null },
  { house_type: 'villa', parent: 'house' },
  { house_type: 'bungalow', parent: 'villa' }
]);

await knex('houses').insert([
  {id: 1, house_type: 'house' },
  {id: 2, house_type: 'villa' },
  {id: 3, house_type: 'bungalow' }
]);

// show initial data from DB
await knex('houses')
  .join('house_types', 'houses.house_type', 'house_types.house_type');

// create models
class HouseType extends Model {
  static get tableName() { return 'house_types' };

  // http://vincit.github.io/objection.js/#relations
  static get relationMappings() {
    return {
      parent: {
        relation: Model.HasOneRelation,
        modelClass: HouseType,
        join: {
          from: 'house_types.parent',
          to: 'house_types.house_type'
        }
      }
    }
  }
}

class House extends Model {
  static get tableName() { return 'houses' };

  // http://vincit.github.io/objection.js/#relations
  static relationMappings() { 
    return {
      houseType: {
        relation: Model.HasOneRelation,
        modelClass: HouseType,
        join: {
          from: 'houses.house_type',
          to: 'house_types.house_type'
        }
      }
    }
  }
}

// get all houses and all house types with recursive eager loading
// http://vincit.github.io/objection.js/#eager-loading
JSON.stringify(
  await House.query(knex).eager('houseType.parent.^'), null, 2
);

// however code above doesn't really allow you to filter 
// queries nicely and is pretty inefficient so as far as I know recursive
// with query is only way how to do it nicely with pure SQL

// since knex doesn't currently support them we can first denormalize housetype
// hierarchy (and maybe cache this one if data  is not changing much)
const allHouseTypes = await HouseType.query(knex).eager('parent.^');

// initialize house types with empty arrays
const denormalizedTypesByHouseType = _(allHouseTypes)
  .keyBy('house_type')
  .mapValues(() => [])
  .value();

// create denormalized type array for every type 
allHouseTypes.forEach(houseType => {
  // every type should be returned with exact type e.g. bungalow is bungalow
  denormalizedTypesByHouseType[houseType.house_type].push(houseType.house_type);
  let parent = houseType.parent;
  while(parent) {
    // bungalow is also villa so when searched for villa bungalows are returned
    denormalizedTypesByHouseType[parent.house_type].push(houseType.house_type);
    parent = parent.parent;
  }
});

// just to see that denormalization did work as expected
console.log(denormalizedTypesByHouseType);

// all villas
JSON.stringify(
  await House.query(knex).whereIn('house_type', denormalizedTypesByHouseType['villa']),
  null, 2
);

Answer 2

Вот рекурсивный CTE, который получает каждую пару в иерархии:

with recursive house_types as (
      select 'house' as housetype, null as parent union all
      select 'villa', 'house' union all
      select 'bungalow', 'villa'
     ),
     cte(housetype, alternate) as (
       select housetype, housetype as alternate
       from house_types
       union all
       select ht.housetype, cte.alternate
       from cte join
            house_types ht
            on cte.housetype = ht.parent
      )
select *
from cte;

(The house_types CTE просто для настройки данных.)

Затем вы можете присоединить это к другим данным, чтобы получить любой уровень иерархии.