http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemBrowse&tracker_id=8549

JC is http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemEdit&tracker_id=8549&tracker_item_id=33628

@fastslack have you a easy way how we can test this? Or a example how this can/is used?

Cood looks ok here (I will comment for CS issues). Thanks.

@zero-24 If you have a component list into the administration side that use filters (using JModel::getfilterForm) the file to edit is file located in /models/forms/filter_yourlist.xml . Look at the explanation of filters there are a good example of how to test it. You can add tags with filters, so your inputs will modify "inline" with the new filter status.

@fastslack i have it just tested.

Please confirm that it was correct

First I apply your patch.

I'm using this xml definition

<field
 name="example_group" 
 type="sql" 
 label="COM_EXAMPLE_GROUP" 
 sql_select="c.*" 
 sql_from="#__content AS c" 
 sql_group="title" 
 sql_order="c.id ASC" 
 key_field="id"
 value_field="title" 
 onchange="this.form.submit();"
/>

on the edit.php i have add this lines:

<div class="control-group">
 <div class="control-label"><?php echo $this->form->getLabel('example_group'); ?></div>
 <div class="controls"><?php echo $this->form->getInput('example_group'); ?></div>
</div>

The result is the following:

The Debug Console show this sql query

SELECT c.*
FROM iv1sg_content AS c
GROUP BY titl

Is this correct?

This comment was created with the J!Tracker Application at http://issues.joomla.org/.

Yes, that is correct but also, we can use it to filter lists too. Check this example:

We have a simple list with 3 filters to apply:

Into the file com_components/models/forms/filter_ourlist.xml we have:

<field name="place" type="msql" default="1" label="COM_EXAMPLE_PLACE" 
  sql_select="t.id, t.name" sql_from="#__places AS t" 
  sql_where="t.published = 1" sql_order="t.id ASC"
  key_field="id" value_field="name" onchange="this.form.submit();" />

<field name="zone" type="msql" size="1" default="" label="COM_EXAMPLE_ZONE" header="COM_EXAMPLE_ZONE"
  sql_select="t.id, t.name" sql_from="#__zones AS t" sql_where="t.id != 0" sql_order="t.name ASC" sql_filter="place"
  key_field="id" value_field="name" description="PARAMFILE" onchange="this.form.submit();" 
/>

<field name="muscle_group" type="msql" size="1" default="" label="COM_EXAMPLE_MUSCULAR_GROUP" header="COM_EXAMPLE_MUSCULAR_GROUP"
  sql_select="t.id, t.name" sql_from="#__muscles_groups AS t" 
  sql_where="t.enabled = 1 AND t.id != 0" sql_order="t.name ASC" sql_filter="" 
  key_field="id" value_field="name" description="PARAMFILE" onchange="this.form.submit();" 
/>

See that the zone filter has some argument called sql_filter with the value place. So this means that zone will check inline for changes on "place" input, and apply to the same list the filter.

And if we change the "place" filter.

Same is we apply the filter and we decided to made a new article, if the form to create a new article has the input "zone" the filter will be applied.

Check the latest file here:

https://github.com/fastslack/matware-libraries/blob/master/libraries/matware/form/fields/msql.php

Yes that looks cool here. Can you have a look into the CS things i comment? If it is ok i can send a quick PR against yours that can fix the CS related things.

Looking for CS, give me a moment...

Done

thanks @fastslack

Sorry but Travis find too more cs issue that i have not seen:

FILE: ...ome/travis/build/joomla/joomla-cms/libraries/joomla/form/fields/sql.php
--------------------------------------------------------------------------------
FOUND 2 ERROR(S) AFFECTING 2 LINE(S)
--------------------------------------------------------------------------------
 168 | ERROR | Expected 2 spaces after the longest variable name
 208 | ERROR | Expected "foreach (...)\n...{\n"; found "foreach(...)\n...{\n"
--------------------------------------------------------------------------------

Fixed travis errors

Hehe Travis does not give up

FILE: ...ome/travis/build/joomla/joomla-cms/libraries/joomla/form/fields/sql.php
--------------------------------------------------------------------------------
FOUND 2 ERROR(S) AFFECTING 2 LINE(S)
--------------------------------------------------------------------------------
 166 | ERROR | Extra newline(s) found before function comment short description
 169 | ERROR | Expected 2 spaces after the longest variable name
--------------------------------------------------------------------------------

I think Mr. Travis was satisfied :)

Mr. Travis:

All is well

So we only need one more test here

