Page 1 of 2
A few questions around the sort product function
Posted: Tue Jun 25, 2024 2:14 am
by Portman
Hi,
I am wanting to change some things about the sort function on my site, I am not sure if there is a simple way to change some of these in admin or something that I have missed of if I need to change the with the code...
When you sort by date added or most popular it defaults to give you the oldest, or least popular item first, I know you then click the sort by button again and it flips it, but I feel like it should default the other way aruond - IE most popular and newest display first.
I would like remove sort by Price as well.
Finally, has anyone done an addon or anything that allows you to sort/show items that are trending (IE rather than showing best sellers of all time, just display best sellers over th past month or so)... if not do you think this would be a hard thing to do? obviously there is the products_ordered filed in the database wich is used for best sellers which makes sorting easy adding a date range I assume would be quite complex.
Thanks
Re: A few questions around the sort product function
Posted: Tue Jun 25, 2024 10:18 am
by Kofod95
Portman wrote: ↑Tue Jun 25, 2024 2:14 am
When you sort by date added or most popular it defaults to give you the oldest, or least popular item first, I know you then click the sort by button again and it flips it, but I feel like it should default the other way aruond - IE most popular and newest display first.
Maybe you can use
this to force DESC to bestsellers and newest.
Portman wrote: ↑Tue Jun 25, 2024 2:14 am
I would like remove sort by Price as well.
Admin->Configuration->Product Listing->Sort Option: Price (0=disable; 1=enable)
Portman wrote: ↑Tue Jun 25, 2024 2:14 am
Finally, has anyone done an addon or anything that allows you to sort/show items that are trending (IE rather than showing best sellers of all time, just display best sellers over th past month or so)... if not do you think this would be a hard thing to do? obviously there is the products_ordered filed in the database wich is used for best sellers which makes sorting easy adding a date range I assume would be quite complex.
Haven't seen anything like that, but should be possible with the hook from the first part of my reply. Maybe you'd have to also add a join for orders_products into the query - can be done with a hook as well.
If the date-part is scary, you could just fetch the X most recent numbers of products ordered, by selecting with a DESC id and a LIMIT at 'X'. That would of course mean, that you'd rarely have exactly a month, but it could be easier to work with.
//Daniel
Re: A few questions around the sort product function
Posted: Tue Jun 25, 2024 10:21 am
by ecartz
Kofod95 wrote: ↑Tue Jun 25, 2024 10:18 am
selecting with a DESC id and a LIMIT at 'X'.
That sounds much scarier than a date range if you are sorting by something other than most recent.
Re: A few questions around the sort product function
Posted: Tue Jun 25, 2024 11:21 am
by Kofod95
ecartz wrote: ↑Tue Jun 25, 2024 10:21 am
That sounds much scarier than a date range [...]
I believe you, but I don't understand why - instead of thinking in "last month's trending" it would be "last N orders' trending" (or "last products' trending"). At least as far as I can see, the effect will be the same: Presenting the products that have been purchased the most recently. But I would not be surprised, if I was very wrong!
Anyway - couldn't let the thought slip, so played a bit with some sql, and found something very ugly, someone might be able to use. NOTE: this is only a concept!
A date-range could easily be added in a WHERE-clause, replacing the ORDER BY and LIMIT:
Code: Select all
SELECT p.*,
(SELECT SUM(ops.products_quantity)
FROM (
SELECT op.products_quantity, op.products_id
FROM orders_products op
ORDER BY op.orders_id DESC
LIMIT 300
) AS ops WHERE ops.products_id = p.products_id
) AS trending
FROM products p
ORDER BY trending DESC;
//Daniel
Re: A few questions around the sort product function
Posted: Tue Jun 25, 2024 11:42 am
by ecartz
Well, to make it work, you are using two levels of subselects because you are using sorting to filter. Meanwhile, the same using a date range:
Code: Select all
SELECT p.*,
(SELECT SUM(op.products_quantity)
FROM orders_products op INNER JOIN orders o ON o.orders_id = op.orders_id
WHERE o.date_purchased >= '2024-06-01 00:00:00' AND op.products_id = p.products_id
) AS trending
FROM products p
ORDER BY trending DESC;
Only one level of subselect. Untested.
Re: A few questions around the sort product function
Posted: Sat Jun 29, 2024 3:31 am
by Portman
thanks for the replys... I will take a bit of time to look at the trending idea in a little while.... quick question though about sort order;
@Kofod95 you directed me to;
viewtopic.php?f=28&t=1624&p=11290#p11290
which I downloaded and tried.... it seems to only allow you to control 1 list at a time... do you know how I change it so I can change 2 lists (IE date added and sales/most popular?)
I tried creating a list with commas in;
Code: Select all
$_GET['sort'] = '3a'; // <--- change sort order here !
eg '3a, 4a'
But that obviously is not how you do it
Re: A few questions around the sort product function
Posted: Sat Jun 29, 2024 6:14 am
by Kofod95
That file is for setting a new default and only one is possible as default.
Try looking at this post
viewtopic.php?p=6734#p6734
The code there shows how to insert a new sort option, and might be useful to change the current options behavior.
I haven't tried it, so I don't know for sure, but just thought it worth a shot.
//Daniel
Re: A few questions around the sort product function
Posted: Sat Jul 06, 2024 1:15 am
by Portman
Thanks @Kofod95
I spend a fair bit of time trying to get this to work with no luck. I am days away from having my site go live and I thought that this would be an easy one to fix so left it until now... Obviously it needs to be fixed as it will cause customers all sorts of confusion... unfortunatly my understanding on how to make hooks work is very limited - Ive done some stuff but more complex things are beyond me.
do you think you could give an idiot run down of how this would work in this instance...
I was able to put the the code that was in that topic into sortable_products_column.php and have a new sort link show up under the sort tab, but I was not able to control how it was displayed (ie most popular to least), it just listed it the same way that the other ones were listing but have not had any luck making it even show up if I set it up as a hook so I am obviously putting it in the wrong place.
Re: A few questions around the sort product function
Posted: Sat Jul 06, 2024 8:40 am
by 14Steve14
Portman wrote: ↑Sat Jul 06, 2024 1:15 am
Thanks @Kofod95
I spend a fair bit of time trying to get this to work with no luck. I am days away from having my site go live and I thought that this would be an easy one to fix so left it until now... Obviously it needs to be fixed as it will cause customers all sorts of confusion... unfortunatly my understanding on how to make hooks work is very limited - Ive done some stuff but more complex things are beyond me.
Contact one of the certified developers who will do a great job for you. Once you have it you will be able to learn from their coding.
Re: A few questions around the sort product function
Posted: Sun Jul 07, 2024 6:56 am
by Kofod95
Portman wrote: ↑Sat Jul 06, 2024 1:15 am
do you think you could give an idiot run down of how this would work in this instance...
Maybe, but not in the immediate future, sorry.
There are a couple of posts containing code in the thread I linked. Try using the hook that sets the default sort order as a base. Comment out the function in that file and add one of the functions pasted in the thread. Play around with it, and see if something does something close to what you need. Don't be afraid to try things out - you can just delete the file and start over, if something goes horribly wrong
//Daniel