We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Selecting with MYSQL and REGEXP function

    • 7327
    • 195 Posts
    diatomin Reply #1, 15 years, 9 months ago
    hey guys,

    This is more of a MySQL problem, but since I’m using it for a custom snippet in my project involving MODx, here goes...

    I’ve got a 2-column simple table that’s used to manage REGIONS (id, region). This basically contains names of cities. I’ve connected this table with my forms, WinLoginPE, and a custom manager script, now I plan to connect this with a TV list, which I’ve done so using the @SELECT binding. So far so good.

    I’ve designed it where I add SALES REPS as children of a container and the template for this resource uses the TV list of regions.

    So if we look at the modx_site_tmplvar_contentvalues table, we can see that our contentid’s of interest contain the regions, delimited by ’||’. Right now, I need to extract these based on content IDs of interest, and that’s where I need the parent ID to narrow things down in the modx_site_content table.

    To illustrate what I’ve got in my tables:

    modx_regions:
    [table][tr][td]ID[/td][td]REGION[/td][/tr]
    [tr][td]1[/td][td]New York[/td][/tr]
    [tr][td]2[/td][td]Washington[/td][/tr]
    [tr][td]3[/td][td]Midleton[/td][/tr]
    [/table]

    modx_site_tmplvar_contentvalues: (Just citing the fields of interest)
    [table][tr][td]ID[/td][td]CONTENTID[/td][td]VALUE[/td][/tr]
    [tr][td]17[/td][td]45[/td][td]New York[/td][/tr]
    [tr][td]18[/td][td]46[/td][td]Washington||Midleton[/td][/tr]
    [tr][td]19[/td][td]47[/td][td]Midleton[/td][/tr]
    [/table]

    What I need to output is really a list of all REGIONS listed in my custom table, and map SALES REPS (which are document resources in MODx) based on what’s selected in the Region TV listing. The output table I’d like to have is the following:

    DOC RESOURCE ID, BUSINESS REGION NAME, SALES REP (or DOC RESOURCE PAGE TITLE)
    [table][tr][td]DOC RESOURCE ID[/td][td]REGION NAME[/td][td]PAGETITLE[/td][/tr]
    [tr][td]45[/td][td]New York[/td][td]Sales Rep 1[/td][/tr]
    [tr][td]46[/td][td]Washington[/td][td]Sales Rep 2[/td][/tr]
    [tr][td]47[/td][td]Midleton[/td][td]Sales Rep 2[/td][/tr]
    [tr][td]48[/td][td]Midleton[/td][td]Sales Rep 3[/td][/tr]
    [/table]

    So far, I’ve almost gotten things to work via the following SQL query:

    select `modx_site_content`.id, `modx_business_regions`.region, `modx_site_content`.pagetitle 

from `modx_site_tmplvar_contentvalues`, `modx_business_regions`, `modx_site_content` 

where 

`modx_site_content`.parent ='9' 
AND `modx_site_content`.id = `modx_site_tmplvar_contentvalues`.contentid 
AND `modx_site_tmplvar_contentvalues`.value REGEXP `modx_business_regions`.region

order by pagetitle, region


    What I need help in is how to add conditions to the part: REGEXP `modx_business_regions`.region because right now, if you execute that code, there are no problems with entries WITHOUT SYMBOLS (like brackets and apostrophes) AND SPACES. Those show, but I’ve noticed that those WITH SYMBOLS AND SPACES don’t show.

    My question is: is there a way to add additional REGEX conditions to something that’s taken as MySQL "variable"? I mean, `modx_business_regions`.region isn’t a literal string, which REGEX takes.
      • 7327
      • 195 Posts
      diatomin Reply #2, 15 years, 9 months ago
      I think I solved it -_-, by changing the REGEX value from:

      modx_venus_business_regions`.region

      TO

      CONCAT(`modx_venus_business_regions`.region, ’*’)

      Though I’ll give updates if things didn’t work out as expected.

      Hope anyone else finds this useful and if anyone else can refine this, just post it in.
        • 7327
        • 195 Posts
        diatomin Reply #3, 15 years, 9 months ago
        One question - Since I plan to use MODx’ built in API when creating URLs ($url = $modx->makeUrl($whatever_doc_id)), and there are several hundreds of these regions, how much impact in performance will this take up?

        I’m using MODx 1.0.4 btw.
          • 7327
          • 195 Posts
          diatomin Reply #4, 15 years, 9 months ago
          Ok, it seems I’m just almost there. the concatenated * didn’t resolve any region name that had something else in the front. So if the selected Region’s just "New York", and the region list had "New York (upper)", and "New York (lower)" on top of "New York", it will also include these two in the select results.

          Update: not the case. A sales rep had "New York (upper)" and the entire New York (whatever) is being attributed to the sales rep. I think the brackets are causing an issue and needs to be escaped... Does anyone know how to do this straight from sql?
            • 7327
            • 195 Posts
            diatomin Reply #5, 15 years, 9 months ago
            Ok, after debugging the statement, I found out that concatenating brackets needs to be escaped first.

            I also found that I needed to concat ’||’ to the start and end of the modx_site_tmplvar_contentvalues.value, then use this as part of the condition in the regex to look for entries properly.

            The working SQL query is the following:

            select `modx_site_content`.id, `modx_business_regions`.region, `modx_site_content`.pagetitle 

from `modx_site_tmplvar_contentvalues`, `modx_business_regions`, `modx_site_content` 

where 

`modx_site_content`.parent ='9' 
AND `modx_site_content`.id = `modx_site_tmplvar_contentvalues`.contentid 
AND CONCAT('||',`modx_site_tmplvar_contentvalues`.value,'||') REGEXP CONCAT('[\|]', REPLACE(REPLACE(`modx_business_regions`.region, '\(','\\('), '\)', '\\)'), '[\|]')

order by pagetitle, region