SpillsThrills

Spilling Over an Eclectic Mix of Thoughts for Your Thrills.

SpillsThrills

Mura CMS Extended Attributes in a "Pivot" Table Using MySQL

November 30, 2013 · No Comments

This will be more of a "cookbook" blog post than a full out detailed post. This entry came about as I needed to return a list of members in Mura CMS that was being filtered against a very large list of custom extended attributes spread across multiple subtypes.   I really wanted to avoid having to load each userid and use the getValue() function in Mura.  I wanted a way to simpy say "return all users that have selected to receive invoices by email" or "show all member telephones where showTelephone is true".  My solution was to create a pivot table grouped by the baseids' in the tclassextenddatauseractivity table and all extended attributes from the tclassextendattributes table and their default values if they exist.  I am not sure how useful this will be to most of you as I often find with Mura CMS that there is most likely functionality already there to handle this.  If you do end up trying this just remember YMMV so please let me know if you find this useful or not.



transaction
    {
        var myQry = new query();
        var result = "";
        
        myQry.setName("extendedAttributePivotTable");                    
        myQry.setCachedWithin(createTimespan(0,0,60,0)); 

        /* You may want to adjust data length here depending on how 
         * large the resulting string is.*/
        myQry.setSQL("SET SESSION group_concat_max_len = 1000000;")  
        myQry.execute();
        // creates one big SQL statement for the query result
        myQry.setSQL("SELECT CONCAT('SELECT tc.baseid,'
                                    , GROUP_CONCAT(SelectSQLText)
                                    , ' FROM tclassextendattributes t 
                                            LEFT OUTER JOIN  tclassextenddatauseractivity tc
                                                ON tc.attributeid  = t.attributeid
                                        GROUP BY tc.baseid'
                                    ) 
                             AS theSQL
                        FROM ( SELECT DISTINCT CONCAT(' MAX(CASE 
                                                                WHEN t.attributeid = ', t.attributeid, ' 
                                                                    AND NOT tc.attributeValue IS NULL
                                                                    THEN tc.attributeValue
                                                                ELSE ''', COALESCE(t.defaultvalue,''), '''    
                                                            END) 
                                                         AS `', t.name, '`'
                                                      ) 
                                       AS SelectSQLText
                                    FROM tclassextendattributes t    
                                    LEFT OUTER JOIN  tclassextenddatauseractivity tc
                                                ON tc.attributeid  = t.attributeid
                              )  
                        AS c; ");
        
        //get the resulting SQL statement
        result = myQry.execute().getResult();

        //execute the prepared statement
        myQry.setSQL(result["theSQL"][1]);
        result = myQry.execute().getResult();
    }
        writeDump(result);
        abort;


Tags: Mura CMS · Railo

0 responses so far ↓

  • There are no comments yet...Kick things off by filling out the form below.

Leave a Comment

Leave this field empty: