XF 2.2 mysql query in a template

[Lee]

I want to run this code:

<xf:foreach loop="$xf.app.db.fetchAll('SELECT custom_fields FROM xf_user_profile WHERE user_id = ?', [$quotedUser])" value="$row">
                {$row.custom_fields}
</xf:foreach>

In a template, which works fine. The result however is an array of data (serialized?). Is it possible to use an individual part of the Array within the template...?

 

[digitalpoint]

Well first of all, let's just forget what a bad idea (imo) putting SQL in a template is...

But if you must, you really want to be querying the xf_user_field_value table. The xf_user_profile.custom_fields column is just a cache of those values, and since you want to do a query directly rather than use entities, you lose the "magic" that entities give you (for example being able to pull individual values from a JSON string).

 

[Lee]

Well first of all, let's just forget what a bad idea (imo) putting SQL in a template is...

But if you must, you really want to be querying the xf_user_field_value table. The xf_user_profile.custom_fields column is just a cache of those values, and since you want to do a query directly rather than use entities, you lose the "magic" that entities give you (for example being able to pull individual values from a JSON string).

Why is it such a bad idea in your opinion? I’m not looking to do anything dangerous or unsafe.

 

[digitalpoint]

Why is it such a bad idea in your opinion? I’m not looking to do anything dangerous or unsafe.

It's not that it's dangerous (XenForo does a good job of protecting itself from things like malicious user input), it's just bad general design. If you search XenForo templates, you'll notice that there is literally zero templates that have the string, app.db (it's not because XenForo devs didn't know they could access $xf in templates). In general, it's just a good idea to keep presentation (templates) separate from code. Imagine if all the queries XenForo did were actually in templates and what a nightmare that becomes trying to keep track of/manage those if you wanted to change something (changing something in a Repository that gets applied to all the places using it is just going to be a lot easier to manage).

 

[Lee]

It's not that it's dangerous (XenForo does a good job of protecting itself from things like malicious user input), it's just bad general design. If you search XenForo templates, you'll notice that there is literally zero templates that have the string, app.db (it's not because XenForo devs didn't know they could access $xf in templates). In general, it's just a good idea to keep presentation (templates) separate from code. Imagine if all the queries XenForo did were actually in templates and what a nightmare that becomes trying to keep track of/manage those if you wanted to change something (changing something in a Repository that gets applied to all the places using it is just going to be a lot easier to manage).

Right I see. So if I may pick your brains slightly further...

If I wanted to replace the name of a quoted user with a value from their custom profile fields, what approach would you take?

 

[digitalpoint]

Well, I don't know what template you are dealing with, but if the template already has the entity for that user, you can access their custom fields via {$user.Profile.custom_fields}. That will be an array of their custom fields because of entity "magic".

If you don't have the user's entity available in that template, depending on what you are doing and how you are doing it, I'd probably just go the route of getting the user entity available to that template (then everything related to the user, including custom fields) becomes available.

 

[Lee]

Well, I don't know what template you are dealing with, but if the template already has the entity for that user, you can access their custom fields via {$user.Profile.custom_fields}. That will be an array of their custom fields because of entity "magic".

If you don't have the user's entity available in that template, depending on what you are doing and how you are doing it, I'd probably just go the route of getting the user entity available to that template (then everything related to the user, including custom fields) becomes available.

Fair. The template is:

bb_code_tag_quote

 

[digitalpoint]

Personally, I'd probably extend the XF/BbCode/Renderer/Html class, add the user's entity I'm interested in to the getRenderedQuote() method, and then it's available in the template. It's slightly more work initially, but keeps your code and presentation separate which will save you time/headache down the road.

 

[Lee]

Personally, I'd probably extend the XF/BbCode/Renderer/Html class, add the user's entity I'm interested in to the getRenderedQuote() method, and then it's available in the template. It's slightly more work initially, but keeps your code and presentation separate which will save you time/headache down the road.

Thanks @digitalpoint - I have approached it this way and all works brilliantly.

 

[digitalpoint]

Thanks @digitalpoint - I have approached it this way and all works brilliantly.

Nice! And ya... as I mentioned before, even though it's a tiny bit more work initially, it will scale better and save yourself time in the future. One change like that isn't a big deal, but imagine if you had 100 of those individually small things that added queries to various templates and say maybe a future version of XenForo has some database schema change... and trying to track down/remember which templates have which queries to fix things. That's another reason to use Finders and Entities whenever you can vs. direct queries (you are letting XenForo's internals handle the underlying queries, which will still work even if the underlying query needs to change because of a database scheme change in the future).

It's not always possible to avoid raw SQL, but it's a good idea to use Finders/Entities when possible.