1

If I hardwire a created_by clause in a joomla sql custom field, the query works as it should (for this specific userid the clause is, and created_by = 305):

SELECT id AS value, title AS text
FROM #__content
where catid = 8 and created_by = 305

What I want to do is allow the created_by = ??? clause to be dynamic (substituted with JFactory::getUser()->id;

I figured if I make the custom SQL like this:

SELECT id AS value, title AS text
FROM #__content
where catid = 8 and created_by = %%userid%%

I could/should be able to replace the token %%userid%% with a dynamic number before the query is used.

I've tried bending the existing custom sql field - just to see if it will work in principle, but I'm not having too much luck.

In plugins/fields/sql/tmpl/sql.php I have:

$query = $fieldParams->get('query', '');

if (substr_count($value,"%%userid%%") > 0){
    $userid = JFactory::getUser()->id;
    str_replace("%%userid%%", $userid, $query);
}

// Run the query with a having condition because it supports aliases
$db->setQuery($query . ' having value in (' . trim($condition, ',') . ')');

Ideally, I would create a brand new custom field, and be happy to donate it to the world!

I'm not even having much fun in trying to debug this!

@mickmacusa and @Thiago, when my sql statement has the token %%userid%%, the str_replace("%%userid%%", $userid, $query); simply does not happen.

I've tried simplifying the code so that it's NOT EVEN looking for my token - it's supposed to just do it:

$db->setQuery($query . ' having value in (' . trim($condition, ',') . ')');

So right now, I think that I've not understood how the supplied sql field actually works.


Absolutely fails... The Query ignores absolutely anything I try...

Even this :

$query = $fieldParams->get('query', '');

$db->setQuery('SELECT mince as value ,potatoes_oinions_peas as text FROM #__marketstall WHERE catid = 8');

Which ought to cause a MAJOR fit when the query is being executed, since it is a totally nonsense query...

But what actually happens is it executes the ORIGINAL sql statement go figure.

So there's 3 things:

Either I'm editing the wrong bit of code.

Something is being MASSIVELY cached.

Or it seems to me as though the SQL statement that is 'defined' by the developer is getting stored somewhere else, and the programmatic change of the statement via the plugin code is being utterly ignored, somehow.

I just don't know enough about how these are supposed to work. I really didn't think it would be this difficult.

6
  • Hi @mickmackusa, Thanks for the editing clarification. Shall try better but erk, this is all a bit modern ;-) This code fails too - do NOT understand why: $query = $fieldParams->get('query', ''); // Run the query with a having condition because it supports aliases $db->setQuery($query . ' AND created_by =' . $db->q( 305 )); It doesn't like double spaces at the end of a line then ;-)
    – BJamieson
    Mar 25, 2020 at 0:41
  • Everyone is at different technical levels in their Joomla journey -- this is totally okay. Please edit your question to include any new relevant details (instead of commenting them). I am at work right now and don't have any means to directly assist. Where exactly are you doing your "hard-wiring"? Mar 25, 2020 at 0:57
  • What's the error you are getting? Mar 25, 2020 at 11:21
  • What's the error you are getting? Mar 25, 2020 at 11:21
  • I've tried putting on developer level logging, etc. If the SELECT contains the hardwired clause AND created_by = 305, it works, but if I adapt the clause to be created_by = %%userid%% and I also have the substitution code if (substr_count($value,"%%userid%%") > 0){ $userid = JFactory::getUser()->id; str_replace("%%userid%%", $userid, $query); } the substitution does not happen, the debug database section tells me that the SQL statement is wrong (of course), and the picklist of items is not created.
    – BJamieson
    Mar 25, 2020 at 11:42

3 Answers 3

0

As you're probably aware, the fields are added in via the system fields plugin. When the admin form is being built the event onContentPrepareForm is triggered, and this results in the system fields plugin adding in the custom fields into the form xml structure.

Later in the layout file renderField() is called, and this causes the field structures in the form xml to be converted into html.

I traced through the process with a debugger and found that the SQL custom fields are rendered using the JFormFieldSQL class which is in libraries/joomla/form/fields/sql.php. In particular the options for the html select field are generated in the getOptions() method which has the code:

