New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

More column filter options in browser #1463

Open
tlhackque opened this Issue Jul 5, 2018 · 14 comments

Comments

Projects
None yet
4 participants
@tlhackque
Copy link

tlhackque commented Jul 5, 2018

Describe the new feature

It would be nice to have some options besides 'like' when filtering in the browser.

Similar to Excel's "blanks", "non-blanks" in Autofiltered columns. (I'm not a big fan of "top 10")

I'd add "NULL" & "not NULL" as you can't type anything to represent them, and perhaps "Blob" and "not Blob" for the same reason. These are helpful when looking for anomalies.

It's possible to get at NULL values for a single column by sorting & scanning visually, but that doesn't scale.

It's possible to drop into SQL to do this, but that result table can't be filtered (except by writing more SQL). Hmmm - would it be possible to have the browser take the results of my query and load them into the filterable window? e.g. create a temporary table from the results of a select in the "execute SQL' tab, and put that into your nice filterable table. Seems like that would be straightforward for viewing (my primary use case), but harder to send modifications back to the real tables.

It also would be useful to add a simple JOIN column (e.g. viewing orders, would be nice to add 'join order.customerid = customers.id')

Additional info

Please answer these questions before submitting your feature request.

Is your feature request related to an issue? Please include the issue number.

No

Does this feature exist in another product or project? Please provide a link.

Excel.

Do you have a screenshot? Please add screenshots to help explain your idea.

Nope. Just run excel autofilter on any spreadsheet and look at the dropdowns. [All] [Blanks] [Non-Blanks] [Custom]

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Jul 5, 2018

Some extra possibilities are already available:

    https://github.com/sqlitebrowser/sqlitebrowser/wiki/Using-the-Filters

That being said, in theory you should be able to save the results from the Execute SQL tab as a View. From there, you should be able to apply filtering to that.

It's not exactly a temporary table, but might be "good enough" for the moment?

@tlhackque

This comment has been minimized.

Copy link
Author

tlhackque commented Jul 5, 2018

Those aren't exactly obvious on first approach - how about a help->'filters' window with that information? And / or a right-click in the filter cell that offers help? I did try both early on.

Still doesn't give me '!Null' (or any variation that I tried'). or =isNULL

theory you should be able to save the results from the Execute SQL tab as a View

Try saving as a view with a read-only database :-)

