With Ties

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# 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

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# 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 Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39568
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# 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!

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 14-Sep-2018 18:18:17   

Woohoo, it works!

.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

public object Ignore;

adding to the DTO)

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

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39568
Joined: 17-Aug-2003
# 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 simple_smile

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

Heh simple_smile Sometimes you have to get rid of old photos simple_smile Ray wished he had my hair! wink

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

THere's a way I think: use this:


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 | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 15-Sep-2018 08:04:41   

Here my use case - I think its a good use of WITH TIES

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?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39568
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# 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:-


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


        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


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.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 10-Jul-2019 05:09:30   

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()

Select TOP 15 * From
(
select *, DENSE_RANK() OVER (Order by UnitPrice) as Rnk from Products
) as p
Where p.Rnk >45
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# 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.

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# 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:

        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;
        }