simple join options for a ManyToOne relationship

19 views
Skip to first unread message

Josh Stratton

unread,
Jan 18, 2014, 1:49:41 AM1/18/14
to sqle...@googlegroups.com
I'm trying to create a simple join and after reading the docs, can't understand how it's actually performed.  I have a set of users and a set of images.  I also have a set of UserImages, where multiple users can point to the same image.  I've been able to do simple queries on the individual tables, but I'm at a loss on how to get all the images for a given user.  

class User(Entity):
    email = Field(UnicodeText)

class Image(Entity):
    url = Field(UnicodeText, unique=True)

class UserImage(Entity):
    user = ManyToOne('User')
    thumbnail = ManyToOne('Image')

I know I can do something like the following to get all the UserImages...

UserImage.query.filter_by(user=user)

...but I'd like to join that with the actual list images that are linked.  I'm just using sqlite right now for testing if that makes a difference.  

Reading the relationship docs, I don't understand this "joined table" like it's supposed to actually exist statically.  Can someone give me the syntax to do something like this?  Would I have to end up with all the Images or would I have a join of the UserImages and Images?  While the images are shared, I figured there might be data on the userimage later like "link" date so I'd end up with a list of images a user is linked to, but also a column of the creation date.  Thanks, any help or even direction would be appreciated.  

yoav glazner

unread,
Jan 18, 2014, 12:59:42 PM1/18/14
to sqle...@googlegroups.com

Delete the 3rd class and use ManyToMany it will create the 3rd table automagically

--
You received this message because you are subscribed to the Google Groups "SQLElixir" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlelixir+...@googlegroups.com.
To post to this group, send email to sqle...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlelixir.
For more options, visit https://groups.google.com/groups/opt_out.

Josh Stratton

unread,
Jan 18, 2014, 8:14:34 PM1/18/14
to sqle...@googlegroups.com
So if I create a ManyToMany relationship, I see it creates the intermediate table, but now I don't know how to query, join, or even create now.  

class User(Entity):
    email = Field(UnicodeText)
    image = ManyToMany('Image')

class Image(Entity):
    url = Field(UnicodeText, unique=True)

So before I just created a relationship entity for reference from the user to the the image.  What's the syntax to do that now?  Am I just using normal sqlalchemy then on the intermediate table for inserts and queries?  If not, how do I use the user entity to add a link to a given image?  And how do I query the created links for a given user?  Just use the user id?  

yoav glazner

unread,
Jan 19, 2014, 3:58:09 AM1/19/14
to sqle...@googlegroups.com, stratto...@gmail.com
On Sun, Jan 19, 2014 at 3:14 AM, Josh Stratton <stratto...@gmail.com> wrote:
So if I create a ManyToMany relationship, I see it creates the intermediate table, but now I don't know how to query, join, or even create now.  

class User(Entity):
    email = Field(UnicodeText)
    image = ManyToMany('Image')

class Image(Entity):
    url = Field(UnicodeText, unique=True)

So before I just created a relationship entity for reference from the user to the the image.  What's the syntax to do that now?  Am I just using normal sqlalchemy then on the intermediate table for inserts and queries?  If not, how do I use the user entity to add a link to a given image?  And how do I query the created links for a given user?  Just use the user id?  
Please see the tutorial:
Section 6: ManyToMany...

There you will see the example how to use it. the join is implicit, you use the properys as lists.
Is this helpful?

yoav glazner

unread,
Jan 19, 2014, 4:13:05 AM1/19/14
to sqle...@googlegroups.com, stratto...@gmail.com
On Sun, Jan 19, 2014 at 10:58 AM, yoav glazner <yoavg...@gmail.com> wrote:



On Sun, Jan 19, 2014 at 3:14 AM, Josh Stratton <stratto...@gmail.com> wrote:
So if I create a ManyToMany relationship, I see it creates the intermediate table, but now I don't know how to query, join, or even create now.  

class User(Entity):
    email = Field(UnicodeText)
    image = ManyToMany('Image')

class Image(Entity):
    url = Field(UnicodeText, unique=True)

So before I just created a relationship entity for reference from the user to the the image.  What's the syntax to do that now?  Am I just using normal sqlalchemy then on the intermediate table for inserts and queries?  If not, how do I use the user entity to add a link to a given image?  And how do I query the created links for a given user?  Just use the user id?  
Please see the tutorial:
Section 6: ManyToMany...

There you will see the example how to use it. the join is implicit, you use the properys as lists. 
Is this helpful?
Oh I see now what you are after...

I don't know if you can do it with the manyToMany attribute.
You can try with your first apporch:
User.query.join(UserImages).join(Images).filter_by(...)...
Reply all
Reply to author
Forward
0 new messages