Post

5 followers Follow
0
Avatar

Query with join

Hi

Is there a way to perform a query with condition using "SQL join".

For example retrieve all "WorkItem" entity which is related to
"WorkItemHierarchyLink" that its field "AttachToRoadmap"
equals 1?

Thanks

Import from old forum Answered

Please sign in to leave a comment.

10 comments

0
Avatar

You don't need a join for that. You could perform your query on WorkItemHierarchyLink filtering it to your needs but when you ask which fields to return, you can ask for "sub" fields (e.g. WorkItem.Name). This way you can get information from the related entity in one query.

Clarizen Team 0 votes
Comment actions Permalink
0
Avatar

Hi,

I have same question, I tried, but failed to filter by "sub" fields, could you please correct my codes ? Thanks!

            EntityQuery qryWBS = new EntityQuery();

            Compare cond1 = new Compare();

            cond1.LeftExpression = new FieldExpression() { FieldName = "AttachToRoadmap" };

            cond1.Operator = Operator.Equal;

            cond1.RightExpression = new ConstantExpression() { Value = 1 };

            Compare cond2 = new Compare();

            cond2.LeftExpression = new FieldExpression() { FieldName = "Child.Project.Name" }; // Error here !!

            cond2.Operator = Operator.Equal;

            cond2.RightExpression = new ConstantExpression() { Value = "MyProject001" };

            qryWBS.TypeName = "WorkItemHierarchyLink";

            qryWBS.Where = new And() { Conditions = new Condition[] { cond1, cond2 } };            

            qryWBS.Fields = WBSFields;

            QueryResult queryResult = clarizen.Query(qryWBS);

Import from old forum 0 votes
Comment actions Permalink
0
Avatar

Hi William

For the sample you've posted, I'd use a simple compare condition on the child Project ID:

cond2 = new Compare

            {

                LeftExpression = new FieldExpression { FieldName = "Child" },

                Operator = Operator.Equal,

                RightExpression = new ConstantExpression { Value = new EntityId { TypeName = "Milestone", Value = "e573ed2bf0334019bafef2bf291a1d29" } }

            };

However, if you really need a condition on a sub field, you do that by using the In operator and specifying a QueryExpresssion on the RightExpression:

            EntityQuery qryWBS = new EntityQuery();

            Compare cond1 = new Compare();

            cond1.LeftExpression = new FieldExpression() { FieldName = "AttachToRoadmap" };

            cond1.Operator = Operator.Equal;

            cond1.RightExpression = new ConstantExpression() { Value = 1 };

            Compare cond2 = new Compare();

            cond2.LeftExpression = new FieldExpression() { FieldName = "Child" };

            cond2.Operator = Operator.In ;

            cond2.RightExpression = new QueryExpression()

                            {

                                Query = new EntityQuery

                                    {

                                        TypeName = "Milestone",

                                        Where = new Compare

                                        {

                                            LeftExpression = new FieldExpression { FieldName = "Name" },

                                            Operator = Operator.Contains,

                                            RightExpression = new ConstantExpression { Value = "Closing Milestone" }

                                        }

                                    }

                            };

            qryWBS.TypeName = "WorkItemHierarchyLink";

            qryWBS.Where = new And() { Conditions = new Condition[] { cond1, cond2 } };

            qryWBS.Fields = new string[] {  };

            QueryResult queryResult = service.Query(qryWBS);

Clarizen Team 0 votes
Comment actions Permalink
0
Avatar

EntityQuery query = new EntityQuery();

query.TypeName = "Workitem";

OrderBy orderByName = new OrderBy();

orderByName.FieldName = "Name";

Compare cond1 = new Compare();

cond1.LeftExpression = new FieldExpression() { FieldName = "TrackStatus" };

cond1.Operator = Operator.Equal;

cond1.RightExpression = new ConstantExpression()

{

Value = "Off Track" 

};

Compare cond2 = new Compare();

cond2.LeftExpression = new FieldExpression() { FieldName = "TrackStatus" };

