Showing results for 
Search instead for 
Did you mean: 

Sort a magento collection by altered attribute

Sort a magento collection by altered attribute

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.