Hi all,

I'm trying to map a following sql query into the LLBLGen:

select *
from product
where idproduct in
(
SELECT idproduct
FROM 
  public.dicttext 
  inner join public.product2property on product2property.idvalue = dicttext.iddicttext
WHERE 
  dicttext.text like '%AQQ%'
  and product2property.idproperty in (15, 16)
group by idproduct
having count(1) >= 2
)

The problem is that I cannot find any information regarding the group by, having keywords in the sub query.

How can I achieve a following predicate?

group by idproduct
having count(1) >= 2

Could You help me with a problem?

Kind Regards, MiloszeS

There is a section in the documentation about using GROUP BY and HAVING

http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/Adapter/Using%20TypedViews,%20TypedLists%20and%20Dynamic%20Lists/gencode_usinggroupbyhaving_adapter.htm

Matt

Ok, but the documentation says that:

This section discusses the usage of GROUP BY and HAVING clauses with typed lists, typed views and dynamic lists. Usage is the same for all three.

I need to apply this for the standard EntityCollection.

I need to retreive an EntityCollection<Product> fulfills the subquery criteria.

The you also need a derived table definition

http://www.llblgen.com/documentation/2.6/Using%20the%20generated%20code/gencode_derivedtabledynamicrelation.htm

There is an example in there about retrieving an entity collection using a derived table subquery with a group by in it - just what you need...

Matt

I've followed a mentioned example and created the following code.

IRelationPredicateBucket bucket = new RelationPredicateBucket();
            IPredicateExpression filter = new PredicateExpression();
            IRelationCollection relations = new RelationCollection();
            ICollection<long> filterTypes = new List<long>();
            int productPropertiesDesiredCount = 0;
            const string sqAlias = "sqp";
            const string productAlias = "P";



relations.Add(Product2PropertyEntity.Relations.DictTextEntityUsingIdValue);


if (!string.IsNullOrEmpty(searchExpression))
            {               
                filter.Add(DictTextFields.Text % ("%" + searchExpression + "%"));
            }

            if (searchProductName)
            {
                filterTypes.Add(this.propertyTypeDictionary.ProductFullName);
                ++productPropertiesDesiredCount;
            }



filter.Add(Product2PropertyFields.IdProperty == filterTypes);


            //subquery with group by
            ResultsetFields sqFields = new ResultsetFields(1);
            sqFields.DefineField(Product2PropertyFields.IdProduct, 0);
            GroupByCollection sqGroupBy = new GroupByCollection(sqFields[0]);
            DerivedTableDefinition sqDerivedTableDef = new DerivedTableDefinition(sqFields, sqAlias, filter, relations, sqGroupBy);

            //relation (subquery)
            DynamicRelation dynRel = new DynamicRelation(sqDerivedTableDef,  JoinHint.Inner, EntityType.ProductEntity,  productAlias, 
                                                (new EntityField2(Product2PropertyFieldIndex.IdProduct.ToString(), sqAlias, typeof(Int64)) == ProductFields.IdProduct.SetObjectAlias(productAlias)));



            bucket.Relations.Add(dynRel);
            bucket.SelectListAlias = productAlias;

When I'm fetching the Collection using the mentioned bucket application throws the following exception:

• {SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: Unable to cast object of type 'System.Collections.Generic.List1[System.Int64]' to type 'System.IConvertible'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.InvalidCastException: Unable to cast object of type 'System.Collections.Generic.List1[System.Int64]' to type 'System.IConvertible'. at NpgsqlTypes.BasicNativeToBackendTypeConverter.ToBasicType[T](NpgsqlNativeTypeInfo TypeInfo, Object NativeData) at NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackendPlainQuery(Object NativeData) at NpgsqlTypes.NpgsqlNativeTypeInfo.ConvertToBackend(Object NativeData, Boolean ForExtendedQuery) at Npgsql.NpgsqlCommand.GetClearCommandText() at Npgsql.NpgsqlCommand.GetCommandText() at Npgsql.NpgsqlQuery.WriteToStream(Stream outputStream) at Npgsql.NpgsqlReadyState.QueryEnum(NpgsqlConnector context, NpgsqlCommand command) at Npgsql.NpgsqlConnector.QueryEnum(NpgsqlCommand queryCommand) at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) --- End of inner exception stack trace --- at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Int32 pageNumber, Int32 pageSize) at Facades.FacadeBase.FetchEntityCollection(IDataAccessAdapter adapter, IEntityCollection2 collectionToFill, IRelationPredicateBucket predicateBucket, IPrefetchPath2 prefetchPath, ISortExpression sortExpr, Int32 pageNumber, Int32 pageSize)

