How to handle accents characters in Spanish websites in Mura

Question

The issue I am encountering is when a user is on the Spanish website, they are not able to see all results if their type the word incorrectly. Meaning, if their character does not have the accent above the character, the results will show blank.

For example, if the user types the word "Medico" without the accent on top of the "é", the search will return blank.

I am using a Mura, a CMS which the search is created. I was able to locate the function where it does search based on the user input. However, I am not able to clearly understand how the query is being implemented.

I am trying to modify the function so that it uses the collation, which I found an example on how to use on stackoverflow, which would handle the accents for Spanish or any other language(s).

I figure it should go after "where" and have a cfif statement where if it is the spanish site, to incorporate the collation.

<cfif #siteID# = "SpanishSite" />
 //here would go the collation on keywords, titles, etc.

Note that the query is done in coldfusion:

<cffunction name="getPublicSearch" returntype="query" access="public" output="false">
<cfargument name="siteid" type="string" required="true">
<cfargument name="keywords" type="string" required="true">
<cfargument name="tag" type="string" required="true" default="">
<cfargument name="sectionID" type="string" required="true" default="">
<cfargument name="categoryID" type="string" required="true" default="">
<cfargument name="tagGroup" type="string" required="true" default="">

<cfset var rsPublicSearch = "">
<cfset var w = "">
<cfset var c = "">
<cfset var categoryListLen=listLen(arguments.categoryID)>

<cfquery attributeCollection="#variables.configBean.getReadOnlyQRYAttrs(name='rsPublicSearch',maxrows=1000)#">
<!--- Find direct matches with no releasedate --->

select tcontent.contentid,tcontent.contenthistid,tcontent.siteid,tcontent.title,tcontent.menutitle,tcontent.targetParams,tcontent.filename,tcontent.summary,tcontent.tags,
tcontent.restricted,tcontent.releaseDate,tcontent.type,tcontent.subType,
tcontent.restrictgroups,tcontent.target ,tcontent.displaystart,tcontent.displaystop,0 as Comments, 
tcontent.credits, tcontent.remoteSource, tcontent.remoteSourceURL, 
tcontent.remoteURL,tfiles.fileSize,tfiles.fileExt,tcontent.fileID,tcontent.audience,tcontent.keyPoints,
tcontentstats.rating,tcontentstats.totalVotes,tcontentstats.downVotes,tcontentstats.upVotes, 0 as kids, 
tparent.type parentType,tcontent.nextn,tcontent.path,tcontent.orderno,tcontent.lastupdate, tcontent.created,
tcontent.created sortdate, 0 priority,tcontent.majorVersion, tcontent.minorVersion, tcontentstats.lockID, tcontentstats.lockType, 
tcontent.expires,tfiles.filename as assocFilename, tcontentfilemetadata.altText as fileAltText,
CASE WHEN tcontent.title = <cfqueryparam cfsqltype="cf_sql_varchar" value="#renderTextParamValue(arguments.keywords)#">
    or tcontent.menuTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#renderTextParamValue(arguments.keywords)#"> THEN 0 ELSE 1 END AS superSort
from tcontent Left Join tfiles ON (tcontent.fileID=tfiles.fileID)
Left Join tcontent tparent on (tcontent.parentid=tparent.contentid
                                    and tcontent.siteid=tparent.siteid
                                    and tparent.active=1) 
Left Join tcontentstats on (tcontent.contentid=tcontentstats.contentid
                    and tcontent.siteid=tcontentstats.siteid) 
Left Join tcontentfilemetadata on (tcontent.fileid=tcontentfilemetadata.fileid
                                                and tcontent.contenthistid=tcontentfilemetadata.contenthistid)


<cfif len(arguments.tag)>
    Inner Join tcontenttags on (tcontent.contentHistID=tcontenttags.contentHistID)
