Display parts parameter per category and quick search in categories

[ECCOsea]

Hi! I have a small lab, so i want to share my experience with you, may me it will be helpfully. There is 2 very necessary features i think:

1:
I want to suggest to a customize field display, sort and search per category. (not only system)
For example, for capacitors add: Dielectric, Capacitance, Voltage, and case. (Screeen1)

2:
Quick search and filter in present parameters elements in category. (like same voltage, capacitance, dielectric and etc)

[RinatTRIN]

Yes, this is a very necessary function for selecting the display of columns with parameters in groups of components, as well as searching and sorting by them.

[Skycheetah]

Yes, this feature is much needed

[clytle374]

I just ran across this while trying to find caps with a X1,Y1 rating. At least I think this is related.

[Treeed]

I, too, think these features are much needed. I believe there's a couple of considerations to be taken about this:

Which columns and filters should be shown?

There's two basic principles here:

Union: All parameters that are available on any shown parts are shown

This would mean that when looking at the outermost category, all parameters are available for selection. The further in you go, the more parameters are excluded.
This option is nice because it works basically without configuration. Any parameters that might be relevant are always shown. Downside is that it bloats the UI and overloads some of the frameworks (DataTables gets very slow with lots of columns)

Intersection: Only parameters that are available on all shown parts are shown

This would mean that in the outermost category only ubiquitous parameters like packaging are shown and more specialized parameters are shown as you get to more specialized categories.
The upside here is that it's nice and tidy and no unnecessary data gets processed. The downside is, that, while the initial configuration could be autogenerated, it would realistically require some setting up where the user decides which parameters should apply to a category even though the data may be missing on some parts and where a parameter really is only relevant to the innermost category.

Naturally there is a spectrum in between these options where a certain percentage of parts need to have a parameter for it to be shown, but generally it is a tradeoff between showing more, requiring less upfront work and showing less and providing more sophisticated configuration tools.

Parameter Configurations / Parameter Types / Parameter Templates

It might be useful to have a unified parameter configuration entity. Generally, I believe a certain type of parameter is specific to a category and should be inherited by all child categories. These entities would therefore be owned by categories and could set a number of things related to that parameter like:

• Which fields of the parameter should be available as columns
• Which fields of the parameter should be shown as quick filters
• Whether the parameter should be proposed on new parts
• Whether the parameter should be mandatory on new parts
• Which fields of the parameter should be specified
• What base unit the parameter should have

This raises the question whether such a parameter configuration should be a direct property of a parameter. As such, a parameter could rely on it for certain calculations and values like the name. I initially thought this would be a good idea, however I realized that if such a parameter configuration is specific to a category, a category contains a subcategory, a subcategory contains parts and a part contains parameters, then any change in that chain could lead to an inconsistent state where a parameter is either not linked to a parameter config that it should be linked to or it is linked to a parameter config that is not relevant to it anymore.

Alternatively, parameter configs could be loaded adhoc, just by querying by name and category. That would mean for instance renaming a parameter type would be much harder, but overall it's probably more practical.

An option which I don't like is making parameter configs generally global. Such a system would mean the user would have to choose the type from an endless list of options, most of which aren't relevant, and some of which might look very similar.

Of course such an entity doesn't need to exist. Parameters can also all just exist on their own and all suggestions and displayed elements are just based on how many parameters of a certain name with certain properties already exist.

Sorting

When showing parameters in columns they should be sortable, and by that I mean not show 1 megaohm before 2 kiloohms. That requires calculating a magnitude relative to some base unit at some point in time.

In the current system all sorting is done by the database which means every parameter needs some scaling value cached as part of its record. If parameter configs were linked to parameters, this would provide a convenient way to provide a mechanism for providing a base unit and calculating the scale of the specific unit. Knowing the base unit would make such a mechanism both more robust and more flexible.
If the sorting is done at a later stage it would also be conceivable to redo the calculation on the server or even client side each time.
Generally I like the cached variant since it allows potentially doing more complex unit conversions which would be too costly to do on every request.

Client vs. Serverside options

Currently which columns are shown and the order is saved on the client side. I think with different configurations for each category these settings should be saved globally on the server side, since it is too much effort to redo the setup on every client. Changes should not be autosaved either.

Client vs. Serverside filtering

Clientside filtering has some advantages on small datasets: The initial loading time is a bit slower, but after that filtering and sorting is extremely snappy and filtering, independent of the data structure on the server is quite easy to implement for all columns. Unfortunately it is not scalable for larger datasets and quickly exceeds memory limits.
Serverside processing doesn't increase initial loading time or memory consumption much, but there's a delay after every selection before the options update and every filtered column requires its own custom queries.

Relation between columns and searchPanes

Datatables considers searchPanes something only a column has. I think there is no reason a column must have a searchPane or vice versa. This consideration somewhat affects whether the searchPanes controller should be integrated with the existing datatables controller (e.g. as a subclass) or be separate.

