I'm not intending for the contents of the BLOB to be readable to MySQL. It would only be cracked open and read from within Python. Meaning Python only knows what the key actually is. So yeah, I understand the caveats of this approach. I merely want to provide a mechanism to, as a second & separate SELECT query, load the relationed instance and be able to access it from the child python instance.
Hopefully that clears some things up. On Thu, Jun 2, 2011 at 4:10 PM, Michael Bayer <[email protected]> wrote: > Using a BLOB as a key is a really bad idea and wont work on all backends, but > other than the database-level limitations inherent, SQLAlchemy will let you > set up whatever column you'd like to use as the key just fine within a > relationship(). Guessing what the problem might be. Foreign key ? If > you rely upon your table metadata to emit CREATE TABLE statements, you can > forego using ForeignKey with your table metadata and configure the foreign > key data on the relationship itself using > "foreign_keys=[table.c.my_referencing_blob_column], > primary_join=table.c.my_key_blob_column==table.c.my_referencing_blob_column". > > As far as your eager load, both joined eager loading and subquery eager > loading rely on being able to JOIN to the target so if your backend is not > letting the JOIN part happen, I'm not sure exactly what SQL you'd like to > emit. If you'd like the "lazy" loader to just fire off immediately, you > can use the "immediate" style of loader - lazy="immediate" or immediateload() > as an option - but that won't save you on SELECT statements. > > > > > On Jun 2, 2011, at 6:15 PM, Ben Chess wrote: > >> Hi, >> >> I want to establish a relationship with an object whose key is defined >> inside a JSON BLOB column in the child. Naively, I know I can do this >> via a regular python @property that uses object_session() to then do a >> query() using the id from inside the blob. Is there a better way that >> lets sqlalchemy manage the relationship and caching? >> >> Also, I'd like to be able to sometimes eagerly load this >> relationship. Obviously I can't do in with a JOIN of the original >> query, but is there some way to define the relationship, perhaps using >> a column_property, to be able to undefer()? >> >> Thanks, >> Ben Chess >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
