Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Hardcoded alias needed for query to work
 

Pages: 1
LLBLGen Pro Runtime Framework
Hardcoded alias needed for query to work
Page:1/1 

  Print all messages in this thread  
Poster Message
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 16-Jan-2018 21:12:11.  
LLBLGenPro v4.2

Code:
            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

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

  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 16-Jan-2018 22:03:05.  
This seems to work
Code:
                    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"?


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14484 posts
# Posted on: 17-Jan-2018 06:56:29.  
Could you please try something like this?

Code:
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")
);
  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37476 posts
# 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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.


  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14484 posts
# Posted on: 17-Jan-2018 17:00:39.  
Quote:
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.
  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# 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.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37476 posts
# 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 Regular Smiley). 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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 21-Jan-2018 17:09:38.  
The full code was

Quote:
        [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! Embarrassed
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")


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37476 posts
# Posted on: 21-Jan-2018 21:34:44.  
Glad it works! Regular Smiley Yes it doesn't use aliases specified as-is, it creates its own. Regular Smiley
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.