×
This forum has been locked. Please submit new Feature Requests on GitHub: github.com/Jensen-Technologies/component-creator-issues/issues
Foreign key field additional SQL parameters
RATE IT SERVICES, SIA
New Member
Posts: 2
8 years 7 months ago #1295
by RATE IT SERVICES, SIA
Foreign key field additional SQL parameters was created by RATE IT SERVICES, SIA
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
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
Please Log in or Create an account to join the conversation.
Andres Maeso
Platinum Member
Posts: 338
8 years 7 months ago #1296
by Andres Maeso
Andrés Maeso
Customer relations manager at Joomla Component Creator.
This email address is being protected from spambots. You need JavaScript enabled to view it.
Replied by Andres Maeso on topic Foreign key field additional SQL parameters
Hi!
Thanks for your feedback. Sounds like a good idea, let's see if it gets more votes!
Regards,
Andrés.
Thanks for your feedback. Sounds like a good idea, let's see if it gets more votes!
Regards,
Andrés.
Andrés Maeso
Customer relations manager at Joomla Component Creator.
This email address is being protected from spambots. You need JavaScript enabled to view it.
Please Log in or Create an account to join the conversation.
RATE IT SERVICES, SIA
New Member
Posts: 2
8 years 7 months ago #1297
by RATE IT SERVICES, SIA
Replied by RATE IT SERVICES, SIA on topic Foreign key field additional SQL parameters
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 ..
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 ..
Please Log in or Create an account to join the conversation.
Jeremy Morris
New Member
Posts: 4
8 years 3 months ago #1399
by Jeremy Morris
Replied by Jeremy Morris on topic Foreign key field additional SQL parameters
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.
// 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.
Please Log in or Create an account to join the conversation.
Time to create page: 0.060 seconds