...

What I'm doing wrong?

MiloszeS

select * from product where idproduct in ( SELECT idproduct FROM public.dicttext inner join public.product2property on product2property.idvalue = dicttext.iddicttext WHERE dicttext.text like '%AQQ%' and product2property.idproperty in (15, 16) group by idproduct having count(1) >= 2 )

Please use a FieldComparesetPredicate, to implement "where idproduct in (Select ....)"

You should use the following overload which accepts IGroupByCollection as the last parameter.

public FieldCompareSetPredicate( 
   IEntityFieldCore field,
   IFieldPersistenceInfo persistenceInfoField,
   IEntityFieldCore setField,
   IFieldPersistenceInfo persistenceInfoSetField,
   SetOperator operatorToUse,
   IPredicate filter,
   IRelationCollection relations,
   string objectAlias,
   long maxNumberOfItemsToReturn,
   ISortExpression sorter,
   bool negate,
   IGroupByCollection groupByClause
)

I've made a following query. It's working how I expect - maybe it'll be useful for someone.

ResultsetFields sqFields = new ResultsetFields(1);
            sqFields.DefineField(Product2PropertyFields.IdProduct, 0);
            GroupByCollection sqGroupBy = new GroupByCollection(sqFields[0]);
            List<long> filterTypes = new List<long>() { 15, 16 };

            IRelationCollection sqRelations = new RelationCollection(Product2PropertyEntity.Relations.DictTextEntityUsingIdValue);

            var sqFilter = new PredicateExpression(DictTextFields.Text % string.Concat('%', "AQQ", '%'));
            sqFilter.Add(Product2PropertyFields.IdProperty == filterTypes);
            var mainFilter = new FieldCompareSetPredicate(ProductFields.IdProduct, null, sqFields[0], null, SetOperator.In, sqFilter, sqRelations, null, 0, null, false, sqGroupBy);



            IRelationPredicateBucket bucket = new RelationPredicateBucket(mainFilter);

            EntityCollection<ProductEntity> col = new EntityCollection<ProductEntity>();

            try
            {
                using (var adapter = CreateAdapter())
                {
                    adapter.FetchEntityCollection(col, bucket);
                }
            }
            catch (Exception ex)
            {

            }

Which gives a following sql:

SELECT "public"."product"."idproduct" AS "IdProduct", "public"."product"."iditemtype" AS "IdItemType", "public"."product"."idstatus" AS "IdStatus" 
FROM "public"."product" 
WHERE ( ( "public"."product"."idproduct" IN 
    (SELECT "public"."product2property"."idproduct" AS "IdProduct" 
    FROM ( "public"."dicttext"  
    INNER JOIN "public"."product2property"  ON  "public"."dicttext"."iddicttext"="public"."product2property"."idvalue") 
    WHERE ( "public"."dicttext"."text" LIKE :Text1 
        AND "public"."product2property"."idproperty" IN (:IdProperty2, :IdProperty3)) 
    GROUP BY "public"."product2property"."idproduct")))

So everything is how I want. But there is one issue which didn't allow me to reach a proper behavior (till now). If you change a following line of code:

List<long> filterTypes = new List<long>() { 15, 16 };

into

IEnumerable<long> filterTypes = new List<long>() { 15, 16 };

or ICollection<long>, the FetchEntityCollection throws an exception mentioned in my previous post. When you change it back to the List it works perfect. Why?

Regards, MiloszeS

The list passed must implement IConvertible. As you as declaring with interface that exception is expected. You can use IList thought.

Thanks for your feedback about the query btw.