Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> With Ties
 

Pages: 1
LLBLGen Pro Runtime Framework
With Ties
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: 14-Sep-2018 07:24:14.  
How do I add "TOP 50 WITH TIES" to a QuerySpec SQL Query?
Limit(50) is as far as I've got.

LLBLGen v4.2
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8065 posts
# Posted on: 14-Sep-2018 07:46:25.  
Hi Simmotech.

Good question. I have never used “with ties” in generated code. Let me do some tests about the possibilities here...


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37455 posts
# Posted on: 14-Sep-2018 14:01:33.  
I don't think there's a way to insert that text snippet after TOP <param> in the select query, or you must try a DbFunctionCall("WITH TIES") and place that as the first field in the projection/select list (as an expression of that field)...
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: 14-Sep-2018 17:33:07.  
How did this get missed? Is it a SQL Server only thing? How do other databases deal with it?

I'll give your DBFunctionCall suggestion a try - thanks for that.

If it doesn't work, I may be back asking for help writing two queries to work around it - I did have a go but I am struggling.

PS What is with the new photo? - reminds me of Ray Davies!


  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 14-Sep-2018 18:18:17.  
Woohoo, it works!

Code:
.Select(() => new ResourceDTO
                 {
                     Ignore = ItemFields.ID.SetFieldAlias("Ignore").SetExpression(new DbFunctionCall("WITH TIES ", new object[] { DBNull.Value })).ToValue<object>(),
                     ResourceID = ItemFields.ResourceID.ToValue<int>()
                 })

(needs
Code:
public object Ignore;
adding to the DTO)

Any way of getting around the "Ignore = " bit?
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37455 posts
# Posted on: 15-Sep-2018 07:43:55.  
simmotech wrote:
How did this get missed? Is it a SQL Server only thing? How do other databases deal with it?

To be honest I never knew it existed till a few months ago. The SQL dialects are pretty massive in some databases and it simply never caught my eye to add it. thing is also that it's a pretty weird feature: it adds duplicates to the last row of the set. So the use case also never came up and therefore it managed to stay under the radar Regular Smiley

Quote:

PS What is with the new photo? - reminds me of Ray Davies!

Heh Regular Smiley Sometimes you have to get rid of old photos Regular Smiley Ray wished he had my hair! Wink

Quote:

Any way of getting around the "Ignore = " bit?

THere's a way I think: use this:

Code:

ResourceID = qf.Field("ResourceID").SetExpression(new DbFunctionCall("WITH TIES {0}", new object[] { ItemFields.ResourceID })).ToValue<int>()

and remove the Ignore field, but I'm not sure.

It's a bit of a hack though, as you simply prefix the field with 'WITH TIES'...


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: 15-Sep-2018 08:04:41.  
Here my use case - I think its a good use of WITH TIES
Code:
SELECT TOP 50 WITH TIES
i.ResourceID
FROM Item i
WHERE i.DateRemoved IS NULL
GROUP BY i.ResourceID
ORDER BY MIN(i.DateAdded) DESC


This is for a school library and is to show the last 50 (or so) new additions to the library.
When WITH TIES is used, it actually returns 64 items because there was a buying binge at the end of the date range - would be weird to miss out some books on that date but include others - and potentially randomly at that unless I add another ORDER BY.


Your suggestion worked perfectly!
I don't understand why though - my version had to pass in a null because otherwise '()' was added and wouldn't execute and putting something between them was the only was I could think to get around that.
Why does your solution exclude braces?
  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37455 posts
# Posted on: 16-Sep-2018 08:43:55.  
It excludes braces because it has a fragment merged into it (the {0}), so it assumes that can have () surrounding it. Otherwise it will emit braces as it assumes the fragment is the function name (so it appends (), without checking).

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: 09-Jul-2019 19:17:36.  
Beware what you wish for!

I've just had a frustrating afternoon panicking because my app was hanging big time on a TextRenderer.DrawText line that has been working fine for years!

Turns out that a query I was using that used WITH TIES was fetching a lot more records than I was expecting - some 7,000 more - and these were used in a Linq loop which indirectly created an Image from them. GDI handles were being hoovered up and at 10,000 goes bang!

So what I really want is a WITH TIES but with an absolute maximum just in case.

This is the original generated query:-

Code:

DECLARE @p2 bigint; SET @p2='50'

