Suggestion for show_item.php: Which recipe components are store bought?

[Palarran]

Whenever I look at an unfamiliar recipe, one of the most important things I want to know is: which components can be bought from NPC vendors (and what is the total cost)? Normally this means clicking on every single item, and keeping a running total of the cost.

The data to determine whether an item is store bought already exists. It would be great if that information could be used in the "Full Recipe List" section! A symbol or graphic or something next to any store bought component would be a great time saver, or maybe italicizing the name of the component.

We could go even further though, and use the "NPCs sell this at" information where available. An example of how this might look on a relatively simple recipe, with my additions in bold:

Cured Shade Silk Cloak
http://www.eqtraders.com/items/show_...r=080110164000

To make Cured Shade Silk Cloak (Tailoring: Yield 1, trivial 82), Combine the following in a Deluxe Sewing Kit, Large Sewing Kit, Loom, Planar Sewing Kit, Tanaan Loom, Coldain Tanners Kit, or Collapsible Sewing Kit (5g4s2c + 4 items):

. . * Heady Paeala(2) (2g4s5c each)
. . . . o To make Heady Paeala (Brewing: Yield 1, trivial 46), Combine the following in a Brew Barrel, Tanaan Brew Barrel, Portable Drink Barrel, or Collapsible Distillery
. . . . . . + Water Flask (subcombines not shown) (vendor: 1s)
. . . . . . + Bottle (vendor: 5c)
. . . . . . + Packet of Paeala Sap(2) (vendor: 1g1s5c each)
. . * Cloak Pattern (vendor: 5s2c)
. . * Shade Silk Swatch(2) (2 items each)
. . . . o To make Shade Silk Swatch (Tailoring: Yield 1, trivial 15 (No-Fail)), Combine the following in a Small Sewing Kit, Deluxe Sewing Kit, Large Sewing Kit, Loom, Planar Sewing Kit, or Collapsible Sewing Kit
. . . . . . + Shade Silk(2)

There would be some tricky details to handle, such as what to do when there are multiple recipes for the same item, but even if it's not accurate 100% of the time I think it would be a valuable addition.

If there's anything I can do to help make this happen, please let me know. (I have plenty of database experience and have dabbled in PHP, if it helps any.)

[KyrosKrane]

Technically, it's feasible, but it's a huge chunk of work, and I'm not sure how it would affect server load. Among other things, it would be multiple additional database queries for every single item on the recipe list.

Also, we run into a problem with items that can both be made with tradeskills or bought from a vendor. For example, some items cost something like 1pp if you make them yourself (with vendor sold components), or 100pp if you buy them from the same vendor. When considering the total cost to make an item, which price do you use? And what about celestial essence? The vendor-sold price is like 2pp each, but if you get a dropped research page, the vendor price is only 1pp and change. Many tradeskillers refuse to make CE and instead buy it in the bazaar for 3-6pp each. Again, how do you price those?

We've had multiple requests for this sort of "shopping cart" pricing system, but the problem is, every time it comes up, the community can't come to a consensus on how to price everything.

[Palarran]

Without the database schema I can't know for certain, but it seems like it should be doable by expanding existing queries. (Besides, those queries need to be executed when I pull up all of the individual component pages anyway! ) Out of curiosity, what kind of database back end is used?

Oh well...what about my initial suggestion, an indicator to show which components are store bought?

[KyrosKrane]

We use a MySQL 4.x database.

OK, now you got my curiosity piqued. The vendor price for an item is trivial to get, since that's part of the item information that's retrieved already. Note, though, that this applies even to items that aren't normally vendor sold, since a player can sell it to a vendor, and another player could then buy it. The mere existence of a vendor price doesn't automatically mean the item is vendor sold.

Finding the vendor source (or whether such a source exists, which is effectively the same), though, is a whole other can of worms. I'd have to poke around a lot more, but I'm reasonably sure it would require a separate query or a subquery attached to the main query (which is functionally the same thing as far as server load goes).

I'll play around with it some and see if I can find a clever trick or something.

[Ngreth Thergn]

We use a MySQL 4.x database.

OK, now you got my curiosity piqued. The vendor price for an item is trivial to get, since that's part of the item information that's retrieved already. Note, though, that this applies even to items that aren't normally vendor sold, since a player can sell it to a vendor, and another player could then buy it. The mere existence of a vendor price doesn't automatically mean the item is vendor sold.

Finding the vendor source (or whether such a source exists, which is effectively the same), though, is a whole other can of worms. I'd have to poke around a lot more, but I'm reasonably sure it would require a separate query or a subquery attached to the main query (which is functionally the same thing as far as server load goes).

I'll play around with it some and see if I can find a clever trick or something.

Correct. Finding if a vendor sells an item is a separate query (or subquery/link... which is pretty much the same extra hit to the server), not part of the item data.

But as KyrosKrane mentioned. We cannot ever seem to get opinion on how to price things that are both vendor sold and player made. And determining if it is both is yet an additional hit (to see if there is a recipe that makes that component) We get strong opinions that both ways are correct, and we would ONLY do it one way.

[Neebat]

