CMP, Grid and Custom Table with modUser association

380 Posts

Brian Reply #1, 11 years, 9 months ago

Over the past week I have been making great progress going through the doodles tutorial (for 2.2) and creating a custom package/cmp with custom database tables.

I am getting a good understanding of how xpdo works and with the help of some amazing people on the forums have been able to simplify my code.

I have just about everything working as intended. One thing I am a bit baffled by is table associations. In one my my custom tables, I have a user_id column which has an modUser id in it. I would like to somehow query that table to show the users' full name rather then just the users' id number.

Can anyone possibly point me to an example that I can look at to figure out how one accomplishes this?

Thanks in advance

-Brian

380 Posts

Brian Reply #2, 11 years, 9 months ago

If anyone else is struggling with this, I found a very simple way to accomplish this.

Thanks to Mark Hamstra's amazing blog I came across this:

https://www.markhamstra.com/modx-blog/2012/04/modobjectgetlistprocessor-class-based-processor/

I added the following to the getlist processor which takes the user id, retrieves the fullname and then replaces it in the row.

    public function prepareRow(xPDOObject $object) {
        $row = $object->toArray('', false, true);
        $user = $this->modx->getObject('modUser',$row['user_id']);
        $profile = $user->getOne('Profile');
        $row['user_id'] = $profile->get('fullname');
        return $row;
    }

380 Posts

Brian Reply #3, 11 years, 9 months ago

One question I have now, which I don't see reference on Mark's blog is, is it possible to filter based on the modified value? Right now, the only way I can filter the data is if I use the user_id rather then the user's fullname.

This is in my action:

public function prepareQueryBeforeCount(xPDOQuery $c) {
        $query = $this->getProperty('query');
        if (!empty($query)) {
            $c->where(array(
                'trans_id:LIKE' => '%'.$query.'%',
                'OR:user_id:LIKE' => '%'.$query.'%',
            ));
        }
        return $c;
    }

380 Posts

Brian Reply #4, 11 years, 9 months ago

So It appears I will need to join the tables rather then modify the row. I have the following which doesn't product any additional output. (looking at the json in firebug)

public function prepareQueryBeforeCount(xPDOQuery $c) {       
        $c->innerJoin('modUserProfile','Profile', 'IYKT.user_id = Profile.internalKey');
        //search query
        $query = $this->getProperty('query');
        if (!empty($query)) {
            $c->where(array(
                'trans_id:LIKE' => '%'.$query.'%',
                'OR:Profile.fullname:LIKE' => '%'.$query.'%',
            ));
        }
        return $c;
    }

My schema is as follows

<object class="IYKT" table="iykt" extends="xPDOSimpleObject">
        <field key="user_id" dbtype="int" phptype="integer" null="false" default=""/>
        <field key="trans_id" dbtype="varchar" phptype="string" precision="255" null="false" default=""/>
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true"/>
        <field key="createdby" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
        <aggregate alias="Profile" class="modUserProfile" local="user_id" foreign="internalKey" cardinality="one" owner="foreign"/>
    </object>

EDIT

On further investigation it appears that my join is not working properly. the SQL produced via a snippet when a join is used is

string(596) "SELECT `IYKT`.`id` AS `IYKT_id`, `IYKT`.`user_id` AS `IYKT_user_id`, `IYKT`.`trans_id` AS `IYKT_trans_id`, `IYKT`.`createdon` AS `IYKT_createdon`, `IYKT`.`createdby` AS `IYKT_createdby` FROM `modx_iykt` AS `IYKT` JOIN `modx_user_attributes` `Profile` ON IYKT.user_id = Profile.internalKey "

$c = $modx->newQuery('IYKT');
$c->innerJoin('modUserProfile', 'Profile', array("IYKT.user_id = Profile.internalKey"));
$items = $modx->getCollection('ITYK',$c);
var_dump($c->toSql());

Is there a problem with my schema or is something else going on here?

EDIT 2

I after hours of troubleshooting I finally got the join to work properly. I needed to specifically select the fields I needed from the joined table (I also had to include the ID of the custom table)

public function prepareQueryBeforeCount(xPDOQuery $c) {

        $c->innerJoin('modUserProfile','Profile');
        $c->select('IYKT.id, Profile.fullname');

        $query = $this->getProperty('query');
        if (!empty($query)) {
            $c->where(array(
                'trans_id:LIKE' => '%'.$query.'%',
                'OR:Profile.fullname:LIKE' => '%'.$query.'%',
            ));
        }
        return $c;
    }

[ed. note: betoranaldi last edited this post 11 years, 9 months ago.]

9 Posts

Liquinaut Reply #5, 9 years, 3 months ago

This is very helpful indeed. Thank you posting your efforts!