GameServer.Database Queries

[Loki]

Code: Select all

DataObject[] testing = GameServer.Database.SelectObjects<DOLCharacters>("SOME Query HERE")

I know there is an error in that piece of code, can somebody perhaps break it down or point out what I've missed out please

For example, I would like 'testing' to return a player who has played the most hours, so I can reference it later in script.


Hope that makes sense?


Thanks

[LugusSkye]

I don't use inline code for SQL but if you want the query, this will return what you are asking:
Doing custom SQL inside DOL is a bit inefficient, imo. Open Workbench or other client you are using and run this:

SELECT D.Name, D.PlayedTime
FROM dolcharacters D
WHERE D.PlayedTime = (SELECT MAX(PlayedTime) as MaxPlayedTime FROM dolcharacters);

[Loki]

Code: Select all

var test = GameServer.Database.SelectObjects<DOLCharacters>("ORDER BY `PlayedTime`", new QueryParameter()).FirstOrDefault();

I needed it inside DOL, I can handle outside the core OK. I was'nt sure on the syntax for DOL, but I've been given the above (TY Graveen)

Hopefully I can now make some custom stuff... If not for a testing server! then for others and to gain knowledge

Have a great weekend all

J

[Graveen]

Original SQL query would be :

Code: Select all

SELECT * FROM dolcharacters ORDER BY `PlayedTime` LIMIT 1;

There is no WHERE clause in this request. But every SelectObjects<T>() start after the WHERE clause (so perhaps correct arg1 is "1 ORDER BY `PlayedTime` LIMIT 1").
2nd argument is blank because you don't need any parameter.

.FirstOrDefault() is the LIMIT 1 part. It is not needed, you can grab ALL objects and just use the myResult[0] to grab the result, or query all and use LINQ to order (but theses are a BAD design decision, there could be many many records - this is why i still suggest LIMIT x).

[Loki]

I used the Limit 1 in case the result was 2 players with the same hours, not likely but you never know.

Further to this why cant I reference DOLCharacters_ID using this code

Code: Select all

if (res = base.Interact(player)) { TurnTo(player, 5000); var getID = GameServer.Database.SelectObjects<DOLCharacters>("RealmPoints > 0 ORDER BY RealmPoints DESC LIMIT 1", new QueryParameter()); foreach (var t in getID) { t. //here I cant access DOLCHARACTERS_ID } }

[Loki]

Man I feel dumb.. I can use the Name as its unique

[Graveen]

Look at DOLDatabase\Tables\DOLCharacters.cs.

Every public field can be accessed, but you won't find ID: DOLCharacter inherits from DataObject. Now go to DataObject.cs, and you can see ObjectID which is the field you need. But, you should not need it, except in very special cases.

[Loki]

I cant use name because then I cant get the players inventory based on the highest RR on server. Name is not present in Inventory, only ownerID which is same as DOLCharacter_ID isnt it?

or am I making this more complicated than it needs to be?

[Loki]

I can see just how much other useful stuff I can access using DOLCharacter more clearly now Ty G

[Graveen]

Code: Select all

var dbChar = GameServer.Database.SelectObjects<DOLCharacters>("RealmPoints > 0 ORDER BY RealmPoints DESC LIMIT 1", new QueryParameter()).FirstOrDefault; GamePlayer dummyPlayer = new GamePlayer(null, dbChar); var inventory = dummyPlayer.Inventory; dummyPlayer.Dispose();

should work, altough the null should be tested in new GamePlayer()

[Loki]

As you said ... Once I get to Gameplayer it opens so much more.


Thank you G... for the spoon feeding. Ive wrote countless winforms apps and thought I could stand my ground but when taken out of a comfort zone I know shit lol.

[Leodagan]

Just one little thing, try to use "something" in QueryParameter()

Code: Select all

var dbChar = GameServer.Database.SelectObjects<DOLCharacters>("RealmPoints > @RPLimit ORDER BY RealmPoints DESC LIMIT 1", new QueryParameter("@RPLimit", 0)).FirstOrDefault()

This is not really needed here for performances, but using parametrized queries can help database sql compiler to get an execution plan from cache...

And it can be easier to reuse this query with different where clause...

I know it's not the brightest database API, but it was a simple step stone instead of trying to completely implement LINQ-to-DB or Entity Framework...

[Leodagan]

If you fear having duplicated "highest RP" players you can use 2 queries...

Performance will not be great but it prevent handling bigger result sets :

Code: Select all

var maxRP = GameServer.Database.SelectObjects<DOLCharacters>("ORDER BY RealmPoints DESC LIMIT 1", new QueryParameter()).Select(dolchar => dolchar.RealmPoints).FirstOrDefault(); var highestPlayers = GameServer.Database.SelectObjects<DOLCharacters>("RealmPoints = @RPMax", new QueryParameter("@RPMax", maxRP));

Remember this can match "0 RP" as being the Max RP, use an if-clause or a Try/Catch using LINQ "First()" to trigger an exception

[Loki]

Code: Select all

var playerac = GameServer.Database.SelectObject<DOLCharacters>("RealmPoints > 0 ORDER BY RealmPoints DESC LIMIT 1"); var inv = GameServer.Database.SelectObject<InventoryItem>(playerac.ObjectId = "OwnerID");

this is not returning what I want

By that I mean I thought it should be matching the DOLCharacter_ID with the Inventory_ID

[Loki]

Got It

Code: Select all

var playerac = GameServer.Database.SelectObject<DOLCharacters>("RealmPoints > 0 ORDER BY RealmPoints DESC LIMIT 1"); var inv = GameServer.Database.SelectObject<InventoryItem>("OwnerID = '" + playerac.ObjectId + "'");