Showing the vendor price without mentioning the drops involved would be a serious problem.

I run into people all the time who know EQTraders exists, who go and do some research, but have no idea how tradeskills actually work. I still remember the folks looking for someone with 400 skill in research. I don't want to be the one trying to explain to them that I can't possibly sell them a Radiant Kyanite of Pure Haste for 6000pp, no matter WHAT EQTraders says.

I think vendor prices are a good idea, provided that you also list the number of dropped components along with them. So, that Radiant Kyanite would be 6500pp + 2 dropped components.

Remember that the shallowest research is done by those looking for what they should pay a tradeskiller. I want to see the highest vendor price you can calculate where ever possible. So yes, go ahead and calculate the vendor price of aqua regia, even though fewer than 1% of my aqua regia comes from a vendor. For someone thinking of making the combine, or buying the result, that gives them a threshold, you must be THIS committed to ride this ride.

As far as the server load for the run-time queries, don't do it. I could write a fairly decent batch script that would set the drop count and vendor price right on all the recipes. Running it after each set of database updates wouldn't take more than a few minutes. In fact, with a bit of cleverness, it could run only on the new and updated recipes, so it finishes very fast.

[Flehmen]

I don't know that vendor pricing is useful for Eqtraders search to calculate/display especially since vendor pricing is great but if you can find the item on a vendor. It would also be one more thing for the DB minions to track and correct etc. whereas now it's a "nice to have" item.

I'd certainly be much more interested in showing always available vendor components in itallic or some such. First thing I do after finding a new tradeskill tagged item or recipe is check what else I need to get from drops to determine its worth/cost to me.

Since now I have to click on anything that I don't reconize to check... it might save a bit of DB time and I do know that the DB minions have been keeping track of what is vendor purchasable and where.

Hehe, thanks DB minions!

[Palarran]

Unfortunately, as mentioned determining the vendor price of an item is the easy part. Determining whether it is an "always available" vendor item is the hard part, and yet is the more important piece of information.

One option might have been to add an "always available" field to the items table, which would automatically be maintained by triggers on the "NPC vendor items" table. That way server load would only increase on inserts/updates/deletes, which are presumably infrequent compared to selects. However it looks like support for triggers is not available until MySQL 5.x.

[Tudani]

Also, doing any sort of showing which items are vendor bought would only reflect current source information in the database. Sometimes it takes a while to get that information for the database.

And what about items that are limited avaliability from vendors (I'm thinking whichever ore that is)?

[KyrosKrane]

I might have come up with a way to determine whether an item is vendor sold without requiring a subquery -- basically linking the tables and using a count() on the sources. I don't know what the performance impact of an aggregate function will be, especially compared to a subquery. I'll have to research it some more later on, but I suspect the performance hit will be about as bad as a subquery.

[Druegar]

I would be quite happy just to know at a glance if something was vendor sold. Having the price, while admittedly handy, is not especially important to me.

my two cents

[Neebat]

I might have come up with a way to determine whether an item is vendor sold without requiring a subquery -- basically linking the tables and using a count() on the sources. I don't know what the performance impact of an aggregate function will be, especially compared to a subquery. I'll have to research it some more later on, but I suspect the performance hit will be about as bad as a subquery.

That still seems like a lot of work to do in real-time for information that rarely changes. It would be much quicker to add a vendor sold bit to the item table and have a script to update it after you do data entry.

[KyrosKrane]

Hmm, that's doable as well, actually. The only problem is that this breaks good database handling procedures (essentially, we're duplicating data, and there's a chance it could get out of sync).

Still, it's another option. I've got a test version that I'm using for evaluating performance. If the performance hit is pretty small, I'd much rather go with the dynamic lookup, since it's less information that has to be maintained. If the performance hit is too great, I can add a vendor-sold flag and have that update whenever we add or edit a source.

[mewkus]

I would think that it wouldn't violate DB standards if the two pieces of information were not directly tied to each other (e.g. someone could go and edit the VendorSold flag for an item). If the VendorSold flag was only updated by the script that looks at the item information (where it's obtained from) then there shouldn't be sync problems.

If you take a look at http://www.eq-manager.com/trophies.htm where I've put up the recipe listings for various trophies you can see how I've handled the vendor sold pricing stuff. It lists the prices for individual items as they're used in a combine and doesn't attempt to compute a vendor sold price for stuff that's made from a combine (unless itself is directly sold by a vendor).

If adding the vendor sold flag is acceptable and updated via script, something else that would be nice to add at the same time is another flag indicating if an item is used in combine. Then for those items that aren't used in a combine, change the link that says 'Find recipes that use this item' on the item page to something like 'This item is not used in any known combines'.

[KyrosKrane]

If adding the vendor sold flag is acceptable and updated via script, something else that would be nice to add at the same time is another flag indicating if an item is used in combine. Then for those items that aren't used in a combine, change the link that says 'Find recipes that use this item' on the item page to something like 'This item is not used in any known combines'.

This is actually a lot easier than it sounds. It doesn't even take a flag; it's just a single yes/no query for one item. The increased load should be negligible on the item pages, but I have to check with Ngreth before adding that to search pages.