I have declared models which have many to many relationships with each other. Below is the module code:
import sqlalchemy
from sqlalchemy.ext import declarative
class Base(object):
pass
DeclarativeBase = declarative.declarative_base(cls=Base)
repositories_tags = sqlalchemy.Table(
'repositories_tags',
DeclarativeBase.metadata,
sqlalchemy.Column(
'tag_id',
sqlalchemy.Integer,
sqlalchemy.ForeignKey('tags.id_')
),
sqlalchemy.Column(
'repo_id',
sqlalchemy.Integer,
sqlalchemy.ForeignKey('repositories.id_')
),
)
class Tag(DeclarativeBase):
__tablename__ = 'tags'
id_ = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
name = sqlalchemy.Column(sqlalchemy.String(30), unique=True)
popularity = sqlalchemy.Column(sqlalchemy.Integer, default=lambda: 0)
association = sqlalchemy.orm.relationship(
'Repository',
secondary=repositories_tags,
backref=sqlalchemy.orm.backref('labels', lazy='dynamic')
)
class Repository(DeclarativeBase):
__tablename__ = 'repositories'
id_ = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
name = sqlalchemy.Column(sqlalchemy.String(100), unique=True)
description = sqlalchemy.Column(sqlalchemy.String(500))
downloads = sqlalchemy.Column(sqlalchemy.Integer, default=lambda: 0)
uri = sqlalchemy.Column(sqlalchemy.String(500), unique=True)
Here Tags and Repository models have many to many relationship with each other.
I need to retrieve repositories associated with given tags. One of last failed try is:
tag_ids = session.query(_models.Tag.id_)\
.filter(_models.Tag.name.in_(tag_names))\
.order_by(_models.Tag.popularity.desc())\
.all()
# unable to write the following esp. the filter part
session.query(_models.labels)\
.filter(_models.labels.tag_id.in_(tag_ids))\
.order_by(_models.Repository.downloads.desc())\
.all()
How can I get the repositories with specified tags. Any help would be much appreciated.
Below is the sample data:
Tags table:
+-----+------+------------+
| id_ | name | popularity |
+-----+------+------------+
| 1 | tag1 | 4 |
| 2 | tag2 | 6 |
| 3 | tag3 | 9 |
| 4 | tag4 | 3 |
| 5 | tag5 | 3 |
+-----+------+------------+
Repositories table:
+-----+---------+------------------+-----------+-------------------+
| id_ | name | description | downloads | uri |
+-----+---------+------------------+-----------+-------------------+
| 1 | repo1 | Basic repo. | 342 | alembic-base.v1 |
| 2 | repo2 | Test repo. | 250 | usd_dev.v2 |
| 3 | repo3 | Some other repo. | 410 | usd_dev.anim |
| 4 | repo4 | Fourth repo. | 500 | nodes_editor.v1 |
| 5 | repo5 | Last one. | 467 | usd_standalone.v1 |
+-----+---------+------------------+-----------+-------------------+
Labels table:
+--------+---------+
| tag_id | repo_id |
+--------+---------+
| 3 | 1 |
| 2 | 1 |
| 1 | 2 |
| 2 | 2 |
| 1 | 3 |
| 4 | 4 |
| 2 | 4 |
| 5 | 5 |
| 2 | 5 |
+--------+---------+
Query I am looking for is:
SELECT repositories.*
FROM repositories
JOIN labels
ON repositories.id_ = labels.repo_id
JOIN tags
ON tags.id_ = labels.tag_id
WHERE tags.name in ("tag1", "tag2");
