Comments (10)
Hi,
Everything should be sorted by name: if it's not then it's a bug. An option
for sort order is a good idea though, I'll look to add this, probably using
how 'ls' handles this as a guide.
Thanks,
Paul
On 20 Dec 2014 14:03, "0ion9" [email protected] wrote:
For some things, getting an 'unsorted' list (which is effectively,
actually sorted by how recently a tag was applied on that.
I've run some tests and it looks like you can get sorting-by-recency
(oldest -> newest tagging) just by leaving out the 'order by' clause from
the SQL.--recency would be a decent option name, I guess.
—
Reply to this email directly or view it on GitHub
#12.
from tmsu.
Yes, if I was unclear, I meant that sorting by recency is much better than alphabetic for some uses.
I manually did this kind of query recently, because I needed it:
select F.name from file_tag as FT join file as F on F.id=file_id where tag_id = (select id from tag where name = 'have_drawn')
And suggested this because it is clearly possible with no changes to the database necessary. At no point did TMSU provide output that wasn't alphabetically sorted.
from tmsu.
Cool, no problem. I'll look to add a sort option. I've already a todo item
for numerical sorting do it would make sense to do this at the same time.
Thanks,
Paul
On 20 Dec 2014 23:53, "0ion9" [email protected] wrote:
Yes, if I was unclear, I meant that sorting by recency is much better than
alphabetic for some uses.
I manually did this kind of query recently, because I needed it:select F.name from file_tag as FT join file as F on F.id=file_id where
tag_id = (select id from tag where name = 'have_drawn')And suggested this because it is clearly possible with no changes to the
database necessary. At no point did TMSU provide output that wasn't
alphabetically sorted.—
Reply to this email directly or view it on GitHub
#12 (comment).
from tmsu.
I don't like the idea of functionality that relies on undefined behaviour of the underlying database: https://www.sqlite.org/lang_select.html#orderby. However, that is easily addressed by ordering by the (normally hidden) 'rowid' column which is, effectively, the insert order.
Not sure how the recency thing would work though: it could be implement this using the rowid column on the file table, which would result in files by ordered by when they were first tagged. To sort them by when they were last tagged would be considerably more difficult. The easiest way would be to first retrieve the set of files that match the query and then perform a second query to work out the last update order. For example, if the files are identified as numbers 1, 5, 8 and 12 then a query such as the following could get the order:
SELECT file_id
FROM file_tag
WHERE file_id IN (1, 5, 8, 12)
GROUP BY file_id
ORDER by max(rowid) DESC
However this would be the last time these files had a tag applied, not necessarily when the tags that are in the query were applied. Would this suffice?
from tmsu.
The two-pass approach seems to obtain the correct results, with, I guess, reasonable time? As long as its no worse than say 2x the time of the default alphabetical sort order, I'm not personally fussed.
Sorting by time of first tagging.. isn't something I would personally make much use of. There are probably applications for it though.
Thanks for the rowid hint; I used it to construct a single-pass query structure that works for AND queries only:
SELECT DISTINCT F.directory, F.name FROM file_tag AS FT
JOIN file AS F ON F.id=FT.file_id
JOIN file_tag AS FT2 ON F.id=FT2.file_id
WHERE FT.tag_id = (SELECT id FROM tag WHERE name = "foo")
AND FT2.tag_id = (SELECT id FROM tag WHERE name = "bar")
ORDER BY max(FT.rowid, FT2.rowid) DESC LIMIT 10;
(I don't know enough about SQL to handle NOT properly in this context, but I usually use just AND anyway, so this is fine for a temporary workaround.)
EDIT: It just occurred to me -- don't you have the file_tag rowid available for selection when you are doing the main query anyway? Could you avoid the second query by selecting this and doing the sorting outside of SQL, or not?
from tmsu.
The file_tag rowid is not directly available as I'm doing an 'in' check, rather than getting values from file_tag. Leave it with me and I'll see what I can do. There might be something I can do with the SQL otherwise I'll just have to run the results through the aforementioned sort.
from tmsu.
The --sort option on 'files' should now cover this. Please let me know if there's anything I've missed otherwise I'll close this.
from tmsu.
Changes went in first in b88a6fc.
from tmsu.
Ah, realised the requirement for 'recency' is not update, not tagging so the --sort=id
is probably not cutting it for you.
from tmsu.
Yeah, I tried all --sort
options awhile ago, none of them achieve this effect so I'm still using an sqlite3 command to perform this.
Ah, realised the requirement for 'recency' is not update, not tagging
I guess that there is a mistake in this sentence, so I'll just restate what I'm talking about for clarity's sake:
"Sort by on-disk order within the file_tag table (not the file table). Functionally, sorts by 'how recently was applied to a given file'"
And in SQL + bash:
sqlite3 $DB 'SELECT F.directory || "/" || F.name FROM file_tag AS T JOIN file AS F ON T.file_id=F.id WHERE tag_id = (SELECT id FROM tag WHERE name = "have_drawn") ORDER BY T.rowid;' | tail -30
(to get the 30 files that were most recently tagged have_drawn, with the absolute most recent at the end. The 'ORDER BY T.rowid' is just for explicitness' sake -- omitting it achieves the same result set in the same order.)
from tmsu.
Related Issues (20)
- Pls consider making this tool available on conda HOT 1
- tmsu-fs-mv may overwrites files it cannot update internally HOT 4
- key-value tags will not work with rename HOT 5
- `tmsu tags --name=never` seems to imply `-1` HOT 1
- How can I delete values? HOT 4
- Need python example on how to import the tags for a file from a sqlite database into TMSU HOT 1
- Confusing status command behavior HOT 1
- ...
- Duplicated tags in file system HOT 1
- Repair files by automatically locating files based on checksum? HOT 2
- List all the tags with values HOT 11
- small contribution: sc-im interface for changing tmsu tags HOT 2
- Question: what does the error message mean? HOT 3
- --name=never removes lines HOT 2
- database locked occasionally
- VFS can be very slow
- Feature Request in VFS: Listing files outside of the `files` folder and hiding `queries` folder
- Cannot install TMSU HOT 1
- Package available in Void Linux
- too many SQL variables - transport endpoint is not connected when too many tag values exist
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 tmsu.