• method renamed from find_in_set() to findInSet()
• method added to the base class

I have tested this item ✅ successfully on a024973

Tested on a mysql environment. Thanks!!

Does joomla need new query method?
Why not replace find_in_set(x, column_with_list)
by something more common like CONCAT(',', x, ',') LIKE CONCAT('%,', column_with_list, ',%')?

i'm not a big fan of this FIND_IN_SET().... but as it is used by core com_fields......

Then I have prepared a list of possibilities.
There is a few sql string function that can replace find_in_set:

• common: LIKE operator
• mysql: locate, position, instr
• postgreSQL: position
• sqlsrv: charindex
• sqlite: instr

@csthomas I can't really remember, but there was a case where the like operation didn't work. I think it was when there was a comma in the search string or in the column_with_list.

I don't think adding a new method is a problem, the only problem I see is that this pr don't still provide a MSSQL translation , @csthomas can you test/write the translation maybe using charindex() on MSSQL ? I don't have a MSSQL environment available...

ok

now we have the MSSQL translation too
thanks @csthomas

Does that mean it is tested by @csthomas on MSSQL?

I have not tested com_fields.
I tested only echo $q->select('*')->from('#__content')->where($q->findInSet('your', 'title')); on mssql as I described at alikon#25

You can easily create it with com_fields when you create a few article categories and create a few fields and assign them to the different article categories. When you edit now an article only fields should be shown which belong to the category the article is in.

I have tested this item ? unsuccessfully on 146d188

When this PR is applied to 351b778 it results in a massive sql error

Unknown column 'a.catid' in 'on clause' SQL=SELECT a.*,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,ua.name AS author_name,c.title as category_title, c.access, c.published FROM #__fields AS a LEFT JOIN #__languagesAS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__users AS ua ON ua.id = a.created_user_id LEFT JOIN #__categories AS c ON c.id = a.catid WHERE a.context = 'com_content.article' AND a.state IN (0, 1) ORDER BY a.ordering asc LIMIT 20 Unknown column 'a.catid' in 'on clause' SQL=SELECT COUNT(*) FROM #__fields AS a LEFT JOIN#__languagesAS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__users AS ua ON ua.id = a.created_user_id LEFT JOIN #__categories AS c ON c.id = a.catid WHERE a.context = 'com_content.article' AND a.state IN (0, 1)

@PhilETaylor probably your test site is not updated see #13019
the #__fields table don't have a column named catid cause has been removed/changed with group_id

My site was a git clone of the main repo as of today

@philetaylor a clean install of staging?

I did have some similiar issues because of a broken setup :)

I have tested this item ? unsuccessfully on ec08ae2

I tested this on joomla-cms/staging at ec08ae2

I installed from scratch, from ec08ae2, and then installed PatchTester Extension, and applied the Patch from PR #12642

I then went to Admin -> Content -> Fields

I then get that massive SQL issue

Also after applying this patch on the frontend I see:

I have tested this item ✅ successfully on 146d188

Tested on a mysql environment. Thanks!!

@PhilETaylor I couldn't reproduce your error. Looks like there is some old code around in your installation as the catid got removed with PR #13019. I know you have been asked already, but are you sure you have the latest staging code and all fully synced?

Im sorry but this is impossible. I know how to make a clean slate and I know I can replicate this issue over and over.

On 13 Dec 2016, at 14:31, Allon Moritz notifications@github.com wrote:

@PhilETaylor https://github.com/PhilETaylor I couldn't reproduce your error. Looks like there is some old code around in your installation as the catid got removed with PR #13019 #13019. I know you have been asked already, but are you sure you have the latest staging code and all fully synced?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub #12642 (comment), or mute the thread https://github.com/notifications/unsubscribe-auth/AAYa3DgP1rs6zjnOCgySfh9WKyUxoUwtks5rHqxGgaJpZM4KkXdC.

In the file administrator/components/com_fields/models/fields.php after line 293 can you add the following code

echo $query;die;

what is the output?

I have just tried this again with joomla-cms/staging#102d90f65446e57d3f90445e85be87521d8ed325

Applied Patch for PR 12642 with patchtester extension from @mbabker

Same result

the part of the code changed (note the black dots in the gutter) near line 293

The dumped query, dumped before the $search = $this->getState('filter.search'); line:

SELECT a.*,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,ua.name AS author_name,c.title as category_title, c.access, c.published FROM #__fields AS a LEFT JOIN #__languages` AS l ON l.lang_code = a.language LEFT JOIN #__users AS uc ON uc.id=a.checked_out LEFT JOIN #__viewlevels AS ag ON ag.id = a.access LEFT JOIN #__users AS ua ON ua.id = a.created_user_id LEFT JOIN #__categories AS c ON c.id = a.catid WHERE a.context = 'com_content.article' AND a.state IN (0, 1)

