On Mon, May 14, 2018 at 9:37 AM, <chbrn...@gmail.com> wrote:
> Hi, I'm still relatively a newby to SQLAlchemy and not sure if I'm using the
> correct query to achieve what I'm trying to and hoping someone can point me
> in the correct direction or help me understand what I need to use to run the
> query I'm attempting.
>
> I'm trying to retrieve data from multiple tables with SQLAlchemy using the
> .join() method.
>
> When I run the query I was expecting to get a single object back which had
> all the data from the different tables joined so that I could use
> a.area_name and so on where area_name is on one of the joined tables. Below
> is the query I am running and the table layout, if anyone could offer
> insight into how to achieve the behavior I'm aiming for I would greatly
> appreciate it! I've been able to use the .join() method with this same
> syntax to match results and return them, I figured it would return the extra
> data from the rows as well since it joins the tables (perhaps I'm
> misunderstanding how the method works or how to retrieve the information via
> the query object?).
>
> If it helps with the troubleshooting I'm using MySQL as the database
>
> Thanks for any help!
>
>
> query:
>
> a = User.query.filter(User.user_id==1).join(UserGroup,
> User.usergroup==UserGroup.group_id).join(Areas,
> User.area==Areas.area_id).first()
>
>
> tables:
>
> class User(db.Model):
> user_id = db.Column(db.Integer, primary_key=True)
> name = db.Column(db.String(20), unique=True)
> usergroup = db.Column(db.Integer, db.ForeignKey('user_group.group_id'),
> nullable=False)
> area = db.Column(db.Integer, db.ForeignKey('areas.area_id'),
> nullable=False)
>
>
>
> class UserGroups(db.Model):
> id = db.Column(db.Integer, primary_key=True)
> group_id = db.Column(db.Integer, nullable=False, unique=True)
> group_name = db.Column(db.String(64), nullable=False, unique=True)
>
>
>
> class Areas(db.Model):
> id = db.Column(db.Integer, primary_key=True)
> area_id = db.Column(db.Integer, nullable=False, unique=True)
> area_name = db.Column(db.String(64), nullable=False, unique=True)
>
The normal way to handle this is to create a relationship between your
User and Areas classes:
http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-one
...which might look something like this:
########################################
import sqlalchemy.orm as saorm
class User(db.Model):
user_id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), unique=True)
area_id = db.Column(db.Integer, db.ForeignKey('areas.area_id'),
nullable=False)
area = saorm.relationship("Areas")
class Areas(db.Model):
id = db.Column(db.Integer, primary_key=True)
area_id = db.Column(db.Integer, nullable=False, unique=True)
area_name = db.Column(db.String(64), nullable=False, unique=True)
########################################
Note that I renamed your "area" column to "area_id". This isn't
strictly necessary, but it makes the code a bit clearer. If "user" is
an instance of your User class, "user.area_id" would be the integer
value of the foreign key, and "user.area" would be an instance of the
Areas class. You would access the area_name value as
"user.area.area_name".
To load the areas at the same time as loading the user, you would use
one of the eager loading options:
http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#joined-eager-loading
For example:
import sqlalchemy.orm as saorm
user = User.query.options(saorm.joinedload('area')).get(1)
This would automatically add the join between the user and areas tables.
Hope that helps,
Simon
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
