Implementing a SQL query - SubQuery/ TOP (1)/ ORDER BY

Posts   
 
    
Vince06
User
Posts: 1
Joined: 08-May-2019
# Posted on: 08-May-2019 19:56:56   

Hello!

I am trying to implement a SQL query within LLBLGen Pro, and having difficulty. To set the background:

  • My database has Conversations.
  • Each Conversation belongs to a Company.
  • Each Conversation is made up of multiple Messages.

I have the following SQL query that takes in a CompanyPK and returns information about the most recent Message in each Conversation for that Company:

SELECT c.[ConversationPk], c.[Subject], c.[IsUrgent], m.[Sender], m.[Body]
FROM MessagingApi.[Conversation] c
INNER JOIN MessagingApi.[Message] m on c.ConversationPk = m.ConversationPk
INNER JOIN MessagingApi.[Company] p on c.CompanyPk = p.CompanyPk
WHERE c.CompanyPk = @CompanyPk
AND m.MessagePk IN (
SELECT TOP (1) [MessagePk]
from MessagingApi.[Message] m2
where m2.ConversationPk = c.ConversationPk
ORDER by SentOnUtc DESC
)

This is the LLBLGen Pro Code that I have written so far:


var qf = new QueryFactory();

    var q = qf.Conversation
        .From(QueryTarget
            .LeftJoin(qf.Message)
            .On(ConversationFields.ConversationPk ==
                MessageFields.ConversationPk)
            .InnerJoin(qf.Company).On(ConversationFields.CompanyPk == CompanyFields.CompanyPk)
        )
        .Select(() => new MessageSummaryDto
        {
            ConversationPk = ConversationFields.ConversationPk.ToValue<Guid>(),
            Subject = ConversationFields.Subject.ToValue<string>(),
            IsUrgent = ConversationFields.IsUrgent.ToValue<bool>(),
            Sender = MessageFields.Sender.ToValue<string>(),
            Body = MessageFields.Body.ToValue<string>(),
        })
        .Where(ConversationFields.CompanyPk == CompanyPk)
        .AndWhere(MessageFields.QueueType == queueType)
        
        //This is where I am trying to implement the subquery
        .AndWhere(MessageFields.MessagePk.In(
            qf.Conversation
                .From(QueryTarget
                .LeftJoin(qf.Message).On(ConversationFields.ConversationPk == MessageFields.ConversationPk)
                )
            .Select(() => MessageFields.MessagePk.ToValue<Guid>())
            .OrderBy(MessageFields.SentOnUtc.Descending()).Limit(1)
            ));


        using (var adapter = AdapterFactory.CreateAdapter(_connectionString, _environment))
        {
            var result = await adapter.FetchQueryAsync<MessageSummaryDto>(q);
            return result;
        }

I understand that this doesn't work because the .Limit(1) is limiting it to a single entry, not the newest entry for each Conversation. But how do I write that part correctly?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-May-2019 11:07:20   

When you enable tracing to see the generated query, what does it look like? At first glance the query looks OK.

You could also use 'Equal()' instead of In() btw, as you obtain just 1 value simple_smile

Frans Bouma | Lead developer LLBLGen Pro