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

Think of the SQL query first.

Please post the SQL query that will bring you Customers who don't have invoices?

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'

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.

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!

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&#66321

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&#66321

That looks like what I need. I'll give it a try. Thanks for your help!

Timothy

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

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.

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