Search for WorkItem filtered by WorkItemType

[aslakknutsen]

When searching for work items to link, a link type has a certain source target type it supports.

When a given link type is chosen, it's target work item types should be used as a filter to search for possible matches.

[tsmaeder]

I would expect work items of subtypes to be included as well.

[tsmaeder]

By changing the parent_path to path and including the name of the type itself in the path, I expect this query would work:

SELECT count(*) over () as cnt2 , * 
FROM work_items w 
JOIN work_item_types w1 on w1.name=w.type 
JOIN work_item_types w2 on w2.path like (w1.path || '%') , 
to_tsquery('english', 'npe:*') as query, 
ts_rank(tsv, query) as rank 
WHERE (tsv @@ query) AND (w.type in('system.bug')) ORDER BY rank desc,w.updated_at desc LIMIT 100

unfortunately, we have little structure in our types right now and only system.bug type work items in our test database to try this out.

[tsmaeder]

Testing showed that we need to go with a subselect instead of a join.