Getting tasks assigned to user in new python_api

[mattmoyes]

Hi,

I can't figure out the new way of getting users assigned to certain tasks. I used to just use:

for task in user.getTasks():

but I cant work out the equivalent query. Is there a database map or something somewhere?

 

Many Thanks

[eight]

I second this. I tried 

'Task where assignments any (resource.username=name)'

 but I got the error: 

 

ftrack_api.exception.ServerError: Server reported error: KeyError(u'username')

 

[eight]

OK. I've got the answer. This is how I did it: 

First I query the user, then I use their ID to get all of their tasks.

user = session.query('User where username is {}'.format(name)).one()
tasks = session.query('Task where assignments any (resource_id={})'.format(user['id']))

[Mattias Seebergs]

Hey,

 

You can use this query to get tasks assigned to a user:

user = session.query('select assignments.context.name from User where username is <username>').one()for assignment in user['assignments']:    print assignment['context']

Let me know if you have any questions.

 

Mattias

[deex]

Hello,

 

One question :

 

session = ftrack_api.Session()user = session.query('select assignments.context.name, assignments.context.project.name from User where username is damien').one()for assignment in user['assignments']:    print assignment['context']['name'], assignment['context']['project']['name']

Why i have this error :

 

 

File "C:\Python27\lib\site-packages\ftrack_api\query.py", line 56, in one    results = self.all()  File "C:\Python27\lib\site-packages\ftrack_api\query.py", line 39, in all    return list(self)  File "C:\Python27\lib\site-packages\ftrack_api\query.py", line 29, in __len__    self._fetch_results()  File "C:\Python27\lib\site-packages\ftrack_api\query.py", line 35, in _fetch_results    self._results = self._session._query(self._expression)  File "C:\Python27\lib\site-packages\ftrack_api\session.py", line 651, in _query    results = self._call(batch)  File "C:\Python27\lib\site-packages\ftrack_api\session.py", line 1341, in _call    raise ftrack_api.exception.ServerError(error_message)ftrack_api.exception.ServerError: Server reported error: KeyError(u'project')

with this code.

 

My Task has a "project" key, so i don't know why it doesn't work (?).

I got the same error with other "object" keys (like status...)

 

Thank you,

Damien

[deex]

Hi,

 

Any answers please ?

[Mattias Lagergren]

Hi,

 

Unfortunately that query is not supported at the moment. Under the hood assignments.context can be either a project or a task. They have a common base called Context which only has name, id and a few other relations but not "project".

 

However you should still be able to do this (without preloading project):

assignment['context']['project']['name'] 

[eight]

It turns out that getting all tasks for a user is very slow, even if I filter out projects that are no longer active. It's taking about 45 seconds to load for someone with 181 active tasks. Is there a way to make it faster or is this just an inherently slow query?

I've tried two different ways. One is the way I did it in a previous post in this thread, and the other way is the way that Mattius did it. They both are similar in time.

[Mattias Lagergren]

Could you post a small self-contained test example of code that I can run and try to reproduce it here? As minimalistic as possible to highlight the issue

[eight]

I realized my problem while I was trying to create an example for you. I was looping through each task and accessing a key that wasn't projected making it a lot slower. It was for parent.type.name. Yet, I can't select this because I get a ServerError. It must be similar to the problem deex had. 

Here's the example code if you still want to see it:

session = ftrack_api.Session()
query = 'User where username is "fred"'
user = session.query(query).one()
query = 'Task where project.status is active and assignments any (resource_id={})'.format(user['id'])
tasks = session.query(query).all()

 

[Mattias Lagergren]

7 hours ago, eight said:

I realized my problem while I was trying to create an example for you. I was looping through each task and accessing a key that wasn't projected making it a lot slower.

Ah, yes - those are sneaky! I usually start looking there myself if I come over code that runs slow.

Quote

Yet, I can't select this because I get a ServerError. It must be similar to the problem deex had.

Sounds likely that, that is the issue. The "parent" can either be a TypedContext or a Project so in the backend we treat it as their common base class Context (which has no type). 

[eight]

I have another update. The missing select statement was only a part of the problem. The rest was because of FileCache.

I tried the example below with and without the session cache (183 tasks). With the cache, it took about 13 seconds, and without, it took about 4 seconds. The big difference showed when adding a lot of select statements. With select statements + cache, it took about 35 seconds while select statements without cache took about 6 seconds.

import os
import time
import tempfile
import ftrack_api

def testCacheMaker(session):
    testCachePath = os.path.join(tempfile.gettempdir(), 'ftrack')
    try:
        os.makedirs(testCachePath)
    except:
        pass
    testCachePath = os.path.join(testCachePath, 'test_cache.dbm')
    print 'testCachePath: {}'.format(testCachePath)
    
    return ftrack_api.cache.SerialisedCache(
        ftrack_api.cache.FileCache(testCachePath),
        encode=session.encode,
        decode=session.decode
    )

def test():
    start = time.time()
    session = ftrack_api.Session(cache=testCacheMaker)
    query = 'User where username is "fred"'
    user = session.query(query).one()
    query = 'Task where project.status is active and assignments any (resource_id={})'.format(user['id'])
    tasks = session.query(query).all()
    print len(tasks)
    print 'time: {}'.format(time.time() - start)

 

[Mattias Lagergren]

On 2/6/2016 at 3:48 AM, eight said:

I have another update. The missing select statement was only a part of the problem. The rest was because of FileCache.

It would be interesting to know how much is due to the FileCache and rule out any extra overhead from layered caches. Could you try and use an additional MemoryCache cache instead of the FileCache? Just to see what happens.

[eight]

So you mean the cache maker would look like this?

def testCacheMaker(session):
    return ftrack_api.cache.MemoryCache()

I get very similar performance as not using a FileCache. Also, I forgot to mention that I was deleting the file that the FileCache made before each run.

[Mattias Lagergren]

8 hours ago, eight said:

I get very similar performance as not using a FileCache.

Ok, cool - I will make a note to the other developers about this. We are currently looking at other aspects of API performance, but I will mention this to the team 

[eight]

Thank you for looking into it!