0

I have let says 3 products

  1. P1 with SKU "xyz_1" and price "10"
  2. P2 with SKU "xyz_2" and price "20"
  3. P3 with SKU "xyz_3" and price "50"

I want a query in magento to get sku xyz with minimum price i.e xyz_1 only.

I want something like this select ,Regex('SKU','myexpression') as SKU to remove last part from i.e "_" part from sku and then apply filter on product collection to get product with minimum price.

Any Ideas how to handle that with Magento?

Toto
  • 89,455
  • 62
  • 89
  • 125
M.Bilal
  • 1
  • 3

1 Answers1

1

You could this regex: ^xyz. This will match any string which starts with xyz and ignore what follows.

That being said, you would need to modify your query to sort the results by price since regular expressions do not cater for numerical comparisons (less then, greater than, etc).

EDIT: As per your question, you could adapt some code from here into this:

$write = Mage::getSingleton('core/resource')->getConnection('core_write');

// now $write is an instance of Zend_Db_Adapter_Abstract
$readresult=$write->query("SELECT SUBSTRING(sku, 0, INSTR(sku, '_')) AS SKU FROM <your table> WHERE sku REGEXP '^<your sku>' ORDER BY price asc LIMIT 1;");

SELECT INSTR('foobarbar', 'bar');

npinti
  • 51,780
  • 5
  • 72
  • 96
  • "xyz" is just an example I have many skus with different first part not the same – M.Bilal Jun 10 '14 at 07:25
  • second my question is how to apply regular expression in magento??Thanks – M.Bilal Jun 10 '14 at 07:26
  • this is great but can you please tell me Is there a way to get only first part to sku i.e "xyz" as the above solution will give to complete sku I want to show only first part on front end?? Thanks for this – M.Bilal Jun 10 '14 at 08:50
  • @M.Bilal: I have amended my answer. I do not have a MySQL instance on this machine so I cannot test, but it should give you the gist of what you need to do. – npinti Jun 10 '14 at 09:27