</cfif> 
    where

                    (tcontent.Active = 1 
                    AND tcontent.Approved = 1
                    AND tcontent.siteid = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.siteID#"/> )

                    AND

                    (
                      tcontent.Display = 2 
                        AND 
                        (
                            (tcontent.DisplayStart <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
                            AND (tcontent.DisplayStop >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#"> or tcontent.DisplayStop is null)
                            )
                            OR  tparent.type='Calendar'
                        )

                        OR tcontent.Display = 1
                    )


            AND
            tcontent.type in ('Page','Folder','Calendar','File','Link','Gallery')

            AND tcontent.releaseDate is null

            <cfif len(arguments.sectionID)>
            and tcontent.path like  <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.sectionID#%">   
            </cfif>

            <cfif len(arguments.tag)>
                and (
                        #renderTextParamColumn('tcontenttags.tag')# in (<cfqueryparam list="true" cfsqltype="cf_sql_varchar" value="#renderTextParamValue(arguments.tag)#"/> )
                        <cfif len(arguments.tagGroup) and arguments.tagGroup neq 'default'>
                            and #renderTextParamColumn('tcontenttags.taggroup')#=<cfqueryparam cfsqltype="cf_sql_varchar" value="#renderTextParamValue(arguments.taggroup)#"/>
                        </cfif>
                    )   
            <cfelse>
                <!---
                <cfloop list="#trim(arguments.keywords)#" index="w" delimiters=" ">
                        and
                        (tcontent.Title like  <cfqueryparam cfsqltype="cf_sql_varchar" value="%#w#%">
                        or tcontent.menuTitle like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#w#%">
                        or tcontent.metaKeywords like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#w#%">
                        or tcontent.summary like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#w#%"> 
                        or tcontent.body like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#w#%">)
                </cfloop>
                --->
                and
                        (#renderTextParamColumn('tcontent.Title')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                        or #renderTextParamColumn('tcontent.menuTitle')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                        or #renderTextParamColumn('tcontent.metaKeywords')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                        or #renderTextParamColumn('tcontent.summary')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                        or (
                                tcontent.type not in ('Link','File')
                                and #renderTextParamColumn('tcontent.body')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                            )
                        or #renderTextParamColumn('tcontent.credits')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">

                        or tcontent.contenthistid in (
                            select distinct tcontent.contenthistid from tclassextenddata 
                            inner join tcontent on (tclassextenddata.baseid=tcontent.contenthistid)
                            where tcontent.active=1
                            and tcontent.siteid=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.siteID#"/> 
                            and #renderTextParamColumn('tclassextenddata.attributeValue')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                        ))
            </cfif>

            and tcontent.searchExclude=0

            <cfif categoryListLen>
                  and tcontent.contentHistID in (
                        select tcontentcategoryassign.contentHistID from 
                        tcontentcategoryassign 
                        inner join tcontentcategories 
                        ON (tcontentcategoryassign.categoryID=tcontentcategories.categoryID)
                        where (<cfloop from="1" to="#categoryListLen#" index="c">
                                tcontentcategories.path like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#listgetat(arguments.categoryID,c)#%"/>
                                <cfif c lt categoryListLen> or </cfif>
                                </cfloop>) 
                  )
            </cfif>

            #renderMobileClause()#


union all

<!--- Find direct matches with releasedate --->

select tcontent.contentid,tcontent.contenthistid,tcontent.siteid,tcontent.title,tcontent.menutitle,tcontent.targetParams,tcontent.filename,tcontent.summary,tcontent.tags,
tcontent.restricted,tcontent.releaseDate,tcontent.type,tcontent.subType,
tcontent.restrictgroups,tcontent.target ,tcontent.displaystart,tcontent.displaystop,0 as Comments, 
tcontent.credits, tcontent.remoteSource, tcontent.remoteSourceURL, 
tcontent.remoteURL,tfiles.fileSize,tfiles.fileExt,tcontent.fileID,tcontent.audience,tcontent.keyPoints,
tcontentstats.rating,tcontentstats.totalVotes,tcontentstats.downVotes,tcontentstats.upVotes, 0 as kids, 
tparent.type parentType,tcontent.nextn,tcontent.path,tcontent.orderno,tcontent.lastupdate, tcontent.created,
tcontent.releaseDate sortdate, 0 priority,tcontent.majorVersion, tcontent.minorVersion, tcontentstats.lockID, tcontentstats.lockType, 
tcontent.expires,tfiles.filename as assocFilename, tcontentfilemetadata.altText as fileAltText,
CASE WHEN tcontent.title = <cfqueryparam cfsqltype="cf_sql_varchar" value="#renderTextParamValue(arguments.keywords)#">
    or tcontent.menuTitle = <cfqueryparam cfsqltype="cf_sql_varchar" value="#renderTextParamValue(arguments.keywords)#"> THEN 0 ELSE 1 END AS superSort
from tcontent Left Join tfiles ON (tcontent.fileID=tfiles.fileID)
Left Join tcontent tparent on (tcontent.parentid=tparent.contentid
                                    and tcontent.siteid=tparent.siteid
                                    and tparent.active=1) 
Left Join tcontentstats on (tcontent.contentid=tcontentstats.contentid
                    and tcontent.siteid=tcontentstats.siteid) 
Left Join tcontentfilemetadata on (tcontent.fileid=tcontentfilemetadata.fileid
                                                and tcontent.contenthistid=tcontentfilemetadata.contenthistid)


<cfif len(arguments.tag)>
    Inner Join tcontenttags on (tcontent.contentHistID=tcontenttags.contentHistID)
</cfif> 
    where
                    (tcontent.Active = 1 
                    AND tcontent.Approved = 1
                    AND tcontent.siteid = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.siteID#"/> )

                    AND

                    (
                      tcontent.Display = 2 
                        AND 
                        (
                            (tcontent.DisplayStart <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
                            AND (tcontent.DisplayStop >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#"> or tcontent.DisplayStop is null)
                            )
                            OR  tparent.type='Calendar'
                        )

                        OR tcontent.Display = 1
                    )


            AND
            tcontent.type in ('Page','Folder','Calendar','File','Link','Gallery')

            AND tcontent.releaseDate is not null

            <cfif len(arguments.sectionID)>
            and tcontent.path like  <cfqueryparam cfsqltype="cf_sql_varchar" value="%#arguments.sectionID#%">   
            </cfif>

            <cfif len(arguments.tag)>
                and (
                        #renderTextParamColumn('tcontenttags.tag')# in (<cfqueryparam list="true" cfsqltype="cf_sql_varchar" value="#renderTextParamValue(arguments.tag)#"/> )
                        <cfif len(arguments.tagGroup) and arguments.tagGroup neq 'default'>
                            and #renderTextParamColumn('tcontenttags.taggroup')#=<cfqueryparam cfsqltype="cf_sql_varchar" value="#renderTextParamValue(arguments.taggroup)#"/>
                        </cfif>
                    )   
            <cfelse>
                <!---
                <cfloop list="#trim(arguments.keywords)#" index="w" delimiters=" ">
                        and
                        (tcontent.Title like  <cfqueryparam cfsqltype="cf_sql_varchar" value="%#w#%">
                        or tcontent.menuTitle like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#w#%">
                        or tcontent.metaKeywords like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#w#%">
                        or tcontent.summary like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#w#%"> 
                        or tcontent.body like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#w#%">)
                </cfloop>
                --->
                and
                        (#renderTextParamColumn('tcontent.Title')# like  <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">

                        or #renderTextParamColumn('tcontent.menuTitle')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                        or #renderTextParamColumn('tcontent.metaKeywords')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                        or #renderTextParamColumn('tcontent.summary')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                        or 
                            (
                                tcontent.type not in ('Link','File')
                                and #renderTextParamColumn('tcontent.body')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                            )
                        or #renderTextParamColumn('tcontent.credits')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">

                        or tcontent.contenthistid in (
                            select distinct tcontent.contenthistid from tclassextenddata 
                            inner join tcontent on (tclassextenddata.baseid=tcontent.contenthistid)
                            where tcontent.active=1
                            and tcontent.siteid=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.siteID#"/> 
                            and #renderTextParamColumn('tclassextenddata.attributeValue')# like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#renderTextParamValue(arguments.keywords)#%">
                        ))
            </cfif>

            and tcontent.searchExclude=0

            <cfif categoryListLen>
                  and tcontent.contentHistID in (
                        select tcontentcategoryassign.contentHistID from 
                        tcontentcategoryassign 
                        inner join tcontentcategories 
                        ON (tcontentcategoryassign.categoryID=tcontentcategories.categoryID)
                        where (<cfloop from="1" to="#categoryListLen#" index="c">
                                tcontentcategories.path like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#listgetat(arguments.categoryID,c)#%"/> 
                                <cfif c lt categoryListLen> or </cfif>
                                </cfloop>) 
                  )
            </cfif>

            #renderMobileClause()#          

ORDER BY supersort, priority, <cfif variables.configBean.getDBType() neq 'nuodb'>sortdate<cfelse>releasedate</cfif> desc
</cfquery>

<cfreturn rsPublicSearch />

My question is: Where should the collation go in the query above? Or is there a better approach than using collation in the query?

Or should I reach out to Mura and see if a new framework would need to be implemented to achieve what I am looking for?

Any help is appreciated

Answer 1

To expand on my comment.

const userInput = 'Médico';
const parsedInput = userInput.replace(/é/gmi, 'e').toLowerCase();

Then you take the that and store it in the database, so if the user searches for medico, Medico, MEDIcO, or any variant of that then it'll always be available to retrieve it from the database.

I can also write this in Spanish if it's easier for you to understand, but SO guidelines prefer English.

Here's a StackBlitz to kind of show you what I mean.