Inserttags are not functional in own SQL (Filter settings)

MacKP commented 1 year ago

Checklist before I submit this issue report

I confirm that:

[x] I have tested this with the latest version available
[x] I have read documentation @ http://metamodels.readthedocs.org/en/ or http://metamodels.readthedocs.org/de/
[x] I have checked the Contao community forums for references https://community.contao.org/
[x] I have checked existing issues for duplicates and found none @ https://github.com/MetaModels/core/issues?q=is%3Aissue

My environment is:

(Please fill in the actual values from your environment - check Contao Manager or use composer show)

Key	Value	Comments
PHP version:	7.4
Contao version:	4.9.39
MetaModels version:	2.2.7
Installed MetaModels packages:
DCG version:

Issue description

I have an SQL with: WHERE language = {{iflng::de}}1{{iflng::en}}3{{iflng::nl}}2{{iflng::es}}4{{iflng::el}}5{{iflng}} In MM Version 2.2.7 i get this query: WHERE language = 13245iflng

I have tested this with MM Version 2.2.6 and there is no bug in that way. I think the patch https://github.com/MetaModels/core/commit/121daee1cd975ad826c23eb655df97ff5d7fe07c is not realy a good sollution ;-)

Kind regards

zonky2 commented 1 year ago

@Ainschy can you check this?

Ainschy commented 1 year ago

Yes, it is a bug. I am working on fixing it.

zonky2 commented 1 year ago

dmolineus commented 1 year ago

The issue also exits in MetaModels 2.3.

Given SQL

SELECT * FROM  mm_region 
WHERE IF (
    {{param::get?name=act}} = 'edit', 
    (
        pid = (
            SELECT unterkunft_region_land
            FROM mm_unterkunft
            WHERE
                id=SUBSTRING_INDEX( {{param::get?name=id}} ,'::',-1)
            )
    ),
    (
        pid != ''
    )
)

Result after CustomSQL breaks it:

SELECT * FROM  mm_region
WHERE IF (
    NULL = 'edit',
    (
        pid = (
            SELECT unterkunft_region_land
            FROM mm_unterkunft
            WHERE
                id=SUBSTRING_INDEX(NULL{{,'::',-1)
            )
        ),
    (
        pid != ''
            )
    )
}}

Ainschy commented 1 year ago

When no default value is defined, the answer is NULL

{{param::get?name=category&default=defaultcat}}

zonky2 commented 1 year ago

When no default value is defined, the answer is NULL {{param::get?name=category&default=defaultcat}}

@baumannsven implemented this once - unfortunately I can no longer say exactly why... before it was an empty string

discordier commented 1 year ago

This was to allow for null. If you want an empty string, you should use an empty default AFAIR.

discordier commented 1 year ago

After discussion with @dmolineus:

Given SQL

SELECT * FROM  mm_region 
WHERE IF (
    {{param::get?name=act}} = 'edit', 
    (
        pid = (
            SELECT unterkunft_region_land
            FROM mm_unterkunft
            WHERE
                id=SUBSTRING_INDEX( {{param::get?name=id}} ,'::',-1)
            )
    ),
    (
        pid != ''
    )
)

The expected Result is (green=expected;red=incorrectly generated)

 SELECT * FROM  mm_region
 WHERE IF (
     NULL = 'edit',
     (
         pid = (
             SELECT unterkunft_region_land
             FROM mm_unterkunft
             WHERE
+                id=SUBSTRING_INDEX(NULL,'::',-1)
-                id=SUBSTRING_INDEX(NULL{{,'::',-1)
             )
         ),
     (
         pid != ''
     )
)
-}}

zonky2 commented 1 year ago

Fixed with https://github.com/MetaModels/core/pull/1496

MetaModels / core