New Posts New Posts RSS Feed: How can I get the opposite of the related records in a many to many relation
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

How can I get the opposite of the related records in a many to many relation

 Post Reply Post Reply
Author
IdeaBlade View Drop Down
Moderator Group
Moderator Group
Avatar

Joined: 30-May-2007
Location: United States
Posts: 353
Post Options Post Options   Quote IdeaBlade Quote  Post ReplyReply Direct Link To This Post Topic: How can I get the opposite of the related records in a many to many relation
    Posted: 12-Jul-2007 at 3:47pm
I am not a SQL expert so it was a bit of a challenge to come up with the query that satisfies your request.  I do think, though, that I have found it.
 
If I am interpreting your request correctly, a query that satisfies it is:
 
select Id from Customer where Id NOT IN (select DISTINCT CustomerId from OrderSummary where OrderSummary.EmployeeId <> 1)
 
in the case where you want to find all customers who have not been served by Employee Id 1.
 
To implement this query in IdeaBlade's Object Query Language (OQL) we add the clauses, subqueries, and operators that make up the query to it using Polish Reverse Notation syntax.  For example, the OQL query that is the equivalent of the SQL query "select * from OrderSummary where Id > 10240 AND Id < 10260" is:
 
             EntityQuery aOrderSummaryQuery = new EntityQuery(typeof(OrderSummary));
            aOrderSummaryQuery.AddClause("Id", EntityQueryOp.GT, 10240);
            aOrderSummaryQuery.AddClause("Id", EntityQueryOp.LT, 10260);
            aOrderSummaryQuery.AddOperator(EntityBooleanOp.And);
            EntityList<OrderSummary> orders = mPm.GetEntities<OrderSummary>(aOrderSummaryQuery);
 
The OQL for the SQL query that gives what you want looks like this:
 
           // Ultimately want to return Customer objects
            EntityQuery aCustomerQuery = new EntityQuery(typeof(Customer));
            // We are actually querying against Orders so we need a subquery that queries OrderSummary
            EntitySubquery aOrderSummarySubquery = new EntitySubquery(EntityRelationLink.From(typeof (Customer),"OrderSummaries"));
            aOrderSummarySubquery.AddClause("EmployeeId",RdbQueryOp.NE, 1); // The actual query
            // Now we will build the "query stack"
            aCustomerQuery.AddSubquery(aOrderSummarySubquery);
            aCustomerQuery.AddOperator(EntityBooleanOp.Not);
            // Fetch Customer Entities that satisfy this OQL query                

            EntityList<Customer> customers = mPm.GetEntities<Customer>(aCustomerQuery);
 
 
Back to Top
Customer View Drop Down
Senior Member
Senior Member
Avatar
User Submitted Questions to Support

Joined: 30-May-2007
Location: United States
Posts: 260
Post Options Post Options   Quote Customer Quote  Post ReplyReply Direct Link To This Post Posted: 12-Jul-2007 at 3:46pm

How can I get the opposite of the related records in a many to many relation. Thus if the employee has customers through the orders object I want to see the customers where the employee has  no orders from.

Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down