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: https://docs.sqlalchemy.org/en/13/ — mad_, Jun 26 '19 at 13:43

score 1 · Answer 1 · answered Jun 26 '19 at 13:47

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")

Ilja Everilä · Accepted Answer · 2019-07-08T10:04:49.363

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)

ipaleka · Answer 3 · 2019-06-26T15:11:16.410

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 '19 at 15:11

answered Jun 26 '19 at 14:21

ipaleka

3,745
2
13
33

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' – Sviatoslav Pikh Jun 26 '19 at 14:44
I edited and fixed that `CrmLead.id == CrmPipelineStatus.id` to `CrmLead.id==CrmTask.lead_id`. – ipaleka Jun 26 '19 at 15:13
`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?. – Sviatoslav Pikh Jun 27 '19 at 07:42
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]))`? – ipaleka Jun 27 '19 at 14:33

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

3 Answers3