What I have got so far

I have started implementing some of that functionality to figure things out and got the basics working, but there's no pretty or documented code yet.
I built a mechanism that automatically adds columns for parameters that are defined on a certain percentage of the parts that are shown. Currently it shows all parameters that are present on more than 50% of the shown parts. Each field of a parameter (min, typ, max, text) is considered like an individual parameter.
I implemented some basic unit parsing that calculates a scaling factor for the unit given on a parameter and stores it as a property of the parameter. The scaling factor gets calculated whenever the unit is set. For initialization there is a script which goes through all parameters and parses the units.
I found datatables has a nice extension called searchPanes which basically does what I would expect of quick filters. Unfortunately it's really focused on client side processing and server side seems to have been a bit of an afterthought. I optimized the behavior and performance of the client for server side processing a bit (at the cost of some flexibility).
I believe for quick filters to be useful they need what datatables calls cascading panes. Essentially options that aren't valid anymore need to be excluded as soon as a filter is applied, which also means the options in the searchpanes update with every selection. I implemented the server side processing to retrieve the entries for the searchPanes and send them to the client, along with the appropriately filtered parts.
I disabled the client side storage of the columns for now since it doesn't like the number of columns constantly changing.
I optimized the queries a bit, mostly to speed up hydration.

What this does not do:

• Code isn't pretty or documented yet
• There is no configuration - columns have to be enabled each time the page is reloaded, changing anything requires modifying things in the code
• Panes should probably be collapsible sideways and the whole box with the panes should be collapsible like the other filters
• Updating after a selection is slower than I'd like. On smaller datasets (100s of parts) about equal time is used on client and server side. This could likely be improved by using a webserver that reuses it's processes for multiple requests, and some more optimization on client and server side. For larger databases (10'000s of parts) the query time quickly becomes limiting. I think I've optimized the queries as well as I can while sticking to DQL. There's one instance where a result could be reused instead of queried each time and otherwise indices and a different database would probably help (using SQLite right now to get a lower limit on performance).
• There's only panes for parameters, not for any other columns for now.
• The current configuration slaps searchPanes on any datatable, so unrelated tables like the log table now has some useless (empty) panes above them.
• I have built the server side processing of the search panes independent from the datatables, but some client side adaption is needed to allow a searchPane to exist without a column
• EDIT: The search bar is broken, quick search works, but pressing enter returns an error.

The sillier bugs are just there because I didn't bother fixing them for this demonstration. I'll polish that if/when we agree on the basic architecture of the feature.

I hope the configuration situation will somehow become easier with the integration of the settings bundle.

Here is a screenshot of what this currently looks like:

You can try it out here: https://github.com/Treeed/Part-DB-server/tree/serverside_filter
Please note this is based on my personal fork with a bunch of other unrelated modifications in it

What you need to do:

• Have a development environment where you are happy to experiment and not concerned about data loss
• Check out my branch
• run ´php bin/console doctrine:migrations:migrate´ to get the scale parameter
• run ´php bin/console partdb:init-parameter-scale´ to initialise the scale parameter on existing parts
• run ´yarn install´ and ´yarn build´
• maybe run ´php bin/console cache:clear´, depending on what you're caching

@jbtronics I would be happy to hear what you think

I have also made a client side filtering version with some optimization on the datatables cascading calculation before realizing that the poor scaling would mean it can't become a part of the main partdb:
https://github.com/Treeed/Part-DB-server/tree/clientside_filter

[ECCOsea]

Wow! Coolest thing! Hope it will be added in original repository and online demo. Thank you!!!

[Treeed]

Oh, another option that I initially dismissed for not being scalable: It would take only marginally more processing to export the part ids with the searchPane options. Sending the ids along with the options would mean the processing of the searchPanes could be done entirely clientside without much delay and only the update of the main datatable would require another roundtrip. The query for the part data would become much simpler too, since the filtering would already be done.
When I initially thought about this I figured the amount of data to send would be way too much, but with 10'000 parts, 16 panes, UTF-8 encoding and a single comma as separator it's "only" 10000*(4+1)161byte=800kbyte which isn't great, but not terrible either.
This would be sort of a middle course between pure client and pure server side.

[ECCOsea]

In my vision, that scenario of sending 10,000 pieces of information about parts is impossible. I think, PartDB is more suitable for hobby use. I use a lot of SMD capacitors and resistors, but it still hasn’t exceeded 1,000 unique part numbers per category. Currently, I’m using a custom MySQL database with 550 items loaded on a single page, and it works very fast—less than a second. When I apply server-side filtering, it works instantly. Anyway, your solution looks very good, and I hope it will be configurable in the future. For example, to configure the main table by deciding which parameters should be displayed and which filters should be applicable for each category.