On Thu, Jun 2, 2011 at 5:18 PM, Michael Bayer <[email protected]> wrote:
>
> On Jun 2, 2011, at 8:11 PM, Ben Chess wrote:
>
>> 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.
>
> How do you want to identify what row contains the target key ? Is the
> comparison on the SQL side or the Python side ?
In Python. Below is an example of what I have.
class Account:
data = Column(JSONType)
@property
def salesperson(self):
session = orm.session.object_session(self)
return session.query(Salesperson, id=self.data['salesperson_id']).one()
>>
>> 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.
>>
>
> --
> 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.
