AbleCommerce Shopping Cart Software

Posted: **Mon Apr 25, 2016 5:44 am**

I've added a bit column to the ac_products table and classes called IsMembership. I'm able to grab those products, no problem.

Now I want a Distinct list of Subscription Plans that are Membership Products. In SQL this works:
SELECT DISTINCT GroupId, Name
FROM ac_SubscriptionPlans
WHERE ProductId IN ( SELECT productID FROM ac_Products WHERE IsMembership = 1)

In the SubscriptionPlanRepository I created this:

Code: Select all

 /// <inheritdoc />
        public IList<SubscriptionPlan> LoadForMembership()
        {
            IList<Product> products = ProductDataSource.GetMembershipProducts();
            List<int> productIds = new List<int>();
            foreach (Product product in products)
            {
                productIds.Add(product.Id);
            }

            return NHibernateHelper.CreateCriteria<SubscriptionPlan>()
                 .SetProjection(Projections.Distinct(Projections.Property("GroupId")))
                .Add(Restrictions.In("Product.Id", productIds.ToArray()))
                .List<SubscriptionPlan>();
        }

Here's my error: could not resolve property: GroupId of: CommerceBuilder.Products.SubscriptionPlan

In SubscriptionPlan.cs I see this:

Code: Select all

        /// <summary>
        /// Gets or sets the Group for this entity.
        /// </summary>
        public virtual int GroupId
        {
            get { return this.Group != null ? this.Group.Id : 0; }
            set { this.Group = CommerceBuilder.DomainModel.EntityLoader.Load<Group>(value); }
        }

Any ideas? I don't totally understand "NHibernate" which is probably what the problem is

TIA!

Posted: **Mon Apr 25, 2016 9:03 am**

Shouldn't it be Property("Group.Id")?

Posted: **Tue Apr 26, 2016 12:25 am**

What Jay suggested should work. Properties like GroupId were introduced for backward compatibility of codes and may not be mapped in Nhibernate. In order to make these properties work with nhibernate query they must be mapped as read only within mappings. Just use it like as Jay suggested and it will work.

Posted: **Tue Apr 26, 2016 2:18 am**

Thank you for the responses. Unfortunately changing it to Group.Id gives me a new error.

Here's my code:

Code: Select all

        /// <inheritdoc />
        public IList<SubscriptionPlan> LoadForMembership()
        {
            IList<Product> products = ProductDataSource.GetMembershipProducts();
            List<int> productIds = new List<int>();
            foreach (Product product in products)
            {
                productIds.Add(product.Id);
            }

            return NHibernateHelper.CreateCriteria<SubscriptionPlan>()
       .SetProjection(Projections.Distinct(Projections.Property("Group.Id")))
                .Add(Restrictions.In("Product.Id", productIds.ToArray()))
                .List<SubscriptionPlan>();
        }

Here's how I would get the proper results in SQL:
SELECT DISTINCT GroupId, Name
FROM ac_SubscriptionPlans
WHERE ProductId IN ( SELECT productID FROM ac_Products WHERE IsMembership = 1)

That gives me this:
GroupId Name
9 Single Membership
10 Couples Membership
11 Promotional Membership
12 Honorary Membership

Without Distinct:
SELECT GroupId, Name
FROM ac_SubscriptionPlans
WHERE ProductId IN ( SELECT productID FROM ac_Products WHERE IsMembership = 1)

GroupId Name
9 Single Membership
10 Couples Membership
11 Promotional Membership
12 Honorary Membership
9 Single Membership

I think I know what the problem may be now. Are the GroupId's supposed to be unique in that table already? Just wondering if the way I use the subscriptions is the problem... Thank you!

UPDATE with ERROR:
{"Unable to perform find[SQL: SQL not available]"}
Inner Exception: {"The value \"9\" is not of type \"CommerceBuilder.Products.SubscriptionPlan\" and cannot be used in this generic collection.\r\nParameter name: value"}

Posted: **Tue Apr 26, 2016 3:14 am**

What is the new error?

Posted: **Wed Apr 27, 2016 1:40 am**

jguengerich wrote:What is the new error?

That would help! Sorry I just updated it with the new error. TIA!

AbleCommerce Shopping Cart Software

Modified source error: could not resolve property: GroupId

Modified source error: could not resolve property: GroupId

Re: Modified source error: could not resolve property: GroupId

Re: Modified source error: could not resolve property: GroupId

Re: Modified source error: could not resolve property: GroupId

Re: Modified source error: could not resolve property: GroupId

Re: Modified source error: could not resolve property: GroupId