Proposal to make SELECT's modifiable

[Makoto]

One of the new third-party standards is that we should not be manipulating core IPS tables (with exception to group settings still I believe). Which I think is absolutely fine and reasonable.

It's not terribly difficult to utilize joins to add in custom columns where we need them. What is currently hard, however, is ensuring our custom columns are actually selected.

Take, for example, the following hook I have written,

//<?php

/* To prevent PHP errors (extending class does not exist) revealing path */
if ( !\defined( '\IPS\SUITE_UNIQUE_KEY' ) )
{
    exit;
}

class radtags_hook_C_DownloadsCategory extends _HOOK_CLASS_
{
    /**
     * Our custom data table
     * @var string
     */
    protected static $radtagsDatabaseTable = 'radtags_downloads_categories';

    /**
     * [Node] Add/Edit Form
     *
     * @param	\IPS\Helpers\Form	$form	The form
     * @return	void
     */
    public function form( &$form )
    {
        parent::form( $form );

        // Get our tags and prefixes for autocomplete suggestions
        $tags = \IPS\radtags\Tag\TagFactory::i()->allTags();

        $prefixes = [];
        foreach ( $tags as $tag )
        {
            if ( $tag->prefix_only )
            {
                $prefixes[] = $tag;
            }
        }

        \dd($this);

        $form->elements['category_forums_integration']['cforum_on']->options['togglesOn'][] = 'form_1_radtags_ctopic_tags';
        $form->elements['category_forums_integration']['cforum_on']->options['togglesOn'][] = 'form_1_radtags_ctopic_prefix';
        $form->add( new \IPS\Helpers\Form\Text( 'radtags_ctopic_prefix', $this->radtags_ctopic_prefix, FALSE, array( 'autocomplete' => array( 'unique' => 'true', 'source' => $prefixes, 'freeChoice' => TRUE, 'maxItems' => 1 ) ) ), 'ctopic_suffix', 'category_forums_integration' );
        $form->add( new \IPS\Helpers\Form\Text( 'radtags_ctopic_tags', $this->radtags_ctopic_tags, FALSE, array( 'autocomplete' => array( 'unique' => 'true', 'source' => $tags, 'freeChoice' => TRUE, 'maxItems' => 1 ) ) ), 'ctopic_suffix', 'category_forums_integration' );
    }

    /**
     * [Node] Save Add/Edit Form
     *
     * @param	array	$values	Values from the form
     * @return	void
     */
    public function saveForm( $values )
    {
        // Nab our custom settings
        $ourSet = [];
        $ourSet['radtags_ctopic_prefix'] = $values['radtags_ctopic_prefix'];
        $ourSet['radtags_ctopic_tags']   = $values['radtags_ctopic_tags'];
        unset( $values['radtags_ctopic_prefix'], $values['radtags_ctopic_tags'] );

        // Go ahead and process now to make sure there are no errors
        parent::saveForm( $values );

        $ourSet['cid'] = $this->id;

        \IPS\Db::i()->replace( static::$radtagsDatabaseTable, $ourSet );
    }

    /**
     * Construct Load Query
     *
     * @param	int|string	$id					ID
     * @param	string		$idField			The database column that the $id parameter pertains to
     * @param	mixed		$extraWhereClause	Additional where clause(s)
     * @return	\IPS\Db\Select
     */
    static protected function constructLoadQuery( $id, $idField, $extraWhereClause )
    {
        /** @var \IPS\Db\Select $query */
        $query = parent::constructLoadQuery( $id, $idField, $extraWhereClause );
        $query = $query->join( static::$radtagsDatabaseTable, static::$databaseTable . '.cid=' . static::$radtagsDatabaseTable . '.cid' );

        // Add to our selects. I hate we have to directly manipulate queries this way, but..
        $pos = mb_strpos( $query->query, ' FROM' );

        if( $pos !== FALSE )
        {
            $columns = ', radtags_downloads_categories.*';
            $query->query = \substr_replace( $query->query, $columns, $pos, 0 );
        }

        return $query;
    }

}

The issue here lies in the final hook constructLoadQuery hook, where we join in our custom settings table.

Adding a join statement using this method is perfectly easy. But when doing this, none of the columns in our joined table are selected by default, and there's no way to simply add them to an array of select statements in the current query builder. So, the only way to accomplish this is by altering that query manually, which I hate ever needing to do, and I'm sure you probably don't like us doing this either.

If you can add a way to programmatically alter selected columns in the query builder, this would allow us to accomplish this hassle free and without needing to rely on hackery.

[bfarber]

We have an internal suggestion to overhaul how we handle selects already, which would cover this. Unfortunately I can't say when it will be included or implemented.

[Makoto]

Darn, that makes it sound like it's not likely to make 4.5. But hopefully soon.

Thanks again.

I'm replying to you fast this morning.