XF 1.5 Where Is The Trophy List?

[Dan Allen]

We have about 53 trophies. I want to make a report that includes the names of the trophies. My research indicated that xf_trophy would be a good place to access trophy names and descriptions in mySQL, but when I looked at that data, I find the right number of records, but no trophy names or descriptions?






Two questions:
How can I access the trophy names and descriptions with sql?
Why is that information not in the trophies table or other obvious location?

Bonus Question:
Are you sure it is a good idea to not have the trophy names in the trophy table? Why?

 

[Jeremy P]

This is because the names and descriptions are stored as phrases so they can be translated. There is no single correct/canonical name or description for a trophy.

 

[Dan Allen]

Thank you Jeremy P. I can see how that facilitates work on a multilingual system now.

 

[Dan Allen]

Not that it matters, but this still is not sitting well with me.

Should we abandon php, since its commands are English-like? Why is that different from storing trophy names using the text entered by the administrator?

I would have to know more about canonolgy to understand how translating what I enter into the system into something unreadable where I look for it in the database makes this a better system. This practice has the effect of closing the database to software except XF. That defeats one of the greatest potential benefits of an application database. An external app cannot easily manipulate the database. All data manipulations are forced through the xf software. Makes integration with other systems more difficult. Breaks the rule of separating data from the app.

I want to admit that I am mostly ignorant about how XF software is put together. I am finding that almost always, when I am not comfortable with something about this software I learn later that my view was completely wrong. I am sure I am very wrong now. Xenforo is by miles the best software I have ever had a chance to work with. I should express more humility than I do and I almost always regret that. I very very much appreciate the answers that always are forthcoming in this forum and I want to thank yo you for that.

 

[Jeremy P]

Should we abandon php, since its commands are English-like? Why is that different from storing trophy names using the text entered by the administrator?

End-users don't see PHP code. And I do sometimes think about how it must be frustrating for non-native English speakers to try to learn how to code, but I'm not sure of a potential solution for that. Fortunately, there are solutions to translate text shown to end-users.

I would have to know more about canonolgy to understand how translating what I enter into the system into something unreadable where I look for it in the database makes this a better system. This practice has the effect of closing the database to software except XF. That defeats one of the greatest potential benefits of an application database. An external app cannot easily manipulate the database. All data manipulations are forced through the xf software. Makes integration with other systems more difficult. Breaks the rule of separating data from the app.

It's in the database, just in the phrase table instead of the trophy table. You can query this information directly from the database, or if you can bootstrap the XF framework in your custom code then you can do something like:

$trophy = new XenForo_Phrase('the_phrase_id');

It's a better system because it allows for translations.

 

[Dan Allen]

Thank you again, Jeremy. This is very helpful on multiple levels, and it fully resolves the questions I had when opening this thread.

Bootstrapping the XF framework into our system is one of most important development objectives our shop has right now. I wish it was the only thing I had going, so I could get onto it sooner. When that bootstraping is working for us, this is all going to make a lot more sense to me.

I took a look at the phrases table. Under the category of, "I can't believe I figured this one out," I found the key to what I am looking for in convert(title using utf8). I imagine that is a pretty basic function in your eyes but I never used it before. I found it by noticing its showed in the sql produced by phpmyadmin when I searched the database for a string. Had it not been for that, I would still be wondering where the trophy titles are. So I have this:

SELECT convert(title using utf8) , phrase_text FROM `xf_phrase` order by phrase_id desc


I guess convert(field using utf8) is important to using this database. I have not encountered the need for that before

 

[Dan Allen]

Related question:

Why are secondary_group_ids in xf_user stored as binary instead of delimited with commas or as a separate table for group memberships? How can we get from SQL a list of everyone with a certain secondary group?



Here is a guess: when these values are stored as hex, you can select on each entry in that field as if there is a separate row in a table for secondary group memberships. I don't know how to do that and I tend to think that it is not sql that does that. SQL is made for dealing with rows in tables and fields within rows. Here, we have fields within a field.

 

[Mike]

Why are secondary_group_ids in xf_user stored as binary instead of delimited with commas or as a separate table for group memberships?

They are stored as comma delimited -- its the DB browsing software (phpMyAdmin) that is choosing to display the binary values as hex. You may wish to try another that doesn't have this behavior.

Further, there is a separate table for it too: xf_user_group_relation.

 

[Dan Allen]

Back to the trophy list, I could not find a straight forward way to join the tables xf_trophy with xf_phrase. The two tables do not share any fields. Same is true for xf_trophy and xf_phrasemap.

The best way I could find for making a list of trophies using sql is select xf_trophy and then embed a lookup into xf_phrase to get the trophy title as one of the fields in the select clause, using a where clause that matches the trophy_id with a substring in the phrase title. It works for trophy title (labeled "trophy" in this example) and trophy description:

SELECT ​

trophy_id,
(SELECT p.phrase_text
FROM `xf_phrase_map` pm
inner join xf_phrase p on pm.phrase_id = p.phrase_id and pm.title=p.title
where convert(pm.title using utf8) = concat('trophy_',t.trophy_id,'_title') and pm.language_id=1) as trophy,
trophy_points,
(SELECT p.phrase_text
FROM `xf_phrase_map` pm
inner join xf_phrase p on pm.phrase_id = p.phrase_id and pm.title=p.title
where convert(pm.title using utf8) = concat('trophy_',t.trophy_id,'_description') and pm.language_id=1) as trophy_description
FROM `xf_trophy` t
order by trophy_points

Here is our trophy list next to the list from sql


If there is a simpler way I would love to see it

 

[Chris D]

This is probably the simplest way:

SELECT trophy.trophy_id, trophy.trophy_points,
    tphrase.phrase_text AS trophy_title,
    dphrase.phrase_text AS trophy_description
FROM xf_trophy AS trophy
INNER JOIN xf_phrase AS tphrase ON
    (tphrase.title = CONCAT('trophy_', trophy.trophy_id, '_title'))
INNER JOIN xf_phrase AS dphrase ON
    (dphrase.title = CONCAT('trophy_', trophy.trophy_id, '_description'))
WHERE tphrase.language_id = 0
AND dphrase.language_id = 0