Set value on foreign table - relation problems

293 Posts

Murray Reply #1, 12 years, 7 months ago

Hi,

I've got a table of galleries and a table of artworks. I'm trying to add 1 to the galleries 'artworktotal' field when a new artwork is created.
I have a feeling something may be wrong with my schema so hopefully someone can offer some advice.
The strange thing is that if I delete my database tables and build them from the schema again, it works the very first time and fails every other time.
When it doesn't work, firebug shows "uncaught exception: [object Object]"

I've extended the modObjectCreateProcessor and added the following function which is called from the public beforeSave() function.
The code appears to die when trying to getOne() on the 4th line down.

private function addToGalleryArtworkCount() {
    $galleryName = $this->getProperty('galleryname');
    $artwork = $this->modx->getObject('artworks', array('galleryname' => $galleryName));
    $gallery = $artwork->getOne('Gallery'); // WORKING IF VERY FIRST ENTRY INTO TABLE
    $gallery->set('artworktotal', $gallery->get('artworktotal')+1); // adds one to the total
    $this->modx->log(modX::LOG_LEVEL_DEBUG, 'CURRENT VALUE OF ARTWORK TOTAL: ' . $gallery->get('artworktotal'));
    $gallery->save();
}

My simplified schema is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<model package="sovereign" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">

    <object class="artworks" table="mycomponent_artworks" extends="xPDOSimpleObject">
        <field key="galleryname" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="filename" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="aname" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="pname" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true" />
        <field key="createdby" dbtype="int" precision="10" phptype="integer" attributes="unsigned" null="false" default="0" />
        <aggregate alias="Gallery" class="galleries" local="id" foreign="id" cardinality="one" owner="foreign" />
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign" />
    </object>
    
  
    <object class="galleries" table="mycomponent_galleries" extends="xPDOSimpleObject">
        <field key="galleryname" dbtype="varchar" precision="250" phptype="string" null="true" default="" />
        <field key="artworktotal" dbtype="int" precision="10" phptype="integer" null="true" default="0" />
        <field key="url" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="year" dbtype="varchar" precision="6" phptype="string" null="true" default="" />
        <field key="enabled" dbtype="int" precision="1" phptype="integer" null="true" default="0" />
        <field key="vote_on" dbtype="int" precision="1" phptype="integer" attributes="unsigned" null="true" default="" />
        <field key="votes" dbtype="int" precision="10" phptype="integer" null="true" default="0" />
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true" />
        <field key="createdby" dbtype="int" precision="10" phptype="integer" attributes="unsigned" null="true" default="0" />
        <composite alias="Artworks" class="artworks" local="id" foreign="id" cardinality="many" owner="local" />
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign" />
    </object>

      
</model>

Could it be something to do with the foreign and local keys?

This question has been answered by Bruno17. See the first response.

[ed. note: muzzstick last edited this post 12 years, 7 months ago.]

I'm lead developer at Digital Penguin Creative Studio in Hong Kong. https://www.digitalpenguin.hk
Check out the MODX tutorial series on my blog at https://www.hkwebdeveloper.com

5,888 Posts

Bruno17 Reply #2, 12 years, 7 months ago

I'd say it should be:

<?xml version="1.0" encoding="UTF-8"?>
<model package="sovereign" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
 
    <object class="artworks" table="mycomponent_artworks" extends="xPDOSimpleObject">
        <field key="aname" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="pname" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true" />
        <field key="createdby" dbtype="int" precision="10" phptype="integer" attributes="unsigned" null="false" default="0" />
        <field key="gallery_id" dbtype="int" precision="10" phptype="integer" attributes="unsigned" null="false" default="0" />
        <aggregate alias="Gallery" class="galleries" local="gallery_id" foreign="id" cardinality="one" owner="foreign" />
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign" />
    </object>
     
   
    <object class="galleries" table="mycomponent_galleries" extends="xPDOSimpleObject">
        <field key="galleryname" dbtype="varchar" precision="250" phptype="string" null="true" default="" />
        <field key="artworktotal" dbtype="int" precision="10" phptype="integer" null="true" default="0" />
        <field key="url" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="year" dbtype="varchar" precision="6" phptype="string" null="true" default="" />
        <field key="enabled" dbtype="int" precision="1" phptype="integer" null="true" default="0" />
        <field key="vote_on" dbtype="int" precision="1" phptype="integer" attributes="unsigned" null="true" default="" />
        <field key="votes" dbtype="int" precision="10" phptype="integer" null="true" default="0" />
        <field key="createdon" dbtype="datetime" phptype="datetime" null="true" />
        <field key="createdby" dbtype="int" precision="10" phptype="integer" attributes="unsigned" null="true" default="0" />
        <composite alias="Artworks" class="artworks" local="id" foreign="gallery_id" cardinality="many" owner="local" />
        <aggregate alias="CreatedBy" class="modUser" local="createdby" foreign="id" cardinality="one" owner="foreign" />
    </object>
 
       