SELECT
TOP(@p2) WITH TIES [Item].[ResourceID] AS [ResourceID],
MIN([Item].[DateAdded]) AS [DateAdded]
FROM
[Item]
WHERE
( ( [Item].[DateRemoved] IS NULL))
GROUP BY
[Item].[ResourceID]
ORDER BY
MIN([Item].[DateAdded]) DESC

generated by this code if it is relevant
Code:

        public static DynamicQuery<ResourceAndItemInfoDTO> CreateRecentlyAddedQuery(int count)
        {
            var qf = new QueryFactory();

            var sortClause = ItemFields.DateAdded.SetAggregateFunction(AggregateFunction.Min) | SortOperator.Descending;
            sortClause.EmitAliasForExpressionAggregateField = false;

            return qf.Item
                .Where(ItemFields.DateRemoved == DBNull.Value)
                .OrderBy(sortClause)
                .Limit(count)
                .Select(() => new ResourceAndItemInfoDTO
                                 {
                                     ResourceID = qf.Field(nameof(ItemEntity.ResourceID)).SetExpression(new DbFunctionCall("WITH TIES {0}", new object[] { ItemFields.ResourceID })).ToValue<int>(),
                                     DateAdded = sortClause.FieldToSortCore.ToValue<DateTime?>()
                                 })
                .GroupBy(ItemFields.ResourceID);
        }


What I am looking for is a simple way to wrap the generated query with another TOP clause specifying an absolute maximum.
In SQL I would do
Code:

SELECT TOP 501 * FROM
(
// Original query here
) x


(based on a 500 maximum - if I get 501 back I know I gone past the maximum and can deal differently with that; if I get exactly 500 back there really were 500)

Is there a simple way within LLBLGen to wrap a query like that?

If not, in this case since there are only a few fields, I'll just let them all come back and just use the first 500 but I prefer a reusable, efficient solution if it is available.
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14480 posts
# Posted on: 10-Jul-2019 05:09:30.  
Quote:
So what I really want is a WITH TIES but with an absolute maximum just in case.

Isn't this just a vanilla TOP maximum, without WITH TIES?

If you have a maximum limit, then it's just simple TOP, if you need to ignore the limit and fetch Ties, then you need to use WITH TIES.
But if you want to use WITH TIES and stop at a specific limit, then it's really a TOP and no need to use WITH TIES.

I might be overlooking something though.

Another supported Option is to use WindowFunctions, and specifically, DenseRank()
Code:
Select TOP 15 * From
(
select *, DENSE_RANK() OVER (Order by UnitPrice) as Rnk from Products
) as p
Where p.Rnk >45


  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 10-Jul-2019 09:46:56.  
Its not quite the same thing.

TOP x will return a maximum number of items based on the ORDER BY

TOP x WITH TIES will return the same rows but in addition will also return all the following rows that have the same value in their ORDER BY as the xth row had.

My app shows a bookshelf showing the last 50 books added. Sometimes that 50th book's DateAdded is a date when many books were added - say 100. So using WITH TIES would include those additional books too and I modify the bookshelf caption to explain why.

In this case, I had just imported a complete library from source which didn't store the DateAdded so the DateAdded was Today for all 7,737 books. The WITH TIES then returned all these and killed the app trying to draw images of them all.

I have now changed my code to use not more than the first 500 returned and ignore the remainder but the optimizer in me would prefer to not fetch these in the first place if possible.

I am still using LLBLGen 4.2 so I don't think WindowFunction are available to me and I also effectively need two TOP values - 50 and 501 in any query so your sample would not achieve what I need.
  Top
simmotech
User



Location:

Joined on:
01-Feb-2006 15:43:00
Posted:
1006 posts
# Posted on: 10-Jul-2019 10:00:15.  
Sorted!

I already had a DataAccessAdapter partial class for customization so I added a long GlobalTop property and added a couple of lines in my CreateSelectDQ() override:
Code:
        protected override IRetrievalQuery CreateSelectDQ(QueryParameters parameters)
        {
            var result = base.CreateSelectDQ(parameters);

            // Tweaks for table hints here
            ...

            if (GlobalTop > 0)
            {
                const string ParameterName = "@GlobalTop";

                result.Command.Parameters.Add(new SqlParameter(ParameterName, GlobalTop));
                result.Command.CommandText = $"SELECT TOP({ParameterName}) * FROM\r\n(\r\n{result.Command.CommandText}\r\n) x";
            }

            return result;
        }


  Top
Pages: 1  


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

Version: 2.1.12172008 Final.