[sqlalchemy] Creating a feed related to different object type

[Brice Leroy]

Hello everybody,

It's about brainstorming on an elegant solution. I previously posted this question on the Flask mailing list, and I got advised to post it on SQLAlchemy list... which make more sense I admit. So here is my issue:

I have N different classes:

class Comment(Models):

  author_id = Integer

  comment = String

  creation_date = Date

class Picture(Models):

  author_id = Integer

  image = File

  creation_date = Date

...

now let say, I have a "follow" feature, allowing a user X to get updates when Y (the user followed by X) does something (creepy...).

So far I came up with something like that:

class FeedItem(Model)

  table = String

  key = Integer

  creation_date = Date

  def url(self):

    #get object by querying self.table with self.key

    object = self.get_an_object(table=self.table, key=self.key)

    return object.view_url

and then add this property to Comment and Picture classes:

  @property

  def view_url(self):

    return url_for('view_function_name', self.id)

- What would be your way of dealing with this kind of "open/generic" relationship items?

- How would you manage automatic deletion of a FeedItem when the object it points to get destroyed? (I'm thinking attaching function on delete event to classes)

- Would you create as many FeedItem per follower, or use a Table to link them to followers, therefore deleting a FeedItem would automatically delete the relation record from the Table.?

Thank you,

--

Brice

[Robert Forkel]

hi,
i used to do something like this, i.e. adding information about urls,
views, etc. to sqlalchemy models, but found this to be inflexibel. Now
I keep URL-related information in the web app's routing component, and
to solve problems like the one you pose, I use zca adapters [1] (which
is easier when using pyramid, because you already have a component
registry available). So with this technology you'd register the same
FeedItem class as adapter for the various sqlalchemy models, which
means that at adaption time, the object to adapt will be passed to
you.
regards
robert

[1] http://www.muthukadan.net/docs/zca.html

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.

[Brice Leroy]

Thank you for the advice Robert. As I'm using flask I'm not sure how adaptable the ZCA would be. I'll try to understand the logic behind it and see if I can replicate it within my models.

--
--

Brice

[Brice Leroy]

So here is my solution, 

https://gist.github.com/4164619

Hope it helps others to get an insight on generic relation in SQLAlchemy.

--

Brice

[Eric Ongerth]

Hi Brice,

Yours is another good case of the 'Generic Associations' or 'Polymorphic Association' pattern which comes up quite often.  Here's a link to some docs that will get you going on a good solution that keeps the database normalized.

http://docs.sqlalchemy.org/en/rel_0_8/orm/examples.html#generic-associations

If that doesn't get you off and running, especially the example in discriminator_on_association.py, continue this thread or pop into the IRC channel.

- ejo

[Brice Leroy]

Hi Eric,

Thank you, I actually used this documentation (even though they are only many-to-many) and some other blog post to come up with this solution. Using 0.7 I don't have event on Mixin so I had to find an alternative to event attachment. BTW I'm pretty surprised by the speed of the event solution provided by SQLAlchemy, as I always heard that event listener on DB object were evil, but it turned out to almost add no overhead (test operated 2k object creation).

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/fIkNEtwTaH8J.

To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

--

Brice

[Eric Ongerth]

Hi Brice,

I think something strange happened in the discussion group web page because it did not show me the existence of the message in which you linked to your solution.  If I had seen that I would have known that you already found the way.

Cheers,
Eric