`

This would mean the remote branch is out of sync then because the patch tester copies the files from the remote branch and replaces them, not patches files (too complex to be able to do that).

@mbabker define "remote branch" in this context (you mean alikon:patch-77 right?) ? as you can see Im starting with a clean folder checkout from the copy and pasted url of the github repo. See my terminal screen.

The remote branch in the context of a pull request is the branch in the user's fork the pull request comes from.

Generally, if one of the files in the pull request changed after they created that branch and made their changes, it can cause conflicts and unintended side effects like what you're seeing.

Thanks @mbabker for clarification. Will be hard without you in the future.

ok give me an hour and I'll manually prepare a patch file from the PR and apply that with command line patch tools :)

ok doing it that way and it tests successfully

I guess today I learned that the PatchTester extension has limitations :)

@waader do you have time to test it on MSSQL as well?

@PhilETaylor great. Can you please hit the successful test button on the issue tracker https://issues.joomla.org/tracker/joomla-cms/12642

I have tested this item ✅ successfully on 146d188

RTC

@alikon I assume you tested it on postgres, did you had also a look on MSSQL. I would not merge it before we don't have a MSSQL test.

I've tested on posgresql, mysql but not on MSSQL, and yes I agree we need
to wait for someonelse tests as for our rules on pr

On 14 Dec 2016 7:14 am, "Allon Moritz" notifications@github.com wrote:

@alikon https://github.com/alikon I assume you tested it on postgres,
did you had also a look on MSSQL. I would not merge it before we don't have
a MSSQL test.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#12642 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AALFsdyNYwt2Nj1q5wjIDwhGp-DCzXWeks5rH4lPgaJpZM4KkXdC
.

I will test on mssql today or tomorrow when i find some time.

Tested successful on mysql but I can not test this PR on mssql.

After I install joomla from git with mssql I got in backend warning:
"Error loading component: com_fields, Component not found."

@csthomas probably you need to install it from here https://github.com/alikon/joomla-cms/archive/patch-85.zip, as it contains the installer script for MSSQL.

I tried testing this one with the installation patch applied on mssql. Adding a new category I get

8155 [Microsoft][SQL Server Native Client 11.0][SQL Server]No column name was specified for column 1 of 'A'. SQL=SELECT * FROM ( SELECT count(id) , ROW_NUMBER() OVER (ORDER BY (select 0)) AS RowNumber FROM #__usergroups) A WHERE A.RowNumber BETWEEN 1 AND 1

Same when adding a field group
8155 [Microsoft][SQL Server Native Client 11.0][SQL Server]No column name was specified for column 1 of 'A'. SQL=SELECT * FROM ( SELECT count(id) , ROW_NUMBER() OVER (ORDER BY (select 0)) AS RowNumber FROM #__usergroups) A WHERE A.RowNumber BETWEEN 1 AND 1

I am stucked.

i'm quite sorry to say that this should be another different MSSQL issue
SELECT * FROM ( SELECT count(id) , ROW_NUMBER() OVER (ORDER BY (select 0)) AS RowNumber FROM #__usergroups) A WHERE A.RowNumber BETWEEN 1 AND 1

seems very ugly SQL

What I done:

git clone https://github.com/csthomas/joomla-cms.git # it is joomla/joomla-cms.git HEAD~2 for now

curl https://patch-diff.githubusercontent.com/raw/joomla/joomla-cms/pull/12642.diff | git apply
curl https://patch-diff.githubusercontent.com/raw/joomla/joomla-cms/pull/12660.diff | git apply

# GO TO libraries/cms/helper/usergroups.php:197:                                ->select('count(id)')
# AND CHANGE TO
libraries/cms/helper/usergroups.php:197:                                ->select('count(id) AS count')

Next I can go to create a new field but when I save I got:

Save failed with the following error: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Cannot insert the value NULL into column 'fieldparams', table 'joomla.dbo.#__fields'; column does not allow nulls. INSERT fails. SQL=INSERT INTO [#__fields] ([context],[group_id],[assigned_cat_ids],[title],[alias],[label],[default_value],[type],[note],[description],[state],[required],[params],[language],[created_time],[created_user_id],[access]) VALUES ('com_content.article','','76','Field recipes 1','field-recipes-1','Field recipes 1','','text','','','1','0','{"hint":"","image":"","image_alt":"","render_class":"","class":"","disabled":"0","readonly":"0","show_on":"","display":"-1"}','*','2016-12-15 20:12:53','968','1')

@alikon count(id) has to have an alias.

Should we fix it in libraries/cms/helper/usergroups.php or in JDatabaseQuerySqlsrv? No

yes seems unrelated to field_in_set()

@alikon Can I ask you to merge that PR with latest joomla staging.
It would be easier to test that PR without curl...

OK As this is another step forward I'm going to merge this. And I have opened #13263 to track the MSSQL testing which looks like it relies on other code to be merged