Hardcoded alias needed for query to work

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 16-Jan-2018 21:12:11   

LLBLGenPro v4.2

            var qf = new QueryFactory();

            var query = qf.Keyword
                .OrderBy(KeywordFields.Phrase | SortOperator.Ascending)
                .Select(
                    KeywordFields.ID,
                    KeywordFields.Phrase,
                    qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID).CountRow().As("ClassificationCount")
                );

ClassificationToKeywordFields is a simple join table holding KeywordID and ClassificationID and the above query works fine.

What I now want to do is keep the above CountRow() line but add another line which also uses the "qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID)" and counts where the ClassificationToKeywordFields.ClassificationIDs returned are used in another table. I can't work out how to do it or whether it is possible.

Something like

                    qf.Resource
                        .Where(ResourceFields.ClassificationID.In(
                            qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID).Select(ResourceFields.ID))
                        ).CountRow()

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 16-Jan-2018 22:03:05   

This seems to work

                    qf.Resource
                        .From(QueryTarget.InnerJoin(qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID))
                            .On(ClassificationToKeywordFields.ClassificationID.SetObjectAlias("LPA_L1") == ResourceFields.ClassificationID)
                        ).CountRow()


but I had to hardcode the "LPA_L1" which I don't like to do.

Where can I specify an alias to replace the default "LPA_L1"?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 17-Jan-2018 06:56:29   

Could you please try something like this?

var query = qf.Order
.OrderBy(OrderFields.OrderDate | SortOperator.Ascending)
.Select(
    OrderFields.OrderId,
    OrderFields.CustomerId,
    qf.OrderDetail.CorrelatedOver(OrderFields.OrderId == OrderDetailFields.OrderId).CountRow().As("LineItemsCount"),
    qf.OrderDetail.CorrelatedOver(OrderFields.OrderId == OrderDetailFields.OrderId).Where(
        OrderDetailFields.ProductId.In(
            qf.Create()
                .Select(ProductFields.ProductId)
                .Where(ProductFields.CategoryId.Equal(4)))
    )
    .CountRow().As("SpecialLineItemCount")
);

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 17-Jan-2018 09:04:02   

The second where clause doesn't look right - I'm not filtering on a constant.

Mine does work but I would just like to get rid of the constant alias name.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Jan-2018 11:43:33   

How would the sql look like, could you give an example of that please? CorrelatedOver simply defines a where clause for a nested query, so the COUNT(*) is on the nested query. So if the nested query should contain more fields in the projection, then you have to define a full nested query (and use TargetAs()). But it's easier if we have a bit of an idea what the sql looks like so we can give you the statements how to achieve that.

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 17-Jan-2018 14:06:58   

This is the SQL generated which appears to work:-

--Retrieval Query:

SELECT [Keyword].[ID], [Keyword].[Phrase], ( SELECT COUNT() AS [LPAV_] FROM [ResourceToKeyword]
WHERE ( [ResourceToKeyword].[KeywordID] = [Keyword].[ID])) AS [Count], ( SELECT COUNT(
) AS [LPAV] FROM [ClassificationToKeyword]
WHERE ( [ClassificationToKeyword].[KeywordID] = [Keyword].[ID])) AS [ClassificationCount], ( SELECT COUNT(*) AS [LPAV
] FROM ( ( SELECT [ClassificationToKeyword].[KeywordID], [ClassificationToKeyword].[ClassificationID] FROM [ClassificationToKeyword]
WHERE ( [ClassificationToKeyword].[KeywordID] = [Keyword].[ID])) [LPA_L1] -- Where does this come from?? INNER JOIN [Resource] ON [LPA_L1].[ClassificationID] = [Resource].[ClassificationID])) AS [LLBLV_1] FROM [Keyword]
ORDER BY [Keyword].[Phrase] ASC

The second occurrence of "LPA_L1" is the one I hard-coded, the first occurrence is generated by LLBLGen.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 17-Jan-2018 17:00:39   

The second where clause doesn't look right - I'm not filtering on a constant.

Mine does work but I would just like to get rid of the constant alias name.

Instead of the inner join, I'm using an IN predicate and a subquery. The constant filter is just to limit the results of the subquery, for the sake of testing the results.

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 17-Jan-2018 19:15:58   

Guys

I stumbled across the correct LLBLGen statements to use in message #2. It produces the SQL in message #6.

In my LLBLGen statements, I had to use ".SetObjectAlias("LPA_L1")" to match that already generated by LLBLGen.

It may well be safe to leave it like that but it would be safer if I could set the alias string on both sides. I just don't know where to do it. That is the question now.

Message #6 has '-- Where does this come from??' to indicate the LLBLGen autogenerated alias.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 18-Jan-2018 17:38:15   

Ok, but what's your full code of the query with the hardcoded alias? As your 'this seems to work' fragment doesn't match with the particular part in the sql as that uses a projection.

Could you post that please?

The projection is a subquery (the one with LPA_L1) so it gets an alias automatically (it has to simple_smile ). If you alias the query with 'As' you can then refer to that alias in your predicate (instead of LPA_L1), but I don't think I see the subquery which aliased as LPA_L1 in your snippets, so a full queryspec query in C# code would be great wink

Frans Bouma | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 21-Jan-2018 17:09:38   

The full code was

    [Test]
    public void TestKeywordCounts()
    {
        const string CountFieldName = "Count";
        const string ClassificationCountFieldName = "ClassificationCount";

        var qf = new QueryFactory();

        var query = qf.Keyword
            .OrderBy(KeywordFields.Phrase | SortOperator.Ascending)
            .Select(
                KeywordFields.ID,
                KeywordFields.Phrase,
                qf.ResourceToKeyword.CorrelatedOver(ResourceToKeywordFields.KeywordID == KeywordFields.ID).CountRow().As(CountFieldName),
                qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID).CountRow().As(ClassificationCountFieldName),

                qf.Resource
                    .From(QueryTarget.InnerJoin(qf.ClassificationToKeyword.CorrelatedOver(ClassificationToKeywordFields.KeywordID == KeywordFields.ID))
                        .On(ClassificationToKeywordFields.ClassificationID.SetObjectAlias("LPA_L1") == ResourceFields.ClassificationID)
                    ).CountRow()
            );

        using (var adapter = new DataAccessAdapter())
        {
            var result = adapter.FetchQuery(query);

            var xxx = result.Where(i => (int) i[3] > 0).ToArray();
            var yyy = result.Where(i => (int) i[4] > 0).ToArray();
        }
    }

I would have sworn I tried 'As' on every part of the query one a time but obviously I missed one! flushed I have now done as you suggested and it now works (though it ignores my suggested alias name and just uses the first letter from it: "Fred" becomes "LPA_F1")

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 21-Jan-2018 21:34:44   

Glad it works! simple_smile Yes it doesn't use aliases specified as-is, it creates its own. simple_smile

Frans Bouma | Lead developer LLBLGen Pro