sql_order not workig for me. I have added this to articles_filter.xml:

        <field
            name="example_group"
            type="sql"
            label="COM_EXAMPLE_GROUP"
            sql_select="test.*"
            sql_from="#__content AS test"
            sql_group="title"
            sql_order="test.title DESC"
            key_field="id"
            value_field="title"
            onchange="this.form.submit();"/>

And get only this in debug:

SELECT test.*
FROM huoqm_content AS test
GROUP BY title

Can you also clarify on sql_filter? Is it simply adds WHERE to query and takes the name of connected filter?

For example I am adding sql_filter = "author", then query will be WHERE author = {some value from the author filter}. Is it correct?

This comment was created with the J!Tracker Application at http://issues.joomla.org/.

@b2z Yes is that correct. Also you can use comma-separated filter to multiple filters like:

sql_filter = "author, date"

Remember that you should have anothers inputs called 'author' and 'date'. It the input is not set, it just will be ignored.

Also i added the order condition to query that seems was missing.

@fastslack I got it. I'll test one more time then ;)

@zero-24 do you think you need to retest?

This comment was created with the J!Tracker Application at http://issues.joomla.org/.

@test ok
good job @fastslack

This comment was created with the J!Tracker Application at http://issues.joomla.org/.

@test @fastslack my example code: #3476 (comment) still works ok here.

@b2z @zero-24 Thanks very much for testing. Is there is another step to merge it?

Hi guys, please test the latest updates. I added default values options so we can set:

sql_default_{FILTER_NAME}="1"

If the filter value is empty, the default value for the custom field name is added to query.

Also added security checks for sql query to prevent injections.

As there have been changes to the Pull Request after the tests I am resetting the tests - we need two tests on the latest PR thanks

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/3476.

Works only with correct syntax. Otherwise - Warnings or destroyed Template

@chmst can you give me an example? because i think that the current sql field destroy the template if syntax is wrong too

You are right - the behaviour is the same if a query is not well formed.
I've repeated the test - and with your query it works.
But I could not add a where clause. So this feature is not very useful for me.
Maybe you can expand your code?

    <field
         name="test" 
         type="sql" 
         label="test" 
         query="select c.id, c.title from #__content as c where c.title like '%Begleitservice%'"
         key_field="id"
         value_field="title" 
         onchange="this.form.submit();"
        />

makes a dropdown with 2 entries as expected for my database.

    <field
         name="test" 
         type="sql" 
         label="test" 
         sql_select="c.id, c.title" 
         sql_from="#__content as c" 
         sql_where="c.title like '%Begleitservice%'"
         sql_group="c.title" 
         sql_order="c.id ASC" 
         key_field="id"
         value_field="title" 
         onchange="this.form.submit();"
        />

gives the whole list of entries in the table.

Of course, i ll check this. Thanks for testing!

@fastslack did you get time to check the where as suggested by @chmst ?

@chmst please test now, sorry about the delay

Hi, I did it - and it was successful. I tested on a database with > 1000 articles and both variats, the old and the new one gave the same result:
name="test-new"
type="sql"
label="test"
sql_select="c.id, c.title"
sql_from="#__content as c"
sql_where="c.title like '%Bayrischzell%'"
key_field="id"
value_field="title"
/>

name="test-old"
type="sql"
label="test old"
query="select c.id, c.title from #__content as c where c.title like '%Bayrischzell%'"
key_field="id"
value_field="title"
/>

Note: I did not test join ...

@fastslack i have just also retetsted with the codes above.

But i know it is hard to tell but Travis is again not happy.

1) JFormFieldSQLTest::testGetInput
Undefined property: JFormFieldSQL::$header
/home/travis/build/joomla/joomla-cms/libraries/joomla/form/fields/sql.php:79
/home/travis/build/joomla/joomla-cms/libraries/joomla/form/fields/sql.php:273
/home/travis/build/joomla/joomla-cms/libraries/joomla/form/fields/list.php:58
/home/travis/build/joomla/joomla-cms/libraries/joomla/form/field.php:393
/home/travis/build/joomla/joomla-cms/libraries/joomla/form/fields/sql.php:82
/home/travis/build/joomla/joomla-cms/tests/unit/suites/libraries/joomla/form/fields/JFormFieldSQLTest.php:108
phar:///home/travis/.phpenv/versions/5.3.29/bin/phpunit/phpunit/TextUI/Command.php:152
phar:///home/travis/.phpenv/versions/5.3.29/bin/phpunit/phpunit/TextUI/Command.php:104

https://travis-ci.org/joomla/joomla-cms/jobs/61804403

It looks like he is not happy with the new $header option?

@zero-24 Seems that Travis is happy now :-D

Thanks @fastslack Based on the tests here i move to RTC.

Merged to 3.5-dev via 30a953e