- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
All entities where sub-collection count = 0
Joined: 02-Oct-2008
LLBLGen v. 3.0 Final - July 12, 2010.
Two class - Self-servicing
In the following query, I am trying to get a list of all customers as well as a list of customers with and without invoices. I can get "CustomersWithInvoices" and "All Customers"...but I'm having a hard time getting "CustomersWithoutInvoices".
Basically, I want to get all customers where TblCustomerEntity.TblInvoices count is 0.
How would I do this?
Thanks a lot!
Timothy
DateTime batchDateFrom = RadDatePickerBatchDateFrom.SelectedDate.Value;
DateTime batchDateTo = RadDatePickerBatchDateTo.SelectedDate.Value;
TblCustomerCollection customers = new TblCustomerCollection();
IPredicateExpression filter = new PredicateExpression();
RelationCollection relations = new RelationCollection();
SortExpression sorter = new SortExpression();
relations.Add(TblCustomerEntity.Relations.TblCodeTypeCustomerGroupsLinkEntityUsingCustId);
relations.Add(TblCodeTypeCustomerGroupsEntity.Relations.TblCodeTypeCustomerGroupsLinkEntityUsingGroupId);
if (DropDownListInvoiceOptions.SelectedValue == "CustomersWithInvoices")
{
//*********************************************************************************
//Adding this relation limits the customers to just those that have invoices, it seems...
//*********************************************************************************
relations.Add(TblCustomerEntity.Relations.TblInvoiceEntityUsingCustId);
filter.Add(TblInvoiceFields.BatchDate >= batchDateFrom);
filter.Add(TblInvoiceFields.BatchDate <= batchDateTo);
if (DropDownListGroup.SelectedValue != "All Groups")
{
filter.AddWithAnd(TblCodeTypeCustomerGroupsFields.GroupId == DropDownListGroup.SelectedValue);
}
}
else if (DropDownListInvoiceOptions.SelectedValue == "CustomersWithoutInvoices")
{
//*********************************************************************************
//How do I just get customers who do not have invoices...?
//*********************************************************************************
//relations.Add(TblCustomerEntity.Relations.TblInvoiceEntityUsingCustId);
//
//filter.Add(TblInvoiceFields.BatchDate >= batchDateFrom);
//filter.Add(TblInvoiceFields.BatchDate <= batchDateTo);
//
//if (DropDownListGroup.SelectedValue != "All Groups")
//{
// filter.AddWithAnd(TblCodeTypeCustomerGroupsFields.GroupId == DropDownListGroup.SelectedValue);
//}
}
else
{
if (DropDownListGroup.SelectedValue != "All Groups")
{
filter.Add(TblCodeTypeCustomerGroupsFields.GroupId == DropDownListGroup.SelectedValue);
}
}
sorter.Add(TblCustomerFields.LastName | SortOperator.Ascending);
customers.GetMulti(filter, 0, sorter, relations);
Joined: 02-Oct-2008
Walaa wrote:
Think of the SQL query first.
Please post the SQL query that will bring you Customers who don't have invoices?
This Sql query gives me the results that I want...
It returns all customers in the "PIA" group who did not have an invoice between 12/24/2009 and 12/24/2010.
BTW, thanks for your help!
select distinct customer.* from tblCustomer customer, tblInvoice invoice, [tblCodeType-CustomerGroupsLink] cgl where (select count(*) from tblInvoice i where customer.CustId = i.CustId and (i.BatchDate >= '12/24/2009' and i.BatchDate <= '12/24/2010') ) = 0 and cgl.CustId = customer.CustId and cgl.GroupId = 'PIA'
Joined: 21-Aug-2005
This Sql query gives me the results that I want...
It returns all customers in the "PIA" group who did not have an invoice between 12/24/2009 and 12/24/2010.
BTW, thanks for your help!
select distinct customer.* from tblCustomer customer, tblInvoice invoice, [tblCodeType-CustomerGroupsLink] cgl where (select count(*) from tblInvoice i where customer.CustId = i.CustId and (i.BatchDate >= '12/24/2009' and i.BatchDate <= '12/24/2010') ) = 0 and cgl.CustId = customer.CustId and cgl.GroupId = 'PIA'
It's easier this way:
SELECT *
FROM Customer
INNER JOIN Group ON Customer.CustId = Group.CustId
WHERE
Group.GroupId = 'PIA'
AND
CustId NOT IN (SELECT CustId FROM Invoice WHERE BatchDate BETWEEN '12/24/2009' AND '12/24/2010')
And for that you only need a FieldCompareSetPredicate to implement the NOT IN predicate.
Joined: 02-Oct-2008
Walaa wrote:
This Sql query gives me the results that I want...
It returns all customers in the "PIA" group who did not have an invoice between 12/24/2009 and 12/24/2010.
BTW, thanks for your help!
select distinct customer.* from tblCustomer customer, tblInvoice invoice, [tblCodeType-CustomerGroupsLink] cgl where (select count(*) from tblInvoice i where customer.CustId = i.CustId and (i.BatchDate >= '12/24/2009' and i.BatchDate <= '12/24/2010') ) = 0 and cgl.CustId = customer.CustId and cgl.GroupId = 'PIA'
It's easier this way:
SELECT * FROM Customer INNER JOIN Group ON Customer.CustId = Group.CustId WHERE Group.GroupId = 'PIA' AND CustId NOT IN (SELECT CustId FROM Invoice WHERE BatchDate BETWEEN '12/24/2009' AND '12/24/2010')
And for that you only need a FieldCompareSetPredicate to implement the NOT IN predicate.
Thanks. I will give that a try...
Still, I would be interested in how I could do this query in the case that I wanted to know the following cases:
The number of invoices in relation to X...
(select count(*) from tblInvoice i where customer.CustId = i.CustId
and (i.BatchDate >= '12/24/2009' and i.BatchDate <= '12/24/2010')
) = X
For instance, where X = 0, X = 2, X > 2, X <= 10...that kind of thing.
Thanks!
Joined: 28-Nov-2005
timothyleerussell wrote:
Still, I would be interested in how I could do this query in the case that I wanted to know the following cases:
The number of invoices in relation to X...
(select count(*) from tblInvoice i where customer.CustId = i.CustId and (i.BatchDate >= '12/24/2009' and i.BatchDate <= '12/24/2010') ) = X
For instance, where X = 0, X = 2, X > 2, X <= 10...that kind of thing.
Take a look at this snippet: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11938&StartAtMessage=0𐌑
Joined: 02-Oct-2008
daelmo wrote:
timothyleerussell wrote:
Still, I would be interested in how I could do this query in the case that I wanted to know the following cases:
The number of invoices in relation to X...
(select count(*) from tblInvoice i where customer.CustId = i.CustId and (i.BatchDate >= '12/24/2009' and i.BatchDate <= '12/24/2010') ) = X
For instance, where X = 0, X = 2, X > 2, X <= 10...that kind of thing.
Take a look at this snippet: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=11938&StartAtMessage=0𐌑
That looks like what I need. I'll give it a try. Thanks for your help!
Timothy
Joined: 02-Oct-2008
So, I thought I would try getting the "in / not in" working first before I moved on to trying to get the results where count == N.
But, I must be doing something wrong.
I am getting the correct values for "All customers" and "Customers with invoices" but am still having a hard time getting "Customers without invoices".
This is the code:
DateTime batchDateFrom = RadDatePickerBatchDateFrom.SelectedDate.Value;
DateTime batchDateTo = RadDatePickerBatchDateTo.SelectedDate.Value;
TblCustomerCollection customers = new TblCustomerCollection();
IPredicateExpression filter = new PredicateExpression();
RelationCollection relations = new RelationCollection();
SortExpression sorter = new SortExpression();
relations.Add(TblCustomerEntity.Relations.TblCodeTypeCustomerGroupsLinkEntityUsingCustId);
relations.Add(TblCodeTypeCustomerGroupsEntity.Relations.TblCodeTypeCustomerGroupsLinkEntityUsingGroupId);
if (DropDownListGroup.SelectedValue != "All Groups")
{
filter.Add(TblCodeTypeCustomerGroupsFields.GroupId == DropDownListGroup.SelectedValue);
}
if (DropDownListInvoiceOptions.SelectedValue == "CustomersWithInvoices")
{
relations.Add(TblCustomerEntity.Relations.TblInvoiceEntityUsingCustId);
IPredicateExpression subFilter = new PredicateExpression();
FieldCompareSetPredicate filteredByInvoiceCount = new FieldCompareSetPredicate(
TblCustomerFields.CustId, TblInvoiceFields.CustId,
SetOperator.In, null);
subFilter.Add(filteredByInvoiceCount);
subFilter.Add(new FieldBetweenPredicate(TblInvoiceFields.BatchDate, batchDateFrom, batchDateTo));
filter.Add(subFilter);
}
if (DropDownListInvoiceOptions.SelectedValue == "CustomersWithoutInvoices")
{
relations.Add(TblCustomerEntity.Relations.TblInvoiceEntityUsingCustId);
IPredicateExpression subFilter = new PredicateExpression();
FieldCompareSetPredicate filteredByInvoiceCount = new FieldCompareSetPredicate(
TblCustomerFields.CustId, TblInvoiceFields.CustId,
SetOperator.In, null, true); //**Attempting to negate which works in my sql query**
subFilter.Add(filteredByInvoiceCount);
subFilter.Add(new FieldBetweenPredicate(TblInvoiceFields.BatchDate, batchDateFrom, batchDateTo));
filter.Add(subFilter);
}
sorter.Add(TblCustomerFields.LastName | SortOperator.Ascending);
customers.GetMulti(filter, 0, sorter, relations);
The sql code that I am using to validate the results:
**** Invoices in the batch date (sql returns 17 rows, my llblgen code returns 17)**
select distinct customer.* from tblCustomer customer, tblInvoice invoice, [tblCodeType-CustomerGroupsLink] cgl
where customer.CustId in
(select i.CustId from tblInvoice i where customer.CustId = i.CustId
and (i.BatchDate >= '12/24/2010' and i.BatchDate <= '12/24/2010')
)
and cgl.CustId = customer.CustId
and cgl.GroupId = 'PIA'
**** No invoices in the batch date (sql returns 9 rows, my llblgen code returns 0)**
select distinct customer.* from tblCustomer customer, tblInvoice invoice, [tblCodeType-CustomerGroupsLink] cgl
where customer.CustId not in
(select i.CustId from tblInvoice i where customer.CustId = i.CustId
and (i.BatchDate >= '12/24/2010' and i.BatchDate <= '12/24/2010')
)
and cgl.CustId = customer.CustId
and cgl.GroupId = 'PIA'
**** Select all in the group (sql returns 26 rows, my llblgen code returns 26)**
select distinct customer.* from tblCustomer customer, tblInvoice invoice, [tblCodeType-CustomerGroupsLink] cgl
where cgl.CustId = customer.CustId
and cgl.GroupId = 'PIA'
It seems pretty obvious that I am making a mistake in how I am formulating the LLBLGen code but I'm not sure what I'm doing wrong.
Thanks! Timothy
Joined: 28-Nov-2005
In this code:
FieldCompareSetPredicate filteredByInvoiceCount = new FieldCompareSetPredicate(
TblCustomerFields.CustId, TblInvoiceFields.CustId,
SetOperator.In, null, true); //**Attempting to negate which works in my sql query**
you are specifying nothing in the predicate expression filter. So this subquery will return all Ids and the NOT IN will result in 0 records. According to your approximate sql code...
(i.BatchDate >= '12/24/2010' and i.BatchDate <= '12/24/2010')
you are missing the date filters. If you are not sure, take a look at the Generated Sql.
Joined: 02-Oct-2008
daelmo wrote:
In this code:
FieldCompareSetPredicate filteredByInvoiceCount = new FieldCompareSetPredicate( TblCustomerFields.CustId, TblInvoiceFields.CustId, SetOperator.In, null, true); //**Attempting to negate which works in my sql query**
you are specifying nothing in the predicate expression filter. So this subquery will return all Ids and the NOT IN will result in 0 records. According to your approximate sql code...
(i.BatchDate >= '12/24/2010' and i.BatchDate <= '12/24/2010')
you are missing the date filters. If you are not sure, take a look at the Generated Sql.
You are extremely correct. I have read up on it a little more in the docs and I have it working.
Thanks a lot for your help!
For others reference, this is the working code:
DateTime batchDateFrom = RadDatePickerBatchDateFrom.SelectedDate.Value;
DateTime batchDateTo = RadDatePickerBatchDateTo.SelectedDate.Value;
TblCustomerCollection customers = new TblCustomerCollection();
IPredicateExpression filter = new PredicateExpression();
RelationCollection relations = new RelationCollection();
SortExpression sorter = new SortExpression();
relations.Add(TblCustomerEntity.Relations.TblCodeTypeCustomerGroupsLinkEntityUsingCustId);
relations.Add(TblCodeTypeCustomerGroupsEntity.Relations.TblCodeTypeCustomerGroupsLinkEntityUsingGroupId);
if (DropDownListGroup.SelectedValue != "All Groups")
{
filter.Add(TblCodeTypeCustomerGroupsFields.GroupId == DropDownListGroup.SelectedValue);
}
if (DropDownListInvoiceOptions.SelectedValue == "CustomersWithInvoices")
{
relations.Add(TblCustomerEntity.Relations.TblInvoiceEntityUsingCustId);
IPredicateExpression subFilter = new PredicateExpression();
FieldCompareSetPredicate filteredByInvoiceCount = new FieldCompareSetPredicate(
TblCustomerFields.CustId, TblInvoiceFields.CustId,
SetOperator.In, new FieldBetweenPredicate(TblInvoiceFields.BatchDate, batchDateFrom, batchDateTo));
subFilter.Add(filteredByInvoiceCount);
filter.Add(subFilter);
}
if (DropDownListInvoiceOptions.SelectedValue == "CustomersWithoutInvoices")
{
relations.Add(TblCustomerEntity.Relations.TblInvoiceEntityUsingCustId);
IPredicateExpression subFilter = new PredicateExpression();
FieldCompareSetPredicate filteredByInvoiceCount = new FieldCompareSetPredicate(
TblCustomerFields.CustId, TblInvoiceFields.CustId,
SetOperator.In, new FieldBetweenPredicate(TblInvoiceFields.BatchDate, batchDateFrom, batchDateTo), true);
subFilter.Add(filteredByInvoiceCount);
filter.Add(subFilter);
}
sorter.Add(TblCustomerFields.LastName | SortOperator.Ascending);
customers.GetMulti(filter, 0, sorter, relations);