sqlalchemy join returns results from first table only-CodePudding

so this is my issue, I have the following tables:

class ClientCampaings(Base):

__tablename__ = 'client_campaign'

campaign_id = Column(INTEGER, primary_key=True)
client_id = Column(VARCHAR(50))
campaign_name = Column(VARCHAR(45))
campaign_status = Column(VARCHAR(45))
campaign_type = Column(VARCHAR(45))
registration_date = Column(DATE)


class ClientKpi(Base):

__tablename__ = 'client_kpi'

kpi_id = Column(INTEGER, primary_key=True)
kpi_name = Column(VARCHAR(45))
cost_conv = Column(FLOAT)
quality_score = Column(FLOAT)



class KpiAssigment(Base):

__tablename__ = 'kpi_assigment'

assigment_id = Column(INTEGER, primary_key=True)
kpi_id = Column(INTEGER, ForeignKey("client_kpi.kpi_id"))
campaign_id = Column(INTEGER, ForeignKey("client_campaign.campaign_id"))
assigned_by = Column(VARCHAR(45))
timestamp = Column(TIMESTAMP)

#Basic One To Many relation
client_campaign = relationship("ClientCampaings")
client_kpi = relationship("ClientKpi")

Them I do the following query:

from database.session import MySqlConnection
from database.models import KpiAssigment,ClientKpi

db = MySqlConnection(database='db_goes_here').db_session()

kpi=db.query(KpiAssigment)\
      .join(ClientKpi)\
      .filter(KpiAssigment.kpi_id==ClientKpi.kpi_id).all()

Which I thought was going to be something like this:

SELECT kpi_assigment.*,
       client_kpi.*
FROM kpi_assigment
INNER JOIN client_kpi
ON kpi_assigment.kpi_id=client_kpi.kpi_id

However, when I run the SqlAlchemy query I getting back only the results from the first table:

 {'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x7fb3d0ace910>, 'kpi_id': 1, 'assigned_by': '[email protected]', 'assigment_id': 2, 'campaign_id': XXXXXXXXX, 'timestamp': datetime.datetime(2022, 12, 7, 17, 5, 8)}

I was looking to get an INNER JOIN and have also the data from ClientKpi table.

I read these related issues but still not finding why this isn't working

SqlAlchemy Outer Join Only Returns One Table

How to join data from two tables in SQLAlchemy?

and I did follow their documentation

https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.join

Any thoughts?

Thanks

CodePudding user response：

The query

kpi=db.query(KpiAssigment)\
      .join(ClientKpi)\
      .filter(KpiAssigment.kpi_id==ClientKpi.kpi_id).all()

will only select the KpiAssignemnt table; to select the corresponding ClientKpi(s) include the ClientKpi model in the query:

kpi=db.query(KpiAssigment, ClientKpi).join(ClientKpi)

The .filter is redundant as SQLAlchemy will use the declared foreign keys to create the JOIN.

If you want to loop over the child objects for each parent you can use the relationship without an explicit join:

for client_kpi in some_kpi_assignment.client_kpi:
    # do something

CodePudding user response：

Still trying to find out if there is a better response for this, in the meantime, I did the following and got what I was looking for:

 query = db.query(KpiAssigment.campaign_id, 
                  ClientKpi.cost_conv,
                  ClientKpi.quality_score)\
                  .join(ClientKpi)\
                  .filter(KpiAssigment.kpi_id==ClientKpi.kpi_id)\
                  .all()

Which returns the resulta has a list of tuples:

print(query)

>>>[(1, 6.0, 2), (2, 6.0, 2)]