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

What I've done in the past is just save the values without the tildes and have a regexp to replace any tilde characters with normal ones so it matches the search. For the user it looks the same, but for you it's easier to manage. — Baruch, Aug 09 '18 at 19:05
Depends on how exactly you're implementing the search. You might find an answer here https://stackoverflow.com/questions/990904/remove-accents-diacritics-in-a-string-in-javascript — John Montgomery, Aug 09 '18 at 19:07
@Baruch: So what you mean is you would grab the user input, replace the normal character with the character with tilde? Am I understanding you correctly? — user9808783, Aug 09 '18 at 19:08
You can't add the accent to the user input, there's no practical way to know for sure whether it's supposed to be there or not. You'd need to strip the accents from the data instead (and from the input as well, if they added one). — John Montgomery, Aug 09 '18 at 19:41

Baruch · Answer 1 · 2018-08-09T19:51:42.830

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.

edited Aug 09 '18 at 19:51

answered Aug 09 '18 at 19:43

Baruch

2,381
1
17
29

@ Baruch: Oh okay. Yes I have tried that but the problem is I do not have access to the table itself. But as far as I am aware, the table already pertains keywords with the tildes – user9808783 Aug 09 '18 at 20:04
1

@user9808783 You might want to edit your question with the details of your particular use case, and preferably the code you're using as well. – John Montgomery Aug 09 '18 at 21:40
@JohnMontgomery Thank you for your suggestion. I have edited the question and provided more details on the issue I am encountering – user9808783 Aug 10 '18 at 16:16

How to handle accents characters in Spanish websites in Mura

1 Answers1