sqlalchemy equvalent of sql syntax

Question

I have a sql code that will print out all events that user with id=3 did not join particular yet:

SELECT * from Event where id not in (select event_id from Participant where user_id =3);

I want to write it in SQLAlchemy and so far I've got this

Event.query.filter(not_(Participant.user_id==3))

but produced query is not what I initially wrote:

SELECT "Event".id AS "Event_id",
"Event".name AS "Event_name",
"Event".published AS "Event_published",
"Event".published_when AS "Event_published_when",
"Event".published_by AS "Event_published_by"
FROM "Event", "Participant"
WHERE "Participant".user_id != ?

The above is not giving any results. I guess I wrote this SQLAlchemy query incorrectly. What is wrong with it?

zerocool · Accepted Answer · 2019-08-27 10:39:21Z

0

Correct syntax:

Event.query.filter(Event.id.notin_(db.session.query(Participant.event_id).filter(Participant.user_id==session['uid'])))

Subquery had to be limited to event_id only. Thanks to this it will now return correct data from DB.

answered Aug 27, 2019 at 10:39

zerocool

8331 gold badge9 silver badges19 bronze badges

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

Comments

Kostas Charitidis · Accepted Answer · 2019-08-27 10:42:12Z

0

Try something like this:

sub = Participant.query(Participant.event_id).filter(Participant.user_id==3)
res = Event.query.filter(Event.id.notin_(sub))

or maybe this way:

res = Session.query(Event, Participant).filter(Event.id == Participant.user_id).filter(Participant.user_id == 3)

edited Aug 27, 2019 at 10:42

answered Aug 27, 2019 at 9:57

Kostas Charitidis

3,1231 gold badge15 silver badges23 bronze badges

2 Comments

Kostas Charitidis Over a year ago

@SuperShoot yeap! I think you're correct. Thanks for the notice

zerocool Over a year ago

thanks for your input! Unfortunatelly, both solutions were incorrect due to problems with subquery (returned more than 1 record) so I had to modify it a bit. See my answer.

Collectives™ on Stack Overflow

sqlalchemy equvalent of sql syntax

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related