[Hint - it's grayed-out]. I'm not going to try exporting 100K records to a CSV (which isn't.)

Which seems to indicate that to do what I want, you'd need a temporary DB in this case.

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Jul 5, 2018

how about a help->'filters' window with that information?
And / or a right-click in the filter cell that offers help?

Good thinking. We've just been doing the wiki thing so far as that seemed good enough.

I wonder if clickable links work in tooptip text? That might be a useful way to add "Further info: url" type of things too.

Try saving as a view with a read-only database :-)

Oops. Yeah. Good point. 😉

Temp database does sound like it's worth trying as a workaround for now then.

@justinclift justinclift changed the title More colum filter options in browser More column filter options in browser Jul 5, 2018

@tlhackque

This comment has been minimized.

Copy link
Author

tlhackque commented Jul 5, 2018

"Further info: url"

I'd rather not see this sort of help require a browser; it should be built-in. It's pretty small, though big for a tooltip. A floating (non-modal) dialog box would work.

Browsers are slow - and if you mean to assume on-line - I get a lot done at 30,000 ft. Shouldn't need an internet connection for a couple of paragraphs. Plus, they'll get translated if they're in the product, and less likely to be (or timely) if on a wiki page.

One easy clue to add would be to have the "filter" cue be "filter expression" - which at least will cause wonder about what the expression syntax might be...

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Jul 5, 2018

Good point. We probably need to think about doing a "real" help system then. 😄

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Jul 5, 2018

Have you checked the "What's this" information for the filter box? It is not extensive, but it should give hints about the possible filter options. In my latest commits I've tried to add useful tips for the more hidden features, but I always have the feeling, that users are not used to read these "What's this" tips 😄

@tlhackque

This comment has been minimized.

Copy link
Author

tlhackque commented Jul 5, 2018

I did not notice that feature. Sorry :-(

It's good information. It could be more prominent.

Suggest elevating it to an icon on the toolbar and making the (same) context help available on a right click in a field. Keeping the help near the task usually wins.

@mgrojo mgrojo self-assigned this Jul 7, 2018

mgrojo added a commit that referenced this issue Jul 8, 2018

Issue #1463: give assistance to users in using the filters
Added contextual menus in filter line box and cells for assisting in using
filters and for discovering existing filters.

The menu in the filter line adds "What's This" option and helper options
that add the operator and a selected placeholder (?) for easy editing.
Special options for NULL and empty strings are also added. Those work
directly.

The menu in the cells works as the current "Use as Filter" but for
different operators. In this case, only the range operator is incomplete.

Currently, not all the operators make sense for strings because we take
them as LIKE filters. At least the Not Equal filter (<>) should work, but
the other would also make sense. This will be addressed in future commits.
@n-arakawa

This comment has been minimized.

Copy link

n-arakawa commented Jul 8, 2018

Still doesn't give me '!Null' (or any variation that I tried'). or =isNULL

In addition to this, I hope to have 'not like' filter.

@tlhackque

This comment has been minimized.

Copy link
Author

tlhackque commented Jul 8, 2018

In addition to this, I hope to have 'not like' filter.

Agree - I looked for this too. I expected ! to do the obvious - & insert a NOT in the SQL expression - e.g.
!foo => not( "field" like '%foo%')
!=bar => not( "field" eq 'bar' ) -- or "field" <> 'bar'

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Jul 9, 2018

I added a commit for improving the visibility of the different filter options. See my comment in bbe2e33 for explanatioon. It should be already in the nightly.

mgrojo added a commit that referenced this issue Jul 12, 2018

Support for operator filters for strings
This adds support for all operators when applied to string values.
Formerly they were always treated as a Containing (LIKE) operator, but
after the implementation of bbe2e33 the
lack of these operators were more visible and, at least, the omission of
<> seems a bug. But the omission of the others may also be a limitation.

For easing the return to the old behaviour, the Containing filter option
now takes into account the necessity of escaping the value when it starts
by one of the operator characters.

For operators applied to strings the behaviour is documented by Sqlite3
here: https://www.sqlite.org/datatype3.html#collating_sequences

See related issue #1463.
@tlhackque

This comment has been minimized.

Copy link
Author

tlhackque commented Aug 23, 2018

Nicely done!

@mgrojo mgrojo added the enhancement label Sep 2, 2018

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Sep 2, 2018

Recollecting, so far we have implemented:

  • Visibility and helper menus of existing filters

The pending enhancements mentioned in this issue are these kind of filters:

  • Is BLOB, Not is BLOB
  • Not like 'X'

@mgrojo mgrojo referenced this issue Sep 3, 2018

Closed

Regexp filters #1522

@tlhackque

This comment has been minimized.

Copy link
Author

tlhackque commented Sep 6, 2018

The pending enhancements mentioned in this issue are these kind of filters:
...
And (from .0):

It also would be useful to add a simple JOIN column (e.g. viewing orders, would be nice to add 'join order.customerid = customers.id')

It's not a filter (the idea was "add column by creating an implicit temp view") - Should another issue be opened so it's not lost?

And (.5, justin) might also want to be split out:

We probably need to think about doing a "real" help system then.

Thanks again for all the work.

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Sep 6, 2018

@tlhackque:

It also would be useful to add a simple JOIN column (e.g. viewing orders, would be nice to add 'join order.customerid = customers.id')

It's not a filter (the idea was "add column by creating an implicit temp view") - Should another issue be opened so it's not lost?

Yes, please, could you open a new issue and explain the idea in more detail?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment