Comments (3)
Additionally, when in a join, distinct
also breaks the orderBy
when ordered by a field in a table that is not used in distinct
argument:
await db
.select(lf.fn.distinct(table.id))
.from(table, secondTable)
.where(secondTable.parentId.eq(table.id))
.orderBy(secondTable.id, lf.Order.ASC)
.limit(10)
.exec();
This will yield the same result as when ordering by created
in the main table.
from lovefield.
By not working I mean it gives me a result that doesn't change if I modify the order to DESC or ASC. It just returns a static result as if the date created is zero or the same number for all rows.
Could you post the code that registers the db schema? Can you verify that
created
is registered as lf.Type.DATE_TIME?- when you populate these fields you are actually adding JS Date objects and not something else?
Removing the distinct function fixes the problem.
That sounds pretty interesting. Do you need to use distinct() though, if table.id is already unique (I assume?). Either way, does an equivalent query in SQLlite returns the results you expect?
from lovefield.
Well, I just dug a little deeper into this, and here is what I have found:
-
If there is a distinct function involved, sorting can only be done by an indexed field. Fields without index just won't work for sorting. This needs to be documented if it is actually intentional.
-
If there is a distinct function involved in a multi-table query, sorting only works on the primary key of the table involved in the distinct function and won't work for any other field, indexed or not.
https://codepen.io/s-flhti/pen/mdEebgP
I am wondering what would happen when it is sorted by an aggregated function on a groupBy table1.parentId
result set in a multi-table query since there is no index for the aggregated function. Like this:
await db
.select(table.id)
.from(table, table2)
.where(table2.parentId.eq(table.id))
.groupBy(table2.parentId)
.orderBy(lf.fn.min(table2.integerField), lf.Order.ASC)
.limit(10)
.exec();
But that's another discussion.
EDIT
created
is registered as lf.Type.DATE_TIME?
yes
when you populate these fields you are actually adding JS Date objects and not something else?
yes, I have a Typescript wrapper on top of LoveFields and it is strongly typed to the interfaces I have, so I can't really make a mistake like this. Unless I explicitly cast the value to any
. I wrote this wrapper to save me especially from these sorts of mistakes.
Do you need to use distinct() though, if table.id is already unique (I assume?).
Well, it is complicated. I don't really need to use distinct on table1.id
. I just did that as part of the example posting here. In reality, I have a dynamic list of filters that might happen on table1
, or table2
, or both. And since each row in table1
has multiple rows in table2
, I need to use distinct on table2.parentId
to get a clean and unique list of items. I do also have sorting options that might happen on table1
or table2
so I need to keep that in mind too. I was hoping to be able to do all of these filters and sorting in one query along with pagination (skip
, limit
) but apparently, it is impossible for now. So I have decided to do it in 3 different queries, and do the sorting and pagination later on the JS side. Lovefield is fast enough that I can afford to do so with around 12k rows in both tables in less than 100ms. So no worries.
Either way, does an equivalent query in SQLlite returns the results you expect?
SELECT DISTINCT(t1.id) FROM table1 as t1 ORDER BY t1.title DESC
works as expected and needs no index to return the valid resultset.
SELECT DISTINCT(t1.id) FROM table1 as t1 INNER JOIN table2 as t2 ON t2.parentid = t1.id ORDER BY t2.created DESC
also works as expected.
So both these two limitations are apparently only applicable to Lovefield and SQLite has no such rules and limitations regarding distinct and indexes, or distinct and inner join.
Tested with https://sqliteonline.com/ with this data:
sqlite.zip
from lovefield.
Related Issues (20)
- Please consider releasing a new version to npm that requires js-yaml >= 3.13.1 HOT 2
- lovefield.min.js does not support aliases for aggregate fields... HOT 3
- nullable string column and sort by this column results in rows with null omitted?! HOT 3
- Silly Question: Lovefield and Apps Script HOT 1
- Error when updating record - Error 501 HOT 3
- Error when trying to use persistentIndex(true) HOT 1
- Help needed to build predicates dynamically HOT 2
- Dead link in docs HOT 1
- Select after delete and insert in transaction HOT 2
- Cannot insert ArrayBuffer correctly ! HOT 1
- Will lovefield get long term support? HOT 1
- lovefields/demo/scrum broken HOT 2
- [Feature Request] Faster `String` Match HOT 4
- indexed nullable DATE_TIME field causes error on .insert() unless specified HOT 1
- Where clause "not in" with an empty array always returns false
- Where's the manual? HOT 1
- dealing with nested queries HOT 1
- Migrate from Travis CI
- Is this project dead? HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from lovefield.