Filter or sort data by related documents from Include() clauses

[luboshl]

Is it possible to somehow filter or sort data by related documents? If I have aggregates/documents Order, Customer and Producst, in SQL I can write something like this:

Filtering

SELECT o.*
  FROM Orders o
 INNER JOIN Customers c on c.Id = o.CustomerId
 INNER JOIN Products p on p.Id = o.ProductId
WHERE c.City LIKE 'P%'
  AND p.Price > 100

Sorting

SELECT *
  FROM Orders o
 INNER JOIN Customers c on c.Id = o.CustomerId
 INNER JOIN Products p on p.Id = o.ProductId
ORDER BY c.City, p.Price

Sometimes I need combination of both. And I use sorting for paging. So I have a lot of data, so I sort them and then get only 10th page of 20 records.

I cannot see how to do the same with Marten. For the first sample with filter I can separately load filtered Customers, filtered Products, get their IDs and then filter Order by this IDs. If there are many records in DB (milions or more) for each document that satisfy the filter condition this meens I have to load all this IDs into memory. Then for combination of these conditions there can be only few records or none. So this is not a good way for me.

For the sorting example it is much more complicated when combined with paging.

It would be nice when there is some concept like navigation properties in Entity Framework. For the first example I would write something like this

var filteredOrders = await session.Query<Order>()
    .Where(o => o.Customer.City.StartsWith("P")
           && o.Product.Price < 100)
    .ToListAsync(ct);

"Navigation property" can be defined like foreign keys as attribute

public class Order
{
    // ...
    
    [ForeignKey(typeof(Customer))]
    public Guid CustomerId { get; set; }

    [Navigation(typeof(Customer))] // <-----------------
    public Customer Customer { get; set; } // <-----------------
}

or maybe to reference FK

public class Order
{
    // ...
    
    [ForeignKey(typeof(Customer))]
    public Guid CustomerId { get; set; }

    [Navigation(nameof(CustomerId))] // <-----------------
    public Customer Customer { get; set; } // <-----------------
}

or as configuration

_.Schema.For<Order>().Navigation<Customer>(x => x.Customer);

[ColonelBundy]

This would be great and a much appreciated qualify of life feature.
Also an overload for include to allow explicit including of related documents instead of having to output to a dictionary would be a great quality of life feature as well.

[jeremydmiller]

This is going to be miserable. At a minimum, test:

• One Where()
• Multiple Where()
• One OrderBy() on List
• Multiple OrderBy() on List
• Compiled query usage for all?
• Include to dictionary only allows a WHERE clause
• Include to list allows both sort and where
• Include to a single document doesn't allow any other kind of querying

[jeremydmiller]

I'm calling a "perfect is the enemy of the good" and only considering Where() clauses on the included documents

[nant1086]

Perhaps it'd require raw SQL to achieve joins

CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    data JSONB
);

CREATE TABLE table2 (
    id SERIAL PRIMARY KEY,
    other_data VARCHAR(50)
);

-- Insert some sample data
INSERT INTO table1 (data) VALUES ('{"foreign_key": 1}');
INSERT INTO table2 (other_data) VALUES ('Data related to foreign key 1');

-- Join the tables using the foreign key stored in the JSONB field
SELECT *
FROM table1 t1
JOIN table2 t2 ON t1.data->>'foreign_key'::INTEGER = t2.id;