How to self join table and select columns in SQLAlchemy python?

Question

I want to implement self join in SQLAlchemy python.

I have a table which has event_id, event_name and event_parent_id. I want to select event id, event name and parent event name from DB for some given event ids. The following code is giving no result. One more doubt how will I differentiate between event name and parent name in the query result.

@app.route("/api/bdi_data_events", methods=["GET"])
def get_bdi_data_events():
  if request.method == "GET":
    try:
        events = request.args.get('events')
        alias1 = BdiDataLifeCycle
        alias2 = BdiDataLifeCycle
        resp = db.session.query(alias1, alias2)\
                .join(alias1.event_parent_id, alias2.event_id)\
                .filter(alias1.event_id.in_(events.split(',')))\
                .values(alias1.event_id, alias1.event_name, alias2.event_name,\
                alias1.event_sequence,\
                alias1.event_reg_dt, alias1.deleted)
        d = []
        print resp
        for i in resp:
            j = {"event_id": i.event_id,"event_name":i.event_name,"event_desc":i.event_desc,"event_name":i.event_name,\
                "event_reg_dt": i.event_reg_dt,"deleted": i.deleted\
                }
            d.append(j)
        return jsonify({"status":"success","response":d})
    except Exception, e:
        print "exception occured-->"
        print e
        return raiseError(BAD_REQUEST, {"message":str(e)})
  else:
    return jsonify({"status":"success"})

Equivalent MySQL query gives proper result.

SELECT a.event_id, a.event_name, b.event_name as parent FROM bdi_data_life_cycle a 
left join  bdi_data_life_cycle b on 
a.event_parent_id = b.event_id
where a.event_id in (1,2,3,73,83,95,96,97,98)


event_id, event_name, parent
'1', 'data_event', NULL
'2', 'test_event4455', 'data_event'
'3', 'test_event5', 'data_event'
'73', 'test_event10', 'data_event'
'83', 'test_event7', 'data_event'
'95', 'test_event101', 'data_event'
'96', 'test_event100', 'data_event'
'97', 'test_event120', 'data_event'
'98', 'test10', 'data_event'

In this case, event id 1 is the parent of all remaining events.

Its not giving proper results. It should give 9 rows. its giving 8 rows. not the '1', 'data_event', NULL — Vivek Nuna
– Vivek Nuna, Commented Jul 7, 2017 at 10:55

Vivek Nuna · Accepted Answer · 2017-07-10 06:49:17Z

I have solved this issue, will provide the solution. I was not using the proper alias. I was using the wrong join. We should use label for column alias.

@app.route("/api/bdi_data_events", methods=["GET"])
def get_bdi_data_events():
  if request.method == "GET":
    try:
        events = request.args.get('events')
        alias1 = aliased(BdiDataLifeCycle)
        alias2 = aliased(BdiDataLifeCycle)
        resp = db.session.query(alias1, alias2)\
                .outerjoin(alias2, alias1.event_parent_id==alias2.event_id)\
                .filter(alias1.event_id.in_(events.split(',')))\
                .filter(alias2.event_parent_id == None)\
                .values(alias1.event_id, alias1.event_name, alias1.event_desc,\
                alias1.event_sequence,\
                alias1.event_reg_dt, alias1.deleted, alias2.event_name.label("event_parent_name"))
        d = []
        print resp
        for i in resp:
            j = {"event_id": i.event_id,"event_name":i.event_name,"event_desc":i.event_desc,"event_sequence":i.event_sequence,\
                "event_reg_dt": i.event_reg_dt,"deleted": i.event_parent_name,"event_parent_name": i.event_parent_name\
                }
            d.append(j)
        return jsonify({"status":"success","response":d})
    except Exception, e:
        print "exception occured-->"
        print e
        return raiseError(BAD_REQUEST, {"message":str(e)})
  else:
    return jsonify({"status":"success"})

Collectives™ on Stack Overflow

How to self join table and select columns in SQLAlchemy python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related