simple join options for a ManyToOne relationship

[Josh Stratton]

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]

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]

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]

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:

 http://elixir.ematia.de/trac/wiki/TutorialDivingIn

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]

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:

 http://elixir.ematia.de/trac/wiki/TutorialDivingIn

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(...)...