Having without group by

Posts   
 
    
Kiltux
User
Posts: 25
Joined: 19-Sep-2013
# Posted on: 19-Sep-2013 15:13:27   

Hi LLBLGen,

I'm trying to create the following query:


SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE SourceIncidentId = 4 OR TargetIncidentId = 4
HAVING COUNT(*) > 2

But can't seem to produce a having clause without a group by field. It turns the having clause in a where clause but that is not the same

I currently have this:


EntityField2 countField = LinkFields.LinkId.SetAggregateFunction(AggregateFunction.CountRow);

EntityFields2 fields = new EntityFields2(1) { countField };

RelationPredicateBucket bucket = new RelationPredicateBucket(LinkFields.SourceIncidentId == 4 | LinkFields.TargetIncidentId == 4);

GroupByCollection groupBy = new GroupByCollection();
groupBy.HavingClause = new PredicateExpression(countField > 2);

DataTable table = new DataTable();
this.AdapterToUse.FetchTypedList(fields, table, bucket, 0, null, true, groupBy);

But this generates the following sql:


SELECT COUNT(*) AS LinkId 
FROM Links 
WHERE SourceIncidentId = 200 OR TargetIncidentId = 200

So i'm missing my having clause with the count

arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 19-Sep-2013 19:02:17   

This query will return 1 row. So are you saying that if the count)*) > 2 you want to get that 1 row and if it is <= 2 you want no rows back?

Seems you could just look at the result row and ignore it if the count(*) is greater than 2

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 19-Sep-2013 21:48:15   

The following SQL will yield the same result, could you please try implementing it.

SELECT *
FROM
{
SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE SourceIncidentId = 4 OR TargetIncidentId = 4
} q
WHERE q.NumberOfLinks > 2
Kiltux
User
Posts: 25
Joined: 19-Sep-2013
# Posted on: 20-Sep-2013 08:31:51   

arschr wrote:

This query will return 1 row. So are you saying that if the count)*) > 2 you want to get that 1 row and if it is <= 2 you want no rows back?

Seems you could just look at the result row and ignore it if the count(*) is greater than 2

The query is a part of an EXISTS statement, and the query can return a lot more rows that just 1. And the having is to test if the row meets the condition that the user has provided.

Walaa wrote:

The following SQL will yield the same result, could you please try implementing it.


SELECT *
FROM
{
SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE SourceIncidentId = 4 OR TargetIncidentId = 4
} q
WHERE q.NumberOfLinks > 2

Its a bit difficult to put the query in a subquery because it is a generated query, and the space that i can play with is about 1 query stuck_out_tongue_winking_eye But is it not possible to only put a having clausing in a query without the group by?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Sep-2013 09:02:34   

No, not supported.

You might also add the PK to the select list, so that you can use GroupBy on the PK, and hence you can use Having.

Kiltux
User
Posts: 25
Joined: 19-Sep-2013
# Posted on: 20-Sep-2013 09:13:31   

Walaa wrote:

No, not supported.

You might also add the PK to the select list, so that you can use GroupBy on the PK, and hence you can use Having.

That unfortunately does not work, because it will for example return 4 times a 1 instead of 1 time a 4


SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE SourceIncidentId = 26252 OR TargetIncidentId = 26252
GROUP BY LinkId
--HAVING COUNT(*) > 2

results in

1 1 1 1

and this


SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE  SourceIncidentId = 26252 OR TargetIncidentId = 26252
--GROUP BY LinkId
HAVING COUNT(*) > 2

results in

4

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 20-Sep-2013 12:23:57   

Kiltux wrote:

Walaa wrote:

The following SQL will yield the same result, could you please try implementing it.


SELECT *
FROM
{
SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE SourceIncidentId = 4 OR TargetIncidentId = 4
} q
WHERE q.NumberOfLinks > 2

Its a bit difficult to put the query in a subquery because it is a generated query, and the space that i can play with is about 1 query stuck_out_tongue_winking_eye But is it not possible to only put a having clausing in a query without the group by?

We only support having with group by so the user is forced to use aggregates as that's the main reason to use having in the first place (a having clause without aggregates makes no sense). A query without a having clause has an implicit group by() clause in most databases and some don't support having without group by, hence we only support it on a group by clause.

