Sort parts correctly per value

[Tropaion]

Is your feature request related to a problem? Please describe. It would be nice if there is an ordering note where the prefixes of values is considerd (nF, uF, ...). Currently it's messed up, and it can be really annoying when searching something in the list.

Describe the solution you'd like A sorting value which recognizes prefixes

Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.

Additional context Add any other context or screenshots about the feature request here.

[jbtronics]

I am afraid that this almost impossible to implement with the new table system. The sorting of the fields is done by the database backend as its the only instance which knows all items of the table (Part-DB only receives only the items of the current page from the database).

However MySQL (and SQLite) does not really support natural sorting on string fields, especially not with the SI prefixes and mixed field names.

So i dont think that will be possible in the near future. Maybe the part parameters system could be utilized for that (there the part values are saved numerically and could be ordered)

[Tropaion]

Okay, pity, thank you. I hoped it would be possible since it was implemented in the old part db version

[Tropaion]

@jbtronics Another thing I discovered, if there are parts with values like "1", "1.1", "1.2", it always shows in this order: "1.1", "1.2", "1". Is it possible to show "1" first?

EDIT: I'm not good with MySQL, but the internet shows workaround solutions for natural sorting, would this be possible? https://stackoverflow.com/questions/153633/natural-sort-in-mysql/12257917#12257917

[RobThree]

I think the better solution is to keep an extra column in the DB that stores the (computed?) "normalized" value so that, for example, 1K1 is stored as 1100 and 1pF is stored as 1.0E-12. You could sort on that column then, but it will only make sense within the same unit ofcourse.

I'm not sure how easy it would be to have Part-DB (or the RDBMS) figure out what the normalized value would be from the part name (that would mean you'd have to parse many variations like "Resistor 1K1" and "R1K1" and "1K1Ω" etc. etc.). I think the safer option is to add a 'numeric value' field of some kind and have the user explicitly enter the 'normalized value'.

[Tropaion]

@RobThree That would also be a possibility, but I would prefer if it works automatically, since it would be many hours of work considering how many passive parts I have. But I really hope this feature comes back in some way, it's the one I miss the most.

[RobThree]

since it would be many hours of work considering how many passive parts I have

Same for me; I've been entering parts all day today, am currently maybe at 2% (new to this, so learning as I go, rather slow so I expect to get faster at this). But I'm afraid that would be the best / safest option. But we'll let @jbtronics decide.

[jbtronics]

The general problem is that MySQL databases do not really offer a possibility to perform a natural sort natively, and fetching every row and do the sort in PHP is a performance killer. Recent versions of MariaDB has the NATURAL_SORT_KEY function, which seems to make the sorting more natural. However im not sure, if you could make it compatible with the SI prefixes (and how performant that would be, as you would need to modify the sort key and that makes index sorting impossible). Also this requires a pretty recent MariaDB (basically MariaDB 10.11 from 2023 as most distros ship only the most recent LTS version) and MySQL (or Sqlite) do not support this function at all. In the moment it does not matter if you use MariaDB or MySQL or which version as long as its not too old and I am not sure if i want to introduce a difference between the different database systems here.

So having a column, which contains a normalized version is most likely the most easiest and most portable version. In principle it could be possible to automatically generate this value automatically from the name. However its not easy, as you can have many different formats and its not very reasonable to order parts like that across different categories: Comparing 10k Ohm with the normalized value 10000, with a 10µF with normalized value 0,00001 does not make much sense.

[Tropaion]

Comparing 10k Ohm with the normalized value 10000, with a 10µF with normalized value 0,00001 does not make much sense.

I agree but I don't think that's a big problem, since these values are separated by category, at least in my installation.

[jbtronics]

With the latest commits Part-DB supports natural sort on postgres and MariaDB 10.7+ (and via slow emulation on other platforms)

[Tropaion]

@jbtronics That's great, thanks! I didn't expect any update about this, I thought it wasn't possible.