</model>

and adding +1 each time you run the processor isn't the best option, I think.
This will result with wrong values, soon.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

293 Posts

Murray Reply #3, 12 years, 7 months ago

Thanks Bruno, I still seem to be having problems doing it this way but glad I've got the schema sorted out.

I've tried changing tact but now ExtJs is giving me headaches (such a steep learning curve!)

I'm attempting now to call a "getArtworkCount" processor from within my gallery grid and render the result in the appropriate field.

Here's my basic processor:

<?php
$galleryId = $scriptProperties['galleryId'];
$artworkCount = $modx->getCount('artworks', $galleryId);

$data['artworktotal'] = $artworkCount;
$response['success'] = true;
$response['msg'] = 'count returned';
$response['data'] = $data;

return $modx->toJSON($response);

and here's the ExtJs I'm using to call it. I know this is wrong but still searching around trying to find the correct way. I'm trying to call the processor and pass the galleryId from the grid's store . If anyone can help me with the extjs that's be fantastic wink

,{
    header: _('mycomponent.artwork_total')
    ,dataIndex: 'artworktotal'
    ,sortable: true
    ,editable: false
    ,width: 30
    ,renderer: function(){
        var grid = Ext.getCmp('mycomponent-grid-galleries');
        var galleryId = grid.getStore().get('id');
        var artworkCount = MODx.load({
            url: MyComponent.config.connectorUrl
            ,params: {action: 'mgr/mycomponent/getArtworkCount'}
            ,'galleryId': galleryId
        });
        return artworkCount;
    }
}

EDIT:

I changed the getStore() to store.getAt(0) and I'm now able to render a value. But it renders as "false" rather than the count. So I assume either the processor is not returning the JSON correctly, or I'm trying to get the value from the JSON incorrectly. Both are highly probable.

Updated extjs is:

,{
    header: _('mycomponent.artwork_total')
    ,dataIndex: 'artworktotal'
    ,sortable: true
    ,editable: false
    ,width: 30
    ,renderer: function(){
        var grid = Ext.getCmp('mycomponent-grid-galleries');
        var galleryId = grid.store.getAt(0).get('id');
        var artworkCount = MODx.load({
            url: MyComponent.config.connectorUrl
            ,params: {action: 'mgr/mycomponent/getArtworkCount'}
            ,'galleryId': galleryId
        });
        return artworkCount;
    }
}

[ed. note: muzzstick last edited this post 12 years, 7 months ago.]

I'm lead developer at Digital Penguin Creative Studio in Hong Kong. https://www.digitalpenguin.hk
Check out the MODX tutorial series on my blog at https://www.hkwebdeveloper.com

5,888 Posts

Bruno17 Reply #4, 12 years, 7 months ago

why not getting the artwokCount within the getlist-processor into an additional field.

Btw.: you may have less headache with extjs by trying to use MIGXdb to create your CMP.

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

293 Posts

Murray Reply #5, 12 years, 7 months ago

Cheers Bruno,

That was my original attempt but ran into problems trying to set the 'artworktotal' value as modObjectGetListProcessor doesn't seem to have a save() function.

For example with this function:

private function getArtworkCount() {
    $count = $this->modx->getCount('artworks');
    $this->modx->log(modX::LOG_LEVEL_DEBUG, 'The current value of count:' . $count);
    return $count;
}

