SQL Быстрый подсчет глубокого количества (отношение один ко многим 2x)

Question

Приветствую сообщество ru.stackoverflow! Имеются 3 таблицы sqlalchemy.

Садоводы (у садовода может быть много деревьев):

class Gardener(Base):
    __tablename__ = 'gardeners'

    gardener_id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(15))
    apple_trees = relationship('AppleTrees', backref='gardener_trees')

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| gardener_id      | int          | Yes  | PRI | NULL    | auto_increment |
| name             | varchar(15)  | Yes  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

+------------------+--------------+
| gardener_id      | name         |
+------------------+--------------+
| 1                | Steve        |
| 2                | Ronald       |
+------------------+--------------+

Деревья принадлежат разным садоводам (на дереве может быть много яблок)

class AppleTrees(Base):
    __tablename__ = 'trees'

    tree_id = Column(Integer, primary_key=True, autoincrement=True)
    gard_id = Column(Integer, ForeignKey('gardeners.gardener_id'))
    apples = relationship('Apples', backref='tree_apples')

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| tree_id          | int          | Yes  | PRI | NULL    | auto_increment |
| gard_id          | int          | Yes  | MUL | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

+------------------+--------------+
| tree_id          | gard_id      |
+------------------+--------------+
| 1                | 1            |
| 2                | 2            |
| 3                | 2            |
+------------------+--------------+

Яблоки принадлежат разным деревьям

class Apples(Base):
    __tablename__ = 'apples'
    apple_id = Column(Integer, primary_key=True, autoincrement=True)
    ap_tree_id = Column(Integer, ForeignKey('trees.tree_id'))

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| apple_id         | int          | Yes  | PRI | NULL    | auto_increment |
| ap_tree_id       | int          | Yes  | MUL | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

+------------------+--------------+
| apple_id         | ap_tree_id   |
+------------------+--------------+
| 1                | 1            |
| 2                | 2            |
| 3                | 3            |
| 4                | 1            |
| 5                | 3            |
+------------------+--------------+
/* Как видим, у Стива всего одно дерево под 1ид и с таким ид есть два яблока номера 1 и 4. У Роджера же два дерева 2ид, 3ид и три яблока номера 2, 3, 5 */

И вот в чем дилемма, как узнать количества яблок конкретного садовода, как говорится, по-умному.

Написал это:

gardener = session.query(Gardener).filter_by(name='Steve').first()
apple_counter = 0
for tree in gardener.apple_trees:
    for apple in tree.apples:
        apple_counter += 1

Но я на 100% уверен, что это очень плохая и медленная методика.

Огромная просьба помочь. Я очень хотел бы увидеть и осознать как данная проблема решается методом работы с объектами sqlalchemy и | или обычным SQL запросом. Заранее прошу прощения за глупость и большое спасибо за любые Ваши решения и подсказки!

Answer 1

Если обычным SQL запросом то,

select g.gardener_id g.name, count(a.apple_id) apples_qty from gardeners g
LEFT JOIN trees t on t.gard_id = g.gardener_id
LEFT JOIN apples a on a.ap_tree_id = t.tree_id
GROUP BY g.gardener_id, g.name

Для SQLAlchemy аналогично может использоваться func.count для подсчета.

БЛОГ НА HUSL

SQL Быстрый подсчет глубокого количества (отношение один ко многим 2x)

Ответы (1 шт):