How do I join three tables with SQLalchemy and keeping all of the columns in one of the tables?
- by jimka
So, I have three tables:
The class defenitions:
engine = create_engine('sqlite://test.db', echo=False)
SQLSession = sessionmaker(bind=engine)
Base = declarative_base()
class Channel(Base):
__tablename__ = 'channel'
id = Column(Integer, primary_key = True)
title = Column(String)
description = Column(String)
link = Column(String)
pubDate = Column(DateTime)
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key = True)
username = Column(String)
password = Column(String)
sessionId = Column(String)
class Subscription(Base):
__tablename__ = 'subscription'
userId = Column(Integer, ForeignKey('user.id'), primary_key=True)
channelId = Column(Integer, ForeignKey('channel.id'), primary_key=True)
And the SQL commands that are executed to create them:
CREATE TABLE subscription (
"userId" INTEGER NOT NULL,
"channelId" INTEGER NOT NULL,
PRIMARY KEY ("userId", "channelId"),
FOREIGN KEY("userId") REFERENCES user (id),
FOREIGN KEY("channelId") REFERENCES channel (id)
);
CREATE TABLE user (
id INTEGER NOT NULL,
username VARCHAR,
password VARCHAR,
"sessionId" VARCHAR,
PRIMARY KEY (id)
);
CREATE TABLE channel (
id INTEGER NOT NULL,
title VARCHAR,
description VARCHAR,
link VARCHAR,
"pubDate" TIMESTAMP,
PRIMARY KEY (id)
);
NOTE: I know user.username should be unique, need to fix that, and I'm not sure why SQLalchemy creates some row names with the double-quotes.
And I'm trying to come up with a way to retrieve all of the channels, as well as an indication on what channels one particular user (identified by user.sessionId together with user.id) has a subscription on.
For example, say we have four channels: channel1, channel2, channel3, channel4; a user: user1; who has a subscription on channel1 and channel4. The query for user1 would return something like:
channel.id | channel.title | subscribed
---------------------------------------
1 channel1 True
2 channel2 False
3 channel3 False
4 channel4 True
This is a best-case result, but since I have absolutely no clue as how to accomplish the subscribed column, I've been instead trying to get the particular users id in the rows where the user has a subscription and where a subscription is missing, just leave it blank.
The database engine that I'm using together with SQLalchemy atm. is sqlite3
I've been scratching my head over this for two days now, I've no problem joining together all three by way of the subscription table but then all of the channels where the user does not have a subscription gets omitted.
I hope I've managed to describe my problem sufficiently, thanks in advance.