if ($this->query)
{
    // Get the database object.
    $db = JFactory::getDbo();

    // Set the query and get the result list.
    $db->setQuery($this->query);

    try
    {
        $items = $db->loadObjectlist();
…

where $this->query is the SQL query which you specify, and is stored in the #__fields table row for this custom field, in the params column of that row.

So given the code above, the only way I could think of solving the problem is to create a new type of custom field (as you suggest), and I based it on the code for the sql field.

So there's 2 parts to it

First is to create the plugin for the new field, which I called sqlext. I copied the code for the sql plugin into a directory plg_fields_sqlext, and then changed 'sql' to 'sqlext' in the code. Eg for the plugin manifest file sqlext.xml I have:

<?xml version="1.0" encoding="utf-8" ?>
<extension type="plugin" version="3.7.0" group="fields" method="upgrade">
    <name>plg_fields_sqlext</name>
    <version>3.7.0</version>
    <description>SQL field extended</description>
    <files>
        <filename plugin="sqlext">sqlext.php</filename>
        <folder>params</folder>
        <folder>tmpl</folder>
    </files>
    <languages>
        <language tag="en-GB">en-GB.plg_fields_sql.ini</language>
        <language tag="en-GB">en-GB.plg_fields_sql.sys.ini</language>
    </languages>
</extension>

and in the main sqlext.php file the class now should reflect sqlext

class PlgFieldsSqlext extends FieldsListPlugin

If you install that plugin and then enable it, then when you go to create a new field you should see the sqlext custom field appearing. (The installation gives a warning because of the missing language files, but still installs; you can tidy this up yourself if you like).

The second thing is to handle the rendering of the field, and I copied the file in libraries/joomla/form/fields/sql.php into libraries/joomla/form/fields/sqlext.php and changed 3 things: the class name, the $type and the query handling in getOptions().

The class name should be

class JFormFieldSQLext extends JFormFieldList

The $type class variable should be

public $type = 'SQLEXT';

Within getOptions() I checked if "%%userid%%" was in the query, and if so replaced it with what I got from calling JFactory::getUser()->id. So I replaced (from about 6 lines down in that method)

    if ($this->query)
    {
        // Get the database object.
        $db = JFactory::getDbo();

        // Set the query and get the result list.
        $db->setQuery($this->query);

with

    if ($this->query)
    {
        if (strpos($this->query, "%%userid%%"))
        {
            $userid = JFactory::getUser()->id;
            $this->query = str_replace("%%userid%%", $userid, $this->query);
        }

        // Get the database object.
        $db = JFactory::getDbo();

        // Set the query and get the result list.
        $db->setQuery($this->query);

This hopefully does what you want! I'm not sure if it's a problem putting your own file in one of the joomla directories. I don't think joomla will delete it when it updates, although obviously if there's a sqlext field created in the future it will be overwritten.

UPDATE:

To be safe, put the file in eg a "customisations" directory, and then within your plugin sqlext.php file, within the onCustomFieldsPrepareDom method add the line:

JForm::addFieldPath(JPATH_ROOT . '/customisations');

This means that Joomla will search this "customisations" directory (as well as its standard ones) to find the class for rendering the field.

4
  • Hi folks - especially @Robbie, the 'part two' of custom fields, I was only just getting to. I was NOT yet aware of the process. My thinking about this sqlext custom field is that used in the right way, it could be suggested to the Joomla team to augment the existing sql field. So, for example, there could be a list of understood tokens that people could choose to use in their sql statement, and the token would transparently be altered to the 'live' value. For my specific use-case, I NEED the created_by = xxx. Mmmpfhmmmpfh so much for your assistance ! And you on 666 too ;-)
    – BJamieson
    Mar 25, 2020 at 20:34
  • Don't put files in libraries/joomla directory.
    – Sharky
    Mar 26, 2020 at 10:07
  • Where would you put them ?
    – BJamieson
    Mar 26, 2020 at 11:09
  • I added a couple of lines about putting the file in a different directory. I think you'd want to do this even for keeping track of your own customised files, so that they're not mixed in with joomla files. Mar 26, 2020 at 20:49
0

Either I'm editing the wrong bit of code.

This is the answer. The layout (plugins/fields/sql/tmpl/sql.php) is used for displaying the value in frontend, not for rendering the field in forms. You will need to create your own plugin. Either with a custom form field class or with custom code in onCustomFieldsPrepareDom event.

1
  • Thanks, I think I've just gotten to where you are by observing the behaviour with JFactory::getApplication()->enqueueMessage($query); because the message did not appear when editing the article. Thanks @Sharky, @Thiago and @mickmackusa. And thanks @FFrewin for letting me realise this is probably not the right place for me to be - I can't ask the right questions in the right way.
    – BJamieson
    Mar 25, 2020 at 20:20
0

Try to get the user id like this:

   $user = JFactory::getUser();
   $id= $user->id; 

Try to directly add it onto your sql statement:

$query = $fieldParams->get('query', ''); // Run the query with a having condition because it supports aliases $db->setQuery($query . ' AND created_by =' . $db->q('.$id.' ));

also check permissions and everything.

  1. You can try unistalling the plugin.

  2. Fix the database by going to:

Extension > Manage > Database and click "fix".

  1. Manually clean the tables releated to your plugin if there's still leftovers.

  2. Clean the cache.

  3. Verify if all files are there.

  4. Verify if it's the correct database.

  5. Install the plugin again.

PLUS:

Instead of editing

plugins/fields/sql/tmpl/sql.php

try to edit the helper and the view file. Those are responsible for manipulating the database. That's probably the cause of the error you are getting.

5
  • FFrewin i would like to avoid opening a discussion or information exchange via new answers but i can't post a comment because i don't have 50 reputation. I'm sorry if i broke the rules but it won't let me comment. I can only post a comment to my own answer. Mar 25, 2020 at 18:23
  • Hi Thiago! Yes I know. We all have been at this place at some point. Still the rules are rules and everybody follows them. You can try to gain reputation by participating in the website, posting answers or questions etc ;)
    – FFrewin
    Mar 25, 2020 at 18:41
  • Sure, thank you and i'm sorry once again. I will try to do my best abiding to the rules. Mar 25, 2020 at 18:43
  • I too have found the rules here a little 'odd'. I'll go try somewhere else - I had been pointed here as a 'first option'. Sorry. The latest clue I have is that the code is in the 'tmpl' folder of the plugin field definition - prob something to do with the database markup of #__content becoming asdf_content, and that mechanism kills the substitution process.
    – BJamieson
    Mar 25, 2020 at 18:44
  • Yes. I'm sorry if i couldn't help you that much but you can try checking the prefix #__ (name of the table) and check if it's the same table name that you have in your database, you can also try to create a new plugin basic plugin to check what's wrong and instead of creating a path like plugins/fields/sql/tmpl/sql.php you can start off with the basic helper file you could also consider creating a module which offers more features If you want, you can send me the plugin (if it's not a secret) and i can try debugging it for you. Good luck. Mar 25, 2020 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.