AAC znote most powerful guilds

[Lurk]

I'm using [Znote AAC] Most powerful guilds (TFS 0.3/4 and 1.0) (https://otland.net/threads/znote-aac-most-powerful-guilds-tfs-0-3-4-and-1-0.209045/) (no table design) and tfs 0.4

the select query is

SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, COUNT(`g`.`name`) as `frags` FROM `killers` k LEFT JOIN `player_killers` pk ON `k`.`id` = `pk`.`kill_id` LEFT JOIN `players` p ON `pk`.`player_id` = `p`.`id` LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id` LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id` WHERE `k`.`unjustified` = 1 AND `k`.`final_hit` = 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 0, 3;

but this frags go away when players use an item that removes frags, for that and also frags look I created a new field frags_all on players
I've been trying without success to get frags_all from players instead of killers and player_killers
can anyone help me wit hthis query?

 

[Lurk]

I got to this query

SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, COUNT(`g`.`name`) as `frags` FROM  `players` p LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id` LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id` WHERE `p`.`frags_all` > 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 0, 3;

but it always returns that the guilds have 1 kill no matter how many they have because this query is just showing guilds that have at least 2 kills. Can anyone help??

 

[Dohko]

I got to this query

SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, COUNT(`g`.`name`) as `frags` FROM  `players` p LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id` LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id` WHERE `p`.`frags_all` > 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 0, 3;

but it always returns that the guilds have 1 kill no matter how many they have because this query is just showing guilds that have at least 2 kills. Can anyone help??

Hello friend, I may be mistaken, but in this section of the query : WHERE p.frags_all > 1
it validates if you have more than 1 kill, not just 1.

Try to put like WHERE p.frags_all > 0 and test again!

 

[Lurk]

same stuff :/

 

[Dohko]

same stuff :/

You deleted cache files? from \engine\cache ?

I use znote and this solution worked for me.

 

[Lurk]

yes I did and I'm executing this query on phpmyadmin

 

[Dohko]

Try this query on your phpmyadmin:

SELECT `g`.`id` AS `id`,
       `g`.`name` AS `name`,
 COUNT(`p`.`frags_all`) as `frags`

FROM  `players` p LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id` LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id`
WHERE `p`.`frags_all` > 0
GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 0, 3;

 

[Lurk]

yep, still shows only 1 frag even tho players have like 8 each

since you said this solved for you, can you share the query you're using with me?

 

[Dohko]

What happens when you remove the WHERE p.frags_all> 0 ?
the impression I have is that the tables are not well tied.

 

[Lurk]

when I remove the where is shows this

I think that it is showing the ammount of member of that guild instead of the frags? dunno

the query is clearly missing something tho

 

[Lurk]

this query is just showing the number of players in each guild that have more than 1 frag, can anyone help?

SELECT `g`.`id` AS `id`, `g`.`name` AS `name`, COUNT(`g`.`name`) as `frags` FROM  `players` p LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id` LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id` WHERE `p`.`frags_all` > 1 GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 0, 3;

same for this one

SELECT `g`.`id` AS `id`,
       `g`.`name` AS `name`,
 COUNT(`p`.`frags_all`) as `frags`

FROM  `players` p LEFT JOIN `guild_ranks` gr ON `p`.`rank_id` = `gr`.`id` LEFT JOIN `guilds` g ON `gr`.`guild_id` = `g`.`id`
WHERE `p`.`frags_all` > 0
GROUP BY `name` ORDER BY `frags` DESC, `name` ASC LIMIT 0, 3;