SQLAlchemy. Как выбрать из дерева только те элементы, которые имеют связи в другой таблице?

Question

[FFSinit]

Имеются две таблицы, в одной дерево категорий(таблица1) и подкатегорий, во второй полезные данные(таблица2)
Структура таблиц примерно такая:
таблица1

`idn` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,       # название
  `parent` int(11) DEFAULT NULL,           # id родителя

таблица2

`idn` int(11) NOT NULL AUTO_INCREMENT, 
  `idcat` int(11) DEFAULT NULL,                   # id категории

Таблицы эти нужно использовать как есть, изменять нельзя.

Мне нужно выбрать из таблицы1 все подкатегории определенной категории (сейчас там два уровня вложенности, врядли будет больше) и связанные с ними записи из таблицы2. В некоторых категориях связей с таблицей2 нет.
Решение в лоб, в цикле всё это перебрать, будет очень много запросов к базе, не хотелось бы так делать.
Хотелось бы выбрать всё это за пару запросов максимум, и получить на выходе древовидную структуру.
Как это сделать в SQLAlchemy? Я раньше немного имел дело с Django ORM, в SQLAlchemy не разбираюсь вообще.

Answer 1

[sim3x]

Дерево получить низзя, список - можно.
Для джанги это был бы запрос с select_related()

Мне нужно выбрать из таблицы1 все подкатегории определенной категории (сейчас там два уровня вложенности, врядли будет больше) и связанные с ними записи из таблицы2. В некоторых категориях связей с таблицей2 нет.

тут не понятно - категории у которых нет товаров, тоже выводить или нет?

Comments

[FFSinit]

нет, они не нужны.

[sim3x]

@FFSinit
я бы попробовал начать с вот этого
docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#q...

session.query(Category).join(Product).\
filter(Category.parent==666).\
filter(Product.idcat==Category.idn).\
all()

[FFSinit]

Не совсем то что хотелось бы, но тоже пойдет. Спасибо.

Answer 2

[FFSinit]

В общем, нашел более лучшее решение.
Позволяет получить всё дерево начиная с определенного родительского элемента и всё связанные элементы из другой таблицы, как мне и нужно было. Всего за один запрос.
Модели

class Category(Base):
    __tablename__ = 'category'
    idn = Column(INTEGER(11), primary_key=True)
    name = Column(VARCHAR(255))
    cparent = Column(INTEGER(11), ForeignKey('category.idn'))

    children = relationship('Category',

                            # cascade deletions
                            cascade="all",

                            # many to one + adjacency list - remote_side
                            # is required to reference the 'remote'
                            # column in the join condition.
                            backref=backref("parent", remote_side='category.idn'),

                            # children will be represented as a dictionary
                            # on the "name" attribute.
                            collection_class=attribute_mapped_collection('name'),
                            lazy="joined",
                            join_depth=3,
                            )
    product = relationship('Product',
                               collection_class=list,
                               backref=backref("category", remote_side='category.idn'),
                               lazy="joined",
                               join_depth=3, # так как продукты у меня связаны только с третьим уровнем.
                               )

    def __init__(self, name, parent=None):
        self.name = name
        self.parent = parent



class Product(Base):
    __tablename__ = 'product'
    idn = Column(INTEGER(11), primary_key=True)
    idcat = Column(INTEGER(11), ForeignKey('category.idn'))
    cat = relationship("Category")
    data = Column(VARCHAR(255))

Чтобы за раз достать всё это, нужно всего лишь выполнить

root_node = session.query( Category).filter( Category.name == root_node_name).first()

Comments

[sim3x]

было бы интересно посмотреть на запрос к бд - код страшновато выглядит

[ring0za]

А он там очень страшный будет вместе с lazy = "joined", JOIN'ы по сути своей медленные, их лучше постараться не использовать.

[ring0za]

Достаточно в конфиге указать SQLACHEMY_ECHO = True