Yes I totally agree with you. If Topic was mapped to a specific user then I
would go that route. In my case it is a shared entity across users, but the
projects are specific to a user. So the query changes based on which user
is logged in.
Querying for a specific set of M projects out of the total N projects
associated to a Topic is very simple with a relationship with explicit
primaryjoin condition or by making the relationship with dynamic and
putting the required filter clause.
My current requirement is a little quirky because I wanted a mapped
property on the Topic entity, whereas I can have the following to get the
results too.
query = select([func.count(...).label('proj_count')]).where(.....user
clause....)
topics = session.query(Topic, query)
topics = topics.all()
But then the result of this will be a KeyedTuple and I have to access the
tuple with its keys. I just cant pass the single Topic entity and have the
count data associated with it. I would have to the handle the KeyedTuple in
every place. I will try out some more options and see what best can be done.
Thanks,
Mainak
On Saturday, December 13, 2014 9:04:56 AM UTC-8, Michael Bayer wrote:
>
>
> On Dec 12, 2014, at 10:58 PM, Mainak Sarcar <[email protected]
> <javascript:>> wrote:
>
> Yes Mike, you are right. Once the object is created and flushed, it has
> the database identity. But the column_property was never accessed. So there
> is no value for the property in the __dict__. Now if I try to access the
> parameter, it fires the query with the unset bindparam and I get the
> exception. Is there a way to set the bindparam value before I access the
> property for the first time on the object?
>
> BTW here is what my model looks like.
>
> Topic
> - id
> - name
> - project_count (column property with bindparam for user_id)
> - projects (relationship)
>
> Project
> - id
> - name
> - topic_id (FK to Topic)
> - user_id
> - status
>
>
> Well what immediately doesn’t make sense is that what does
> Topic.project_count mean if there is no user_id that is local to Topic?
> the Topic has N number of projects, but you only want M, those with some
> arbitrary user_id. So it makes no sense to use column_property for this.
> You need to pass in user_id to a function as an argument. Suppose
> column_property did what you wanted, now Topic.project_count is “10”. But
> now user_id, somewhere on the outside, changes to something else. Now
> Topic.project_count is incorrect. It doesn’t make sense for this to be a
> “mapped” value, because Topic has no fixed user_id.
>
> OTOH if user_id were part of Topic then you’d just use a correlated
> subquery as you see in
> http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#using-column-property
> .
>
> i think basically it doesn’t make any sense to use a dynamically bound
> parameter with column_property().
>
>
> I have a REST endpoint for Topic. When I create a new Topic instance, I
> emit the json version of the object. That is where the column_property gets
> accessed for the first time. Now in reality, there wont be any projects
> when the Topic is created, so firing a query is not really required.
>
> I did give "hybrid property" a try initially using a
> func.count(Topic.projects). But that did not fire a count query, instead
> fired the complete query for fetching projects. I even tried to make
> Topic.projects a "dynamic" load, so that I can pass the extra where clause
> for user_id and status etc. Then I gave column_property a try based on the
> example.
>
> Regards
> Mainak
>
>
> On Fri, Dec 12, 2014 at 9:44 AM, Michael Bayer <[email protected]
> <javascript:>> wrote:
>>
>>
>> On Dec 12, 2014, at 7:11 AM, Mainak Sarcar <[email protected]
>> <javascript:>> wrote:
>>
>> Hi,
>>
>> I have a column_property defined on a model with a select expression as
>> explained in the examples in
>> http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#using-column-property.
>>
>> I had to add an extra condition to the where clause with a bindparam. I
>> have also defined the column_property with the "deferred=True", so that the
>> query is not fired in the first place.
>>
>> I am able to perform queries by using the "undefer" option in the query
>> instance and also passing the param to the query. But when I create an
>> instance of the model and flush and then try to access the column_property,
>> the query tries to get fired and throws exception complaining about the
>> bindparam value not being passed.
>>
>>
>> Is there a way I can explicitly prevent the column_property query being
>> fired for an object that is newly created.
>>
>>
>> I’m puzzled here, the object is flushed, meaning it now has a database
>> identity. Then, I’m assuming either the column_property has never been
>> accessed before, so there is no value in __dict__, or you did a commit(),
>> which expired everything, so there’s nothing in __dict__. Then you access
>> it, which implies you’d like it to return a value of some kind. However,
>> the property has an unset bound parameter. What would the correct value
>> be? If there is no correct value, then what are you expecting to happen
>> when you access this property?
>>
>> It sounds like what you really want here is a hybrid:
>> http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/hybrid.html
>>
>>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
