How to add the current user's user id in query of a custom field for a plugin?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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".

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

4. Clean the cache.

5. Verify if all files are there.

6. Verify if it's the correct database.

7. 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.