Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Priority Mod with Multiple Priorities???

Quote Reply
Priority Mod with Multiple Priorities???
I have added the priority mod to a site I'm building but now it turns out I'll have at least three different ways that sites can improve their priority rating in the system.. I want to make sure I haven't missed anything in the logic of what I'm doing so bear with me while I explain.

What I need is a total of the three priority improving fields to use to sort the results by when building the pages each night. Each of these has a field in the table and will be updated in different ways. What I am going to do is adjust the sort order along these lines.. If I go to nph-build.cgi in sub build_category_pages, I see that the list of links from each category is retrieved here..

$get_links = $LINKDB->prepare (" SELECT * FROM Links WHERE CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");

What I'm doing is adding something like this

$get_links = $LINKDB->prepare (" SELECT *,Priority1+Priority2+Priority3 as Priority FROM Links WHERE CategoryID = ? ORDER BY $LINKS{build_sort_order_category} LIMIT 1000 ");

Of course in Links.pm I have adjusted this
$LINKS{build_sort_order_category} = "Priority DESC,isNew,Title"

I haven't tested this on the actual build yet, but if I run the query from the command line, it seems to work fine when I mock up some data. I figure this way if I add any other priority improving methods, I should think I could just change this one line.... I realize I probably need to change either the field called Priority or the results of my calculated fields so I don't create other problems..

My question is, Is there anywhere else that I need to worry about this. I really don't use page.cgi to create dynamic pages.. I'm not sure yet if I want to implement this in search results. I'm just wondering if there might be anywhere else that I should be using the mysql calculated priority total.



Quote Reply
Re: Priority Mod with Multiple Priorities??? In reply to
I take it something like:

Priority1: 3
Priority2: 4
Priority3: 3


Priority1: 0
Priority2: 0
Priority3: 10

would be equal? Are you taking this into account on the sort order? It seems you would need to sort them individually, or sort by a total of the three, which I'm not sure is possible.

I can see the value of having multiple priority possibilities, but it seems it would be much simpler and cleaner to combine them into one and adjust system to accomodate the individual needs of the three.


Quote Reply
Re: Priority Mod with Multiple Priorities??? In reply to
You probably won't like this answer, but rather than have 3 priority fields you are trying to manage, and then make sure very SQL call is updated, you should put them into different fields, and leave the priority field as the sum total.

There are several ways to do this.

The _best_ way, and most compatible, is to have your update/add/modify routines process the field input, and use a bitmask (1,2,4) so that a (3) in the priorty was level 1 and 2, a '2' was level 2, and '5' was level 1 and 4, etc.

This would allow sorting on the priority field, and only the ouput and input routines have to really even care _what_ that priority is. Make sense?

The other way, if the priority levels are truly independent, and you want them independently managed by other scripts, services, etc, is to set up a system like:


Then, make the 'Priority' field the sum of those 3 fields.

Again, since this is only on input/output of a record, the actual engine code would never have to care that the 'priority' field is really a calculated total.

Either of these options is _much_ _much_ more portable and maintainable than what you are trying to do.

I don't think MySQL supports calculated fields directly, so you have to do it during the input/output routines.

FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: Priority Mod with Multiple Priorities??? In reply to
In my case at least, the two would be equal and I want to sort by the total, not individual elements of it. Here's why. I have one of the priorities as a base priority which every link will be assigned a default value, say 1 on a scale of 1 to 5. In the other priorities, they will pretty much either be 0 or 1, so you get a 1 point bump in priority for certain things. For example, if they link back to me, the link back priority becomes 1, and the total becomes base priority +1 or in this case 2. If they do other things, the other fields get increased to 1 instead of 0, which ultimately increases the overall total and their ranking in the results order. If I do this and sort the results on descending priority, I can add as many different priority categories as I want.

Also, by keeping multiple fields for each of the priorities, I think it's cleaner in terms of updating on a regular basis. Using the linkback priority as an example, in order to track that in the total priority value, I would need another field to track whether they were linking back or not. With my design, if the value is 1, they are, if it's 0 they aren't. If I used just one total field, it becomes pretty complicated as to who is doing what in regards to these priorities. At least it seems so to me.

Sorting by the total of the three is really simple, that's what the code above does because it creates a new field result in the select statement, which you can then use in the build_sort_order and let mysql do the calculation on the fly when building the pages.

Quote Reply
Re: Priority Mod with Multiple Priorities??? In reply to
Actually pugdog, maybe I wasn't very clear in my description, but I am pretty sure what I'm doing matches the second way you described. I am using three separate individual priority fields in the table. I do also have a Priority field (which should be the total). I am letting the Select statement in nph-build.cgi do the total Priority calculation on the fly and assign the result of the calculation to the field name Priority without having to update the database. I think you are right about MySQL not supporting calculated fields directly which is why I structed the Select query the way I did.

If you notice in the second $get_links above (my change)

....SELECT *,Priority1+Priority2+Priority3 as Priority FROM Links ....

I have added the calculation after the SELECT * for the three new Priority fields and set that to a field name I can use in the sort order. Now I can just edit the sort order to sort on Priority and it's done. I have other programs running independent of the basic links stuff that determines what the values of the various priority fields are and update the database as necessary, which are all just basically run out of cron every day.

I thought this would be incredibly portable because if I add another way of improving the rating, say sites listed can pay me $10/month to improve your rating, all I need to do is...
1) add the field to the table
2) edit the select statement to add the new field to the calculated total
and it's done... except of course for the independent program which would be necessary to track who has paid and such. :) which would be necessary no matter how the calculations get structured.