I need to sort a collection by an attribute, taking into account only the numbers and decimal places of this attribute, ignoring the letters. I don't think there is anyway to use addAttributeToSort here. On version Magento 2.3.
The collection is of catalog_product_entity. The attribute I need to sort by is in catalog_product_entity_text. The collection is filtered by another attribute already, no further filtering needed, just sort.
The attribute and value are in catalog_product_entity_text; let's call the attribute test_group and it can have values similar to this: CA350, AB100, B225, BB100.5 etc..
How can I sort the collection so I end up with the collection in the order of AB100, BB100.5, B225, CA350?
I've tried $collection->getItems(), sort these items, delete items in collection, push these sorted items to collection. This results in very slow performance, so I would like to utilize mysql to handle the sorting, like addAttributeToSort does. I also do not want to overwrite the existing attribute in the collection.
I'm still learning mysql and haven't had any success with my attempts, but I'm thinking something like
$collection->getSelect()->joinLeft(array( 'catalog_product_entity_text'=>"catalog_product_entity_text"), 'main_table.test_group_no_char=catalog_product_entity_text.value')
but with the test_group_no_char not containing letters.
