How to use different types of joins for sql-alchemy in python

Question

I`m trying to figure out how to define the join type in sql-alchemy ORM. How to use left join and left outer join? What about inner join?

This is for the query which can select all crm_lead without a related crm_task. I tried exists filter but couldn`t filter existing crm_leads with this clause.

Desired SQL:

select *
from crm_lead l
join crm_task t on l.id = t.lead_id
left outer join crm_pipeline_status cps on l.pipeline_status_id = cps.id
where l.pipeline_status_id not in (142, 143) 
  and (t.id is null or t.is_completed is false);

OR: (if exists clause is better for this case)

select *
from crm_lead l
left outer join crm_pipeline_status cps on l.pipeline_status_id = cps.id
where cps.crm_id not in (142, 143)
  and not exists (select id from crm_task t where l.id = t.lead_id and t.is_completed is false);

My best try was:

session = sessionmaker(bind=engine, autocommit=True)()
with session.begin():
    leads = session.query(CrmLead).outerjoin(CrmTask).outerjoin(CrmPipelineStatus).filter(
        and_(CrmLead.account_id == 2,
        CrmPipelineStatus.crm_id not in (142, 143),
        or_(CrmTask.is_completed is False, CrmTask.id is None))
    )

but it converts into:

SELECT *
FROM crm_lead 
LEFT OUTER JOIN crm_task ON crm_lead.id = crm_task.lead_id 
LEFT OUTER JOIN crm_pipeline_status ON crm_pipeline_status.id = crm_lead.pipeline_status_id 
WHERE false

ALTERNATIVE SOLUTION: My case can be solved with raw SQL as shown here [https://stackoverflow.com/a/22084672/2822537]

Example:

    query_text = '''
    select *
    from crm_lead l
    left outer join crm_pipeline_status cps on l.pipeline_status_id = cps.id
    where cps.crm_id not in (:success_final_status, :failed_final_status)
      and l.account_id = :account_id
      and not exists (select id from crm_task t where l.id = t.lead_id and t.is_completed is false);
    '''
    leads = session.execute(query_text, {
        'account_id': crm_configuration["instance_id"],
        'success_final_status': 142,
        'failed_final_status': 143
    })

I cannot see your sqlalchemy attempt in your post. Please be mindful nobody will spoon feed you with answers unless you try something. To start with: docs.sqlalchemy.org/en/13 — mad_
– mad_, Commented Jun 26, 2019 at 13:43

Ilja Everilä · Accepted Answer · 2019-07-08 10:04:49Z

1

The expression

CrmPipelineStatus.crm_id not in (142, 143)

evaluates to False or True in Python, since __contains__() cannot be used in the same fashion as some other overloads. If it is False, the whole enclosing and_() construct is compiled to simply false. The proper way in this case is to use the method notin_():

CrmPipelineStatus.crm_id.notin_([142, 143])

A somewhat similar problem occurs

or_(CrmTask.is_completed is False, CrmTask.id is None)

as the identity operator is cannot be overloaded, and so you have effectively

or_(False, False)

which again compiles to false. For the first one you should just use the boolean as a boolean (given it is NOT NULL) and the NULL check can be written either using the is_() method or the special case in the operator overloads:

or_(not_(CrmTask.is_completed), CrmTask.id == None)

edited Jul 8, 2019 at 10:04

answered Jun 27, 2019 at 17:03

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom Leboeuf · Accepted Answer · 2019-06-26 13:47:07Z

1

Look maybe this post

q = session.query(Table1.field1, Table1.field2)\
.outerjoin(Table2)\ # use in case you have relationship defined
# .outerjoin(Table2, Table1.id == Table2.table_id)\ # use if you do not have relationship defined
.filter(Table2.tbl2_id == None)

should do it, assuming that field1 and field2 are from Table1, and that you define a relationship:

class Table2(Base):
# ...
table1 = relationship(Table1, backref="table2s")

answered Jun 26, 2019 at 13:47

Tom Leboeuf

93 bronze badges

Comments

ipaleka · Accepted Answer · 2019-06-26 15:11:16Z

0

CamelCase is used for mapped classes of lowercased table names:

from sqlalchemy.sql import exists

Session().query(CrmLead).join(CrmTask).outerjoin(CrmPipelineStatus).filter(CrmLead.pipeline_status_id == CrmPipelineStatus.id).filter(CrmPipelineStatus.crm_id.notin_([142, 143])).filter(~exists().where(and_(CrmTask.is_completed==False, CrmLead.id==CrmTask.lead_id)

edited Jun 26, 2019 at 15:11

answered Jun 26, 2019 at 14:21

ipaleka

3,9772 gold badges16 silver badges39 bronze badges

4 Comments

Sviatoslav Pikh Over a year ago

Session().query(CrmLead).join(CrmTask).outerjoin(CrmPipelineStatus).filter( CrmLead.pipeline_status_id == CrmPipelineStatus.id).filter( CrmPipelineStatus.crm_id.notin_([142, 143])).filter( ~exists().where(and_(CrmTask.is_completed == False, CrmLead.id == CrmPipelineStatus.id))).scalar() creates Invalid Request Select statement 'SELECT * FROM crm_task, crm_lead, crm_pipeline_status WHERE crm_task.is_completed = false AND crm_lead.id = crm_pipeline_status.id'

ipaleka Over a year ago

I edited and fixed that CrmLead.id == CrmPipelineStatus.id to CrmLead.id==CrmTask.lead_id.

Sviatoslav Pikh Over a year ago

sqlalchemy.exc.InvalidRequestError: Select statement 'SELECT *  FROM crm_task, crm_lead  WHERE crm_task.is_completed = false AND crm_lead.id = crm_task.lead_id'

It seems, "exists" statement do not use CrmLead model from the main query and is trying to request it again via strange query select * from CrmTask, CrmLead. How to make "exists" clause use a CrmLead model from the main query?.

ipaleka Over a year ago

What about this:

Session().query(CrmLead).join(CrmTask, CrmTask.lead_id == CrmLead.id, CrmTask.is_completed==False, isouter=True).outerjoin(CrmPipelineStatus).filter(CrmLead.pipeline_status_id == CrmPipelineStatus.id).filter(CrmPipelineStatus.crm_id.notin_([142, 143]))

?

Collectives™ on Stack Overflow

How to use different types of joins for sql-alchemy in python

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related