cond2.Operator = Operator.Equal;

cond2.RightExpression = new ConstantExpression()

{

Value = "At Risk" 

};

query.Where = new Or() { Conditions = new Condition[] { cond1, cond2 } };

 

Getting Error  Value Off Track is invalid for this field

Hari 0 votes
Comment actions Permalink
0
Avatar

The right side value needs to be an EntityId object and not a constant string

cond1.RightExpression = new ConstantExpression() { Value = new EntityId() { TypeName = "TrackStatus", Value = "Off Track" } }

Uri Tzikoni 0 votes
Comment actions Permalink
0
Avatar

Hi thanks for the reply i got that can you let me know how to write in condition for below query

 

Compare condition = new Compare();

condition.LeftExpression = new FieldExpression() { FieldName = "TrackStatus" };

condition.Operator = Operator.Equal;

condition.RightExpression = new ConstantExpression()

{

Value = new EntityId

{

TypeName = "TrackStatus",

Value = "Off Track"

}

};

Compare condition1 = new Compare();

condition1.LeftExpression = new FieldExpression() { FieldName = "TrackStatus" };

condition1.Operator = Operator.Equal;

condition1.RightExpression = new ConstantExpression()

{

Value = new EntityId

{

TypeName = "TrackStatus",

Value = "At Risk"

}

};

Hari 0 votes
Comment actions Permalink
0
Avatar

I'm not sure i understand your request, but below there is code that works

 

EntityQuery query = new EntityQuery();

query.TypeName = "Task";

query.Fields = new string[] { "ExternalId", "Name" };

Compare condition = new Compare();

condition.LeftExpression = new FieldExpression() { FieldName = "TrackStatus" };

condition.Operator = Operator.Equal;

condition.RightExpression = new ConstantExpression()

{

Value = new EntityId

{

TypeName = "TrackStatus",

Value = "Off Track"

}

};

Compare condition1 = new Compare();

condition1.LeftExpression = new FieldExpression() { FieldName = "TrackStatus" };

condition1.Operator = Operator.Equal;

condition1.RightExpression = new ConstantExpression()

{

Value = new EntityId

{

TypeName = "TrackStatus",

Value = "At Risk"

}

};

query.Where = new Or() {Conditions = new Condition[]{condition,condition1}}; ;

QueryResult queryResult = clarizenClient.Query(query);

if (!queryResult.Success)

throw new Exception(queryResult.Error.Message);

Uri Tzikoni 0 votes
Comment actions Permalink
0
Avatar

Hi, im having an issue with a similar problem. I want to be able to get the fields of a project based on its parents ID. I am trying to do it in java. here is what i have written so far based of scouring the forums. It seems to only return the orignal parent. I have a feeling its because im comparing the parent entity with itself and not returning the child...

http://pastebin.com/fA5EA0hf

(Please ignore the noob code, im learning)

Thanks! :D

stewart dalton 0 votes
Comment actions Permalink
0
Avatar

Hi Stewart. I haven't tested your code, but you seem to be on the right track.

Some tips that will hopefully get you there:

  1. For a Project you will probably want the condition set on Parent _or _ParentProject (the Project field points to the same project, not the parent).
  2. If you have a condition on the Parent or ParentProject you don't want the condition on the SYSID on the Project itself (meaning: drop Compare1 on your code)
  3. If you have the parent's External ID (the Id used by the API) you can place an EQUAL condition on the Parent or ParentProject witha ConstantExpression (of type EntityId) on the RightExpression (meaning, Right2 on you code can be replaced by a simple ConstantExpression). See this post: https://success.clarizen.com/entries/28301828-Using-Link-Entity-in-query

Hope this helps.

Ophir

Ophir Kenig 0 votes
Comment actions Permalink
0
Avatar

Thanks Ophir, this is very usefull! When i get the code working i will post it here incase anyone else would like to use/see it.

Stew

stewart dalton 0 votes
Comment actions Permalink