> On Dec 13, 2014, at 3:40 PM, Mainak Sarcar <[email protected]> wrote:
>
> 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.
Well I’d just use a regular relationship and then emit a Query() against it as
needed:
def user_specific_projects(self, user_id):
return
object_session(self).query(Project).with_parent(self).filter_by(user_id=user_id).all()
>
> 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
>
> <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
>>>
>>> <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
>> <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]
> <mailto:[email protected]>.
> To post to this group, send email to [email protected]
> <mailto:[email protected]>.
> Visit this group at http://groups.google.com/group/sqlalchemy
> <http://groups.google.com/group/sqlalchemy>.
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
--
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.
