[SOLVED] Reference Filter issue on SQL

Bugs Solved Bugs
1

I am having an issue when searching through reference data, What I did is that I created a schema named region and added some content in it, afterwards I created another schema named stores and used region schema as reference in it (set dropdown as editor). Now when I added content in stores schema and doing a search through region reference, its showing me nothing, what I could be doing wrong here?

Picture is attached for a reference.

Screenshot 2026-02-16 100654
Screenshot 2026-02-16 1006541919×431 32.6 KB

I have…

  • [*] Read the following guideline: Troubleshooting and Support | Squidex. I understand that my support request might get deleted if I do not follow the guideline.
  • Used code blocks with ``` to format my code examples like JSON or logs properly.

I’m submitting a…

  • Regression (a behavior that stopped working in a new release)
  • Bug report
  • Performance issue
  • Documentation issue or request

Current behavior

Expected behavior

Minimal reproduction of the problem

Environment

App Name:

  • Self hosted with docker
  • [*] Self hosted with IIS
  • Self hosted with other version
  • Cloud version

Version: [VERSION]

Browser:

  • [*] Chrome (desktop)
  • Chrome (Android)
  • Chrome (iOS)
  • Firefox
  • Safari (desktop)
  • Safari (iOS)
  • IE
  • Edge

Others:

2

Sorry, I have really missed that. Could you provide my your schemas as json? There is a json tab.

3

Here is the schema of a region that I am using as a reference.

{
    "previewUrls": {},
    "properties": {
        "cachedValues": {},
        "label": "Regions",
        "validateOnPublish": false
    },
    "category": "Regions",
    "scripts": {},
    "isPublished": true,
    "fieldRules": [],
    "fieldsInLists": [],
    "fieldsInReferences": [
        "data.Name"
    ],
    "fields": [
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": true,
                "displayName": "Name"
            },
            "name": "Name",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "language",
            "properties": {
                "cachedValues": {},
                "fieldType": "String",
                "label": "Name",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "isUnique": false,
                "isEmbeddable": false,
                "inlineEditable": false,
                "createEnum": false,
                "contentType": "Unspecified",
                "editor": "Input"
            }
        },
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": false,
                "displayName": "Full Name"
            },
            "name": "FullName",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "invariant",
            "properties": {
                "cachedValues": {},
                "fieldType": "String",
                "label": "Full Name",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "isUnique": false,
                "isEmbeddable": false,
                "inlineEditable": false,
                "createEnum": false,
                "contentType": "Unspecified",
                "editor": "Input"
            }
        },
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": false,
                "displayName": "Image"
            },
            "name": "Image",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "invariant",
            "properties": {
                "cachedValues": {},
                "fieldType": "Assets",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "previewMode": "ImageAndFileName",
                "resolveFirst": false,
                "mustBeImage": false,
                "resolveImage": false,
                "allowDuplicates": false
            }
        },
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": true,
                "displayName": "ISOCode"
            },
            "name": "ISOCode",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "language",
            "properties": {
                "cachedValues": {},
                "fieldType": "String",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "isUnique": false,
                "isEmbeddable": false,
                "inlineEditable": false,
                "createEnum": false,
                "contentType": "Unspecified",
                "editor": "Input"
            }
        }
    ],
    "type": "Default"
}

and here is the schema of a newshub that I have created and using region as a reference in it

{
    "previewUrls": {},
    "properties": {
        "cachedValues": {},
        "validateOnPublish": false
    },
    "category": "News",
    "scripts": {},
    "isPublished": true,
    "fieldRules": [],
    "fieldsInLists": [
        "status",
        "data.PublishDate",
        "data.Regions",
        "data.Title",
        "version"
    ],
    "fieldsInReferences": [],
    "fields": [
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": false,
                "displayName": "Regions"
            },
            "name": "Regions",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "invariant",
            "properties": {
                "cachedValues": {},
                "fieldType": "References",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "allowDuplicates": false,
                "resolveReference": false,
                "mustBePublished": false,
                "query": "",
                "editor": "Tags",
                "schemaIds": [
                    "627dc189-e9ff-44ca-ab80-71fa0bf22367"
                ]
            }
        },
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": false,
                "displayName": "News Publish Date"
            },
            "name": "PublishDate",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "invariant",
            "properties": {
                "cachedValues": {},
                "fieldType": "DateTime",
                "label": "News Publish Date",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "editor": "Date"
            }
        },
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": true,
                "displayName": "Promotion Tag"
            },
            "name": "PromotionTag",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "language",
            "properties": {
                "cachedValues": {},
                "fieldType": "String",
                "label": "Promotion Tag",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "isUnique": false,
                "isEmbeddable": false,
                "inlineEditable": false,
                "createEnum": false,
                "contentType": "Unspecified",
                "editor": "Input"
            }
        },
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": false,
                "displayName": "Header Image"
            },
            "name": "HeaderImage",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "invariant",
            "properties": {
                "cachedValues": {},
                "fieldType": "Assets",
                "label": "Header Image",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "previewMode": "Image",
                "folderId": "5b1602cf-db20-4200-8e9d-5d02bf69231a",
                "maxItems": 1,
                "expectedType": "Image",
                "resolveFirst": false,
                "mustBeImage": true,
                "resolveImage": false,
                "allowDuplicates": false
            }
        },
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": true,
                "displayName": "News Title"
            },
            "name": "Title",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "language",
            "properties": {
                "cachedValues": {},
                "fieldType": "String",
                "label": "News Title",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "isUnique": false,
                "isEmbeddable": false,
                "inlineEditable": false,
                "createEnum": false,
                "contentType": "Unspecified",
                "editor": "Input"
            }
        },
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": true,
                "displayName": "News Sub Title"
            },
            "name": "SubTitle",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "language",
            "properties": {
                "cachedValues": {},
                "fieldType": "String",
                "label": "News Sub Title",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "isUnique": false,
                "isEmbeddable": false,
                "inlineEditable": false,
                "createEnum": false,
                "contentType": "Unspecified",
                "editor": "Input"
            }
        },
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": true,
                "displayName": "Description"
            },
            "name": "Description",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "language",
            "properties": {
                "cachedValues": {},
                "fieldType": "String",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "isUnique": false,
                "isEmbeddable": false,
                "inlineEditable": false,
                "createEnum": false,
                "contentType": "Unspecified",
                "editor": "RichText"
            }
        },
        {
            "cachedValues": {
                "canUpdate": true,
                "isLocalizable": false,
                "displayName": "Image"
            },
            "name": "Image",
            "isHidden": false,
            "isLocked": false,
            "isDisabled": false,
            "partitioning": "invariant",
            "properties": {
                "cachedValues": {},
                "fieldType": "Components",
                "label": "Image",
                "isRequired": false,
                "isRequiredOnPublish": false,
                "isHalfWidth": false,
                "isCreateOnly": false,
                "calculatedDefaultValue": "Null",
                "schemaIds": [
                    "8685370d-3039-4c0a-897d-3a14fbbe60b8"
                ]
            }
        }
    ],
    "type": "Default"
}
4

I cannot reproduce it … sorry

5

Do you want me to provide new simple schemas for the problem I am facing ? I really need the reference filter if possible.

6

I think that the reference filter is due to the DB server I am using, currently I am using sql server and your cloud might be using mongoDB or something else, I created the exact same schemas on both locally and on cloud, on cloud the reference filter works but on local it shows nothing.

7

Good point, I will dig into that. On SQL unfortunately not all operators are supported, but I will see what can be done.

8

Can you please look into it as soon as possible, I really need the reference Filtration.

9

Yes, sure. I will have a look.

10

I have just merged a big improvements to the SQL filters.

11

This is amazing - i have just had to fudge some queries and make them very very very unoptimized having looked through your PR this should fix the issue i have with querying

When can we expect v7.22.0 or v7.21.1?

12

Dont expect good performance. it is super difficult with the dynamic nature of the queries. Look at the SQL Code: https://github.com/Squidex/squidex/blob/master/backend/src/Squidex.Data.EntityFramework/Providers/SqlServer/json_function.sql

This is also due to the semantics of the queries that are copied from MongoDb basically.

13

Thank you so much, the search by reference filter is working now. can you guide me how I can add this part to released version because there is an issue with z-index or something here with the UI.

image
image1919×943 48 KB

Although the search filter works great now. Thanks again for rapid fix.

14

Oh, I can fix this as well…

1 Like
15

any chance we can get a release? I may have to build the docker file myself if not (sad face)

16

You can use the dev-build, it goes through the same steps as the normal release.