How do I sort ps_store to show on site?

[rialni]

Hi,

 

I'm almost done with a new ps, but just at the finish line I'm running into an issue that I didn't think about from the beginning.

 

If you look here: http://byboysen.dk/prestashop/index.php?controller=stores you see a list of stores.

 

I would like this list to be sorted, first by country, second by post code. I'm trying to do it by phpmyadmin, but it's not reflected on the site how I sort the table directly in the database.

 

Does anyone know how I can do this?

 

Do you also know if it's possible for me to add a heading for each country section, when the above has been done? - if not it doesn't matter, the above is more essential for the shop 

 

Thanks in advance

[math_php]

Hi Rialni,

 

In storesController.php you have

 

if (Tools::getValue('all') == 1)
        {
            $stores = Db::getInstance()->executeS('
            SELECT s.*, cl.name country, st.iso_code state
            FROM '._DB_PREFIX_.'store s
            '.Shop::addSqlAssociation('store', 's').'
            LEFT JOIN '._DB_PREFIX_.'country_lang cl ON (cl.id_country = s.id_country)
            LEFT JOIN '._DB_PREFIX_.'state st ON (st.id_state = s.id_state)
            WHERE s.active = 1 AND cl.id_lang = '.(int)$this->context->language->id);
        }

 

Modify it to

 

if (Tools::getValue('all') == 1)
        {
            $stores = Db::getInstance()->executeS('
            SELECT s.*, cl.name country, st.iso_code state
            FROM '._DB_PREFIX_.'store s
            '.Shop::addSqlAssociation('store', 's').'
            LEFT JOIN '._DB_PREFIX_.'country_lang cl ON (cl.id_country = s.id_country)
            LEFT JOIN '._DB_PREFIX_.'state st ON (st.id_state = s.id_state)
            WHERE s.active = 1 AND cl.id_lang = '.(int)$this->context->language->id).' ORDER BY s.name' ;
        }

 

You can do this or make the same in a controller storesController.php override.

Best regards.

[rialni]

Thank you so much for your reply Unfortunately, it doesn't sort the list on the site.

 

I replaced the lines you suggested, in the file storescontroller.php. Anywhere else I need to make changes, to see it work?

[math_php]

Oops, yes quick fix without testing.

 

Then the good way is still in the same file but in protected function assignStoresSimplified() :

 

 

WHERE s.active = 1 AND cl.id_lang = '.(int)$this->context->language->id);

 

have to be modified to

 

WHERE s.active = 1 AND cl.id_lang = '.(int)$this->context->language->id.' ORDER BY s.name');

[PascalVG]

Believe you wanted to order not by name but country/postcode?

 

Then change to

 .' ORDER BY country, s.postcode'

 

My 2 cents

pascal

[math_php]

Ok Pascal,

 

You are right, I missed the point.

 

I know RTFM but I dunno if there is a RTFQ which would apply for me on this case

 

My fault

 

I checked and yes RTFQ exist, then yes it looks like a life standard

Edited October 16, 2013 by math_php (see edit history)

[rialni]

I'm sorry to say, I still can't get it to work. I feel quite stupid right now. Why can't I Work this out?

 

This is what my code looks like right now:

if (Tools::getValue('all') == 1)        
		{   
		$stores = Db::getInstance()->executeS('            
		SELECT s.*, cl.name country, st.iso_code state            
		FROM '._DB_PREFIX_.'store s '.Shop::addSqlAssociation('store', 's').'            
		LEFT JOIN '._DB_PREFIX_.'country_lang cl ON (cl.id_country = s.id_country)            
		LEFT JOIN '._DB_PREFIX_.'state st ON (st.id_state = s.id_state) 
		WHERE s.active = 1 AND cl.id_lang = '.(int)$this->context->language->id.' ORDER BY s.country, s.postcode');						        }

and thank you both for looking at this with me, by the way I really appreciate it

[math_php]

It is ok,

Undo what you have done.

 

Search assignStoresSimplified()

 

 

$stores = Db::getInstance()->executeS('
        SELECT s.*, cl.name country, st.iso_code state
        FROM '._DB_PREFIX_.'store s
        '.Shop::addSqlAssociation('store', 's').'
        LEFT JOIN '._DB_PREFIX_.'country_lang cl ON (cl.id_country = s.id_country)
        LEFT JOIN '._DB_PREFIX_.'state st ON (st.id_state = s.id_state)
        WHERE s.active = 1 AND cl.id_lang = '.(int)$this->context->language->id);

 

Then modify

 

$stores = Db::getInstance()->executeS('
        SELECT s.*, cl.name country, st.iso_code state
        FROM '._DB_PREFIX_.'store s
        '.Shop::addSqlAssociation('store', 's').'
        LEFT JOIN '._DB_PREFIX_.'country_lang cl ON (cl.id_country = s.id_country)
        LEFT JOIN '._DB_PREFIX_.'state st ON (st.id_state = s.id_state)
        WHERE s.active = 1 AND cl.id_lang = '.(int)$this->context->language->id  .' ORDER BY country, s.postcode');

[rialni]

IT WORKS!!!!! :) :) I just love coding, when things work out I actually think I tried this last version myself before, but I must have missed something. Now, it works.

 

Thank you, thank you, thank you

[PascalVG]

I'm sorry to say, I still can't get it to work. I feel quite stupid right now. Why can't I Work this out?

 

This is what my code looks like right now:

 

if (Tools::getValue('all') == 1)        
		{   
		$stores = Db::getInstance()->executeS('            
		SELECT s.*, cl.name country, st.iso_code state            
		FROM '._DB_PREFIX_.'store s '.Shop::addSqlAssociation('store', 's').'            
		LEFT JOIN '._DB_PREFIX_.'country_lang cl ON (cl.id_country = s.id_country)            
		LEFT JOIN '._DB_PREFIX_.'state st ON (st.id_state = s.id_state) 
		WHERE s.active = 1 AND cl.id_lang = '.(int)$this->context->language->id.' ORDER BY s.country, s.postcode');						        }

and thank you both for looking at this with me, by the way I really appreciate it

 

Ohhh, you were so close! See, only you had s.country instead of country :-)

 

Nice that it works!!

 

Happy selling,

pascal