Foreign key field additional SQL parameters

Hi there!

Would be great if a foreign key field could have additional parameter for select query.

Now we select table, key field and value field ..

But how about extra query? For example, I want to select only those records where certain fields have specific value.

SELECT id, name from #__sometable where otherfield=1 ..

I see that this could be possible with SQL list, but then there's no EMPTY RECORD (like Please select value (0) ) ..

I tried to do so with UNION ..

(SELECT '0' as id, 'Please select' as name from #__sometable LIMIT 1) UNION (SELECT id, name from #__sometable where parentid=0)

But then I get mysql errors when any field has selected that parentid field .. HAVING id='1'

thanks

Hi!

Thanks for your feedback. Sounds like a good idea, let's see if it gets more votes!

Regards,

Andrés.

administrator\models\forms\tablename.xml

add new attribute to field : where="parent_id=0"

administrator\models\fields\foreignkey.php

private $where_extra;

protected function getInput()
{
.......
$this->where_extra = (string) $this->getAttribute('where');

........

if($this->where_extra != '')
{
$query->where($this->where_extra);
}

....
}



works fine for me, it's just annoying to modify all this after rebuild ..

I have also added a "where" attribute to limit foreignkey results based on a condition.

// If the return is to be limited by condition
$this->where = ( trim ( $this->getAttribute('where') ) <> '' ) ? (string) trim( $this->getAttribute('where') ) : NULL;
.......
// add where filtering, if set
if ( $this->where ) {
$query->where($this->where);
} // end if


I have also found that displaying a completely empty select box is pretty useless so I set empty foreignkey fields to hide.

if ( count( $options ) ) {
$html = JHtml::_('select.genericlist', $options, $this->name, $input_options, 'value', 'text', $value, $this->id);
} else {
$html = '';
$this->hidden = true;
}

An additional attribute, such as "hideempty", could be helpful.