Query with join

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.

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);

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);

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

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" } }

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"

}

};

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);

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

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

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