The log shows that the count value is correct but I'm unsure of how to save this to the database.
I would normally use setProperty() and then save() but that won't work here.
The entire processor is here:

<?php
class GalleryGetListProcessor extends modObjectGetListProcessor {
    public $classKey = 'galleries';
    public $languageTopics = array('mycomponent:default');
    public $defaultSortField = 'id';
    public $defaultSortDirection = 'DESC';
    public $objectType = 'mycomponent';


    public function prepareQueryBeforeCount(xPDOQuery $c) {

        $query = $this->getProperty('query'); // Search box
        if (!empty($query)) {
            $c->where(array(
                'galleryname:LIKE' => '%'.$query.'%',
                'OR:description:LIKE' => '%'.$query.'%',
            ));
        }
        $this->setProperty('artworktotal', $this->getArtworkCount()); // get count of artworks
        return $c;
    }

    private function getArtworkCount() {
        $count = $this->modx->getCount('artworks');
        $this->modx->log(modX::LOG_LEVEL_DEBUG, 'The current value of count:' . $count);
        return $count;
    }


}
return 'GalleryGetListProcessor';

If you were meaning a different approach then I think I've misunderstood.

I've played around with migxdb and it's great! I'm just committed to trying to learn all this though wink

I'm lead developer at Digital Penguin Creative Studio in Hong Kong. https://www.digitalpenguin.hk
Check out the MODX tutorial series on my blog at https://www.hkwebdeveloper.com

discuss.answer

5,888 Posts

Bruno17 Reply #6, 12 years, 7 months ago

what about

    public function afterIteration(array $list) {
        $rows = array();
        foreach ($list as $row){
            $row['artworktotal'] = $this->getArtworkCount();
            $rows[] = $row;
        }
        
        return $rows;
    }

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

293 Posts

Murray Reply #7, 12 years, 7 months ago

Aha! It's working - I can stop pulling my hair out

Cheers again Bruno, I'll buy you a beer!

I'm lead developer at Digital Penguin Creative Studio in Hong Kong. https://www.digitalpenguin.hk
Check out the MODX tutorial series on my blog at https://www.hkwebdeveloper.com

293 Posts

Murray Reply #8, 12 years, 7 months ago

Uh oh, one more thing...

It works fine if there is no criteria specified like above

$count = $this->modx->getCount('artworks');

If I want to add criteria to the getCount function: for example only counting the artworks with a relevant 'galleryname'

The following doesn't work the way I would assume:

 private function getArtworkCount() {
    $galleryName = $this->getProperty('galleryname');
    $this->modx->log(modX::LOG_LEVEL_DEBUG, 'The current value of $galleryName:' . $galleryName); // log shows no value! 
    $count = $this->modx->getCount('artworks', array('galleryname' => $galleryName));
    $this->modx->log(modX::LOG_LEVEL_DEBUG, 'The current value of count:' . $count); // count is zero if this criteria is specified
    return $count;
}

Is there something about the getlist processor that will only allow getProperty to be used in certain functions?

I even tried hard coding a value that definitely exists into the getCount function and it still returned a count of zero e.g.:

$count = $this->modx->getCount('artworks', array('galleryname' => 'Gallery 1'));

So close! haha

I'm lead developer at Digital Penguin Creative Studio in Hong Kong. https://www.digitalpenguin.hk
Check out the MODX tutorial series on my blog at https://www.hkwebdeveloper.com

5,888 Posts

Bruno17 Reply #9, 12 years, 7 months ago

does this work?

public function afterIteration(array $list) {
    $rows = array();
    foreach ($list as $row){
        $row['artworktotal'] = $this->getArtworkCount($row['id']);
        $rows[] = $row;
    }
     
    return $rows;
}

private function getArtworkCount($gallery_id) {
    $count = $this->modx->getCount('artworks', array('gallery_id' => $gallery_id));
    $this->modx->log(modX::LOG_LEVEL_DEBUG, 'The current value of count:' . $count); // count is zero if this criteria is specified
    return $count;
}

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

☆ A M B ☆
24,524 Posts

sottwell Reply #10, 12 years, 7 months ago

I don't think you can have spaces like that (Gallery 1)

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

Answered Set value on foreign table - relation problems