Why is Relation#one only available on sorted relations?

[snusnu]

I’ve recently started to question the current implementation of Axiom::Relation#one. It currently must be called on a sorted relation, but i actually see no reason for this restriction. Why would i have to do something like relation.restrict(some_key: value).sort.one?

It’s purpose is to extract one tuple from a relation, and bail out if there is more than one tuple present. I don’t see any necessity for sorting here.

The only reason, it seems, is that when the relation gets compiled to sql, sorting it can prevent from loading shitloads of records by passing a LIMIT, which itself is only available on sorted relations. I’m not at all sure tho, if this safety net justifies having to sort everytime i want one tuple. After all, probably the most common usecase is when i know that i get only one result, because i restricted on a key field.

I’m having a hard time imagining (frequent) real life queries, where i want to retrieve one tuple, that is not identified by a (candidate) key. If this means that #one is potentially expensively loading shitloads of records, then so be it. I’m finding the current behavior too defensive.

Please correct me if I'm missing something. Any thoughts are highly appreciated. Thx!

[snusnu]

I should probably add that I think that there's probably a good reason to have a means of extracting the first tuple in an ordered sequence, but I don't want that to be the only means of getting at one tuple. This also seems related to the fact that DM1 exposed #get and #first.

Also, we already have a means of getting at the first tuple in an ordered sequence by doing: relation.sort_by(:something).first.

[dkubb]

@snusnu this is a good point. I think the only reason I did that is that I wanted to apply a limit to the relation, since limiting an unordered relation is nondeterministic.

However, thinking more about this, I think that #one may be a case where I need a relation subclass to represent "one" tuple, so that I can call it on an unordered relation but still generate an efficient query from it rather than potentially materializing a large relation just to test if it contains more than one tuple.

I will say that I am using the #one idea on another project, along with a #min_one and #max_one, and I find myself not using #first or #last at all, since they not only do what I want, but they provide a constraint that matches my expectations.

[snusnu]

@dkubb I'm not entirely sure i get your last point. By "since they not only do what i want ...", are you referring to #min_one and #max_one, or to #firstand #last? (fwiw, i'm assuming you're referring to #{min,max}_one because from the sound of it, it kinda implies that these somehow fail if either the min or max expectation isn't met, and thus do provide an additional constraint?

In any case, I'm happy we seem to agree that #one doesn't only make sense for sorted relations. Your idea of handling it via a special relation subclass in order to still be able to serialize it to a clever query seems good to me at first glance.

Is there anything I can do to help?

[dkubb]

I'm not entirely sure i get your last point

@snusnu my point was that lots of ORMs give people #first and #last, but once I had #one, #max_one, and #min_one I found myself using the latter more and not using the former at all. I was quite surprised since #first and #last are so common in database apis, yet I found them to be nearly useless when a better approach was available.

Your idea of handling it via a special relation subclass in order to still be able to serialize it to a clever query seems good to me at first glance.

It turns out I didn't need to create a special node, and it couldn't really work since #one returns a tuple, rather than a relation. However, the solution I came up with is much simpler and should work with existing SQL serialization code.

[snusnu]

@dkubb can you elaborate a bit more on #{min,max}_one? I think I have some thoughts on how they would behave, but I'd like to hear how you implemented (and used) them.

[snusnu]

@dkubb anything i can do to help merging the axiom-do-adapter fixes?