An Inner Join with Sqlalchemy

How to achieve inner join using SQLAlchemy?

For that you first need to have a session to make a Query. Additionally it can be convenient to have a relationship on your MessageModel.

class MessageModel(Base):
    __tablename__ = 'messages'
    player_id = Column(Integer,ForeignKey('chats.id'), nullable=False)
    message = Column(String(2000), nullable=False)
    time = Column(TIMESTAMP, server_default=func.now())
    player = relationship(PlayerModel, backref="messages")

This will create the relationship on both models.

results = (session.query(PlayerModel)
                  .join(PlayerModel.messages)
                  .values(PlayerModel.username,
                          MessageModel.message,
                          MessageModel.time))
# results will be a generator object

# This seems a bit convoluted, but here you go.
resultlist = []
for username, message, time in results:
    resultlist.append({'message': message,
                       'username': username,
                       'time': time})

There may be more elegant ways to come to your data structure but this one should work.

Flask SQLAlchemy syntax for SELECT statement with inner join

• filter_by() used with keyword argument expression.

The keyword expressions are extracted from the primary entity of the query, or the last entity that was the target of a call to Query.join().

so you need write as:

productstore = ProductStore.query.filter_by(productid=3) \
                                 .join(MyStore, ProductStore.storeid==MyStore.storeid) \
                                 .filter_by(userid=2) \
                                 .order_by(ProductStore.price).first()

• filter() used with SQL expression, can take field compare's result into argument.

in SQL expression you can write:

productstore = ProductStore.query.join(MyStore, ProductStore.storeid==MyStore.storeid) \
                           .filter(ProductStore.productid==3, 
                                   MyStore.userid ==2) \
                           .order_by(ProductStore.price).first()

Forcing inner join with many-to-many relationship in sqlalchemy

MySQL is doing an inner join; that is, the query you've shown is one implementation of an inner join.

From the docs:

innerjoin=False – when True, joined eager loads will use an inner join
to join against related tables instead of an outer join. The purpose
of this option is strictly one of performance, as inner joins
generally perform better than outer joins. This flag can be set to
True when the relationship references an object via many-to-one using
local foreign keys that are not nullable, or when the reference is
one-to-one or a collection that is guaranteed to have one or at least
one entry.

The key point is inner join vs. outer join. The syntax is irrelevant.

Note that your query can be converted to a query using the inner join syntactic form which has the exact same meaning:

SELECT permissions.permissionid AS permissions_permissionid,
     permissions.name AS permissions_name
FROM permissions
INNER JOIN users_permissions 
    ON permissions.permissionid = users_permissions.permissionid
WHERE ? = users_permissions.userid
ORDER BY permissions.name

MySQL will usually generate an identical execution plan for the two forms.

Eager Inner Join in SQLAlchemy

I might be slightly off here, but have you tried explicitly passing the join condition?

q = session.query(City.population, State.name).join(State).all()

Also, assuming your objective is the initial query, have you tried a few tweaks in the sqlalchemy syntax to actually get the same statement?

print (q.statement)

Lastly, Query classes have a method enable_eagerloads(). From the docs:

Control whether or not eager joins and subqueries are rendered.

When set to False, the returned Query will not render eager joins regardless of joinedload(), subqueryload() options or mapper-level lazy='joined'/lazy='subquery' configurations.

SQLAlchemy: create sqlalchemy query from sql query with subquery and inner join

Base on your SQL I think you are trying to query the lines which user_id is given and with max date.
So first maybe we can update the SQL a little:

SELECT DISTINCT d.*, t.max_date, t.USER_ID
FROM DATABASE_ONE d
INNER JOIN (
    SELECT USER_ID, MAX(DATE_CREATED) max_date 
    FROM DATABASE_ONE
    WHERE 
        USER_ID IN ('1','2','3')
        AND CATEGORY = 'Users'
        AND IS_DELETED IS NULL
    GROUP  BY USER_ID
) t on t.USER_ID = d.USER_ID AND t.max_date = d.DATE_CREATED

In these case it's easier to read and convert into SQLAlchemy language.

---

Then the problem about your second SQLAlchemy query, is that you are trying to select from a subquery but the select is from origin table. If convert the second SQLAlchemy query into sql query, It's will look like:

SELECT DATABASE_ONE.USER_ID, MAX(DATABASE_ONE.DATE_CREATED) max_date FROM (
    SELECT * FROM DATABASE_ONE
    WHERE USER_ID IN ('1','2','3')
    AND CATEGORY = 'Users'
    AND IS_DELETED IS NULL
)
GROUP  BY DATABASE_ONE.USER_ID

The better way to write the SQLAlchemy query:

    subquery = Session.query(
        DatabaseOne.user_id,
        func.max(DatabaseOne.date_created).label("max_date"),
    )
    .filter(
        DatabaseOne.user_id.in_('1', '2', '3'),
        DatabaseOne.category == "Obligor Rating Scorecards",
        DatabaseOne.is_deleted == None,
    )
    .group_by(DatabaseOne.user_id)
    .subquery()

    Session.query(
        DatabaseOne, subquery.c.user_id, subquery.c.max_date # see the .c method to access column in subquery
    ).select_from(subquery).join(DatabaseOne).filter(DatabaseOne.user_id = subquery.c.user_id,DatabaseOne.date_created = subquery.c.max_date).all()

---

Related Topics