The workaround Walaa gave is how to get what you want, as a having clause is actually just a predicate on the set containing the having clause, and can be formulated just as the query you have.

Please show us the code you have and what version you're working with so we can suggest how to implement the workaround (e.g. through query spec which is the easiest).

Frans Bouma | Lead developer LLBLGen Pro
Kiltux
User
Posts: 25
Joined: 19-Sep-2013
# Posted on: 20-Sep-2013 13:59:21   

I work with LLBLGen 3.5

I cannot show the exact code because it is a part of our dynamic query system, and the fields are generated by what the user selects in the interface and thereby the conditions.

This is the sample code i can translate back to our code


EntityField2 countField = LinkFields.LinkId.SetAggregateFunction(AggregateFunction.CountRow);
EntityFields2 fields = new EntityFields2(1) { countField };

RelationPredicateBucket bucket = new RelationPredicateBucket(LinkFields.SourceIncidentId == 4 | LinkFields.TargetIncidentId == 4);

GroupByCollection groupBy = new GroupByCollection();
groupBy.HavingClause = new PredicateExpression(countField > 2);

DataTable table = new DataTable();
this.AdapterToUse.FetchTypedList(fields, table, bucket, 0, null, true, groupBy);
table.Dump();

And i must use the LLBLGen Pro Framework, and cannot use the QuerySpec framework because of the nature of the dynamic query.

Kiltux
User
Posts: 25
Joined: 19-Sep-2013
# Posted on: 20-Sep-2013 14:18:20   

I have created a temporary solution for the problem.


public class HavingClause : GroupByCollection
{   
    public HavingClause() : base(new EntityField2("()", null)) { }

    public override string ToQueryText(bool ignoreExpressions)
    {
        string text = base.ToQueryText(ignoreExpressions);
        if(text.StartsWith(".[()]"))
            text = text.Replace(".[()]", "()");
        return text;
    }
}


I realize that this isn't the ideal solution, but in this case it is easier to implement on my side instead of adjusting our dynamic query builder to support the subquery construction.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 20-Sep-2013 17:34:44   

You can create a DerivedTableDefinition from the elements you now use to build the query, and assign it to a DynamicRelation which you then add to a RelationCollection, so it's wrapped into the main query.

Anyway, your workaround works too. You can make it easier though:



public class HavingClauseWithEmptyGroupBy : GroupByCollection
{   
    public HavingClauseWithEmptyGroupBy () : base() { }

    public override string ToQueryText(bool ignoreExpressions)
    {
        this.HavingClause.DatabaseSpecificCreator = this.DatabaseSpecificCreator;
        var toReturn = string.Format(" HAVING {0}", this.HavingClause.ToQueryText(true ) );
        this.Parameters.AddRange( this.HavingClause.Parameters );
        return toReturn;
    }
}

No need to hack around it with a dummy field, this simply emits the having clause without the group by elements. The code in the ToQueryText is copied from the GroupByCollection.

Frans Bouma | Lead developer LLBLGen Pro
Kiltux
User
Posts: 25
Joined: 19-Sep-2013
# Posted on: 23-Sep-2013 08:25:46   

Otis wrote:

You can create a DerivedTableDefinition from the elements you now use to build the query, and assign it to a DynamicRelation which you then add to a RelationCollection, so it's wrapped into the main query.

Anyway, your workaround works too. You can make it easier though:



public class HavingClauseWithEmptyGroupBy : GroupByCollection
{   
    public HavingClauseWithEmptyGroupBy () : base() { }

    public override string ToQueryText(bool ignoreExpressions)
    {
        this.HavingClause.DatabaseSpecificCreator = this.DatabaseSpecificCreator;
        var toReturn = string.Format(" HAVING {0}", this.HavingClause.ToQueryText(true ) );
        this.Parameters.AddRange( this.HavingClause.Parameters );
        return toReturn;
    }
}

No need to hack around it with a dummy field, this simply emits the having clause without the group by elements. The code in the ToQueryText is copied from the GroupByCollection.

Ah smile thank you it is indeed better, i didn't knew what i would break if i didn't leave the construction as it is stuck_out_tongue_winking_eye