[resolved] how to save the positions of the elements ?

[cvbdev]

Hi all,

 

I am developing a module in the backoffice.

I want to sort the records with the "position" button

 

 

 

How does prestashop to save changes to the database, when I click on the "position" button ? A example ? A tutorial ?

 

 

Thank'you very much !!

 

 

ps : escuse my english !

 

 

Edited October 25, 2013 by cvbdev (see edit history)

[PascalVG]

The function called is in /controllers/admin/AdminProductsController.php

Look for functions with position in there. They for example then call function processPosition(), which in turn calls a function in, for example, Category.php: funtion updatePosition():

	public function updatePosition($way, $position)
	{
		$id = Context::getContext()->shop->id;
		$id_shop = $id ? $id: Configuration::get('PS_SHOP_DEFAULT');
		if (!$res = Db::getInstance()->executeS('
			SELECT cp.`id_category`, cs.`position`, cp.`id_parent`
			FROM `'._DB_PREFIX_.'category` cp
			LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
				ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
			WHERE cp.`id_parent` = '.(int)$this->id_parent.'
			ORDER BY cs.`position` ASC'
		))
			return false;

		foreach ($res as $category)
			if ((int)$category['id_category'] == (int)$this->id)
				$moved_category = $category;

		if (!isset($moved_category) || !isset($position))
			return false;
		// < and > statements rather than BETWEEN operator
		// since BETWEEN is treated differently according to databases
		$result = (Db::getInstance()->execute('
			UPDATE `'._DB_PREFIX_.'category_shop` cs
			LEFT JOIN `'._DB_PREFIX_.'category` c
				ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
			SET cs.`position`= cs.`position` '.($way ? '- 1' : '+ 1').'
			WHERE cs.`position`
			'.($way
				? '> '.(int)$moved_category['position'].' AND cs.`position` <= '.(int)$position
				: '< '.(int)$moved_category['position'].' AND cs.`position` >= '.(int)$position).'
			AND c.`id_parent`='.(int)$moved_category['id_parent'])
		&& Db::getInstance()->execute('
			UPDATE `'._DB_PREFIX_.'category_shop` cs
			LEFT JOIN `'._DB_PREFIX_.'category` c
				ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
			SET cs.`position` = '.(int)$position.'
			WHERE c.`id_parent` = '.(int)$moved_category['id_parent'].'
			AND c.`id_category`='.(int)$moved_category['id_category']));
		Hook::exec('actionCategoryUpdate');
		return $result;
	}

Hope thishelps,

pascal

[cvbdev]

thank you very much !  

[PascalVG]

Hi Cvb,

Glad it was useful!

 

if this was enough info, please mark your topic as solved (See my footer text how to)

 

Thanks,

pascal

[cvbdev]

Hi Cvb,

Glad it was useful!

 

if this was enough info, please mark your topic as solved (See my footer text how to)

 

Thanks,

pascal

 

 

Hi Pascal,

 

the solution does not work....

I leave it open as long as I have not found!

 

I am trying to understand the source code

 

 

Do you know a tutorial on the subject?

 

 

Thank you very much !

++

[PascalVG]

No, don't know of any tutorial, example. What exactly do you try to achieve??

[cvbdev]

No, don't know of any tutorial, example. What exactly do you try to achieve??

.

 

I try to change the position of the elements in a list (record database positions) in a module that I developed in the backoffice (see picture) I can not find an example on the web.

 

And the source code of prestashop is not easy to understand.

 

 

 

 

Thank's  

++

[PascalVG]

Let's try to break the example up in parts:

		$id = Context::getContext()->shop->id;
		$id_shop = $id ? $id: Configuration::get('PS_SHOP_DEFAULT');
		if (!$res = Db::getInstance()->executeS('
			SELECT cp.`id_category`, cs.`position`, cp.`id_parent`
			FROM `'._DB_PREFIX_.'category` cp
			LEFT JOIN `'._DB_PREFIX_.'category_shop` cs
				ON (cp.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
			WHERE cp.`id_parent` = '.(int)$this->id_parent.'
			ORDER BY cs.`position` ASC'
		))
			return false;

What happens:

1) First the get the 'current shop'. As you might know, PrestAshop has a multishop option, where you can maintain more shops in one administration (back office), so they check which shop you're dealing with.

2) Then get the categories (only these fields: category id, position, id of parent category) that has the same parent as the 'current category' (so, all siblings). The current one is found through the '$this element). The current category is the one that we moved. The found categories list (rows) gets ordered by their (old) position.

3) if anything goes wrong in getting these categories, return an error ("return false")

		foreach ($res as $category)
			if ((int)$category['id_category'] == (int)$this->id)
				$moved_category = $category;

They walk through the found rows, and refer to each row as  a '$category'. When they find the current category (the moved one) in the list, they keep a reference to this element, called $moved_element. So it finds the old position info of the moved category.

		if (!isset($moved_category) || !isset($position))
			return false;

If, for any reason, they couldn't find the current category in the list, OR if the new position is not given correctly when calling this function, return an error

		$result = (Db::getInstance()->execute('
			UPDATE `'._DB_PREFIX_.'category_shop` cs
			LEFT JOIN `'._DB_PREFIX_.'category` c
				ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
			SET cs.`position`= cs.`position` '.($way ? '- 1' : '+ 1').'
			WHERE cs.`position`
			'.($way
				? '> '.(int)$moved_category['position'].' AND cs.`position` <= '.(int)$position
				: '< '.(int)$moved_category['position'].' AND cs.`position` >= '.(int)$position).'
			AND c.`id_parent`='.(int)$moved_category['id_parent'])

Here they update fields in some of the record (rows) in category_shop:

update the field `position`, by subtracting or adding 1 to the current value, dependent on the '$way' (the current category moved: up= $way has value 0/down= $way has value 1)

 

What happens is, when the current category moved DOWN, all categories which used to be below the current category (i.e. which used to have a higher position number) but NOW are above the current category has to move UP

example:

 

Before 

CatA 1

CatB 2

CatC 3

CatD 4

CatE 5

 

and we moved CatB two positions DOWN:

 

After:

CatA 1 (no change)

CatC 3 (has to move UP 1)

CatD 4 (Has to move UP 1)

CatB 2 (our moved category, will have to get value of $position = 4)

CatE 5 (No change)

 

$position = 4

$way = down (1)

 

so

             SET cs.`position`= cs.`position` '.(1 ? '- 1' : '+ 1').'

means: the position value will be decreased by 1

(N.B> They use the short way of if else statement here: if ($way) then '- 1' else ' '+ 1'  here $way is 1, so '- 1' is returned, resulting in: cs.`position` = cs.`position` - 1 in the query)

 

but not for all rows, but only the ones that used to be below, but now are above the current category (CatB, CatC). This is indicated by the WHERE clause:

            WHERE cs.`position`
            '.($way
                ? '> '.(int)$moved_category['position'].' AND cs.`position` <= '.(int)$position
                : '< '.(int)$moved_category['position'].' AND cs.`position` >= '.(int)$position).'

 

As $way = 1, the bold code is added to the query, which says: all categories which has a position number > old position of our moved one (2) but must have a position number <= than the new position (4), so only the categories with position 3 (CatC) or 4 (CatD)

So those two categories will decrease their position number by one. (CatC from 3-> 2, CatD from 4->3)

 

What's left: Change the position of our moved category itself:

		&& Db::getInstance()->execute('
			UPDATE `'._DB_PREFIX_.'category_shop` cs
			LEFT JOIN `'._DB_PREFIX_.'category` c
				ON (c.`id_category` = cs.`id_category` AND cs.`id_shop` = '.(int)$id_shop.')
			SET cs.`position` = '.(int)$position.'
			WHERE c.`id_parent` = '.(int)$moved_category['id_parent'].'
			AND c.`id_category`='.(int)$moved_category['id_category']));

Set the position of the "$moved_category" (as you remember, this was a reference to the row which hold the id and old position of the moved category)

So it Update's the value of cs.`position` with the value of $position, for those rows that have the id of the moved one (i.e. the moved category itself)

 

Finalise with a call of any function that may have linked to the ActionCategoryUpdate hook. (This can be any function that needs to do some updating (can be a screen update, another database update, or whatever) after a category has changed). Read more about hooks in the developer guide if interested

 

In the end, return the result of the updates (if anything went wrong during the update, this result is returned, otherwise the confirmation that all went OK is returned)

 

 

That's about it. Not sure if this helps, or that you're still confused. If you still have problems, maybe show some 'context code' you have where you want to add the repositioning. Maybe we can then help you out further.

 

pascal

[cvbdev]

Thank'you very much !

It's works !

[PascalVG]

Glad to hear that!

Will you publish the module, or is it only useful for your own project?

 

Pascal