Need Schema Help: Relating 2 Custom Tables, and the User Table

249 Posts

cyclissmo Reply #11, 15 years, 4 months ago

Your code looks fine, how is it being called?

lo9on.com

MODx Evolution/Revolution | Remote Desktop Training | Development

4 Posts

pureppl Reply #12, 15 years, 4 months ago

Quote from: smg6511 at Jun 09, 2011, 05:22 PM

Ahh, got ya. The tables were created on my first test calls to GetCollection.

Now in my testing environment, I’m having another interesting issue: I’m testing saving data to the "itineraries" table and I’m getting duplicate data written each time. Here’s the code for the test save:
$itinerary = $modx->newObject('saItinerary');
$itinerary->fromArray(array(
    'name' => 'Yet Another Test Itinerary',
    'userId' => $uid,
    'createdon' => time()
));
$itinerary->save();
Any idea why this might happen, given my schema (posted earlier in this thread)?

If you mean with "I’m getting duplicate data written each time." that an saItinerary with the name of ’Yet Another Test Itinerary’ is created each time you call the above script, then this is what should happen.

I guess you expect something like this:

// get a saItinerary with the name 'Yet Another Test Itinerary'
$itinerary = $modx->getObject('saItinerary', array('name' => 'Yet Another Test Itinerary'));
if(!$itinerary) {
  // create a saItinerary with the name 'Yet Another Test Itinerary'
  // because it does not exist.
  $itinerary = $modx->newObject('saItinerary');
  $itinerary->fromArray(array(
    'name' => 'Yet Another Test Itinerary',
    'userId' => $uid,
    'createdon' => time()
  ));
}
$itinerary->save();

99 Posts

smg6511 Reply #13, 15 years, 4 months ago

No, I’m saying for some reason each time I call the script the data is written twice ... as if I was loading the page (containing the test snippet) twice, but I’m only hitting in once.

------
Server: *nix, ModX Evo 1.0.4, Apache 2.x, PHP 5.x, Mysql 5.x.

4 Posts

pureppl Reply #14, 15 years, 4 months ago

Try this:

$itinerary = $modx->newObject('saItinerary');
$itinerary->fromArray(array(
    'name' => 'Yet Another Test Itinerary',
    'userId' => $uid,
    'createdon' => time()
),'',true,true);
$itinerary->save();

But its just a "tryout". If this works, i cant tell you why it works.

99 Posts

smg6511 Reply #15, 15 years, 4 months ago

Nope, that’s not it. Very bizarre. Before going too much further, let me see if I can replicate this on another server...

------
Server: *nix, ModX Evo 1.0.4, Apache 2.x, PHP 5.x, Mysql 5.x.

99 Posts

smg6511 Reply #16, 15 years, 4 months ago

Well, interestingly, I moved a copy of this to an external dev server all works as expected ... no duplication of data. So all things being equal (version of Revo and the schema/build files), my local version is the one with the dup issue. I can only surmise that there’s something awry in my local server config. Anyone seen something like this before?

------
Server: *nix, ModX Evo 1.0.4, Apache 2.x, PHP 5.x, Mysql 5.x.

99 Posts

smg6511 Reply #17, 15 years, 3 months ago

Quote from: smg6511 at Jun 03, 2011, 12:31 PM

<?xml version="1.0" encoding="UTF-8"?>

<model version="1.1" package="SA_modPlanner" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" phpdoc-package="SA_modPlanner" phpdoc-subpackage="model">

    <object class="saItinerary" table="itineraries" extends="xPDOSimpleObject">

        <field key="userId" dbtype="int" precision="5" phptype="integer" null="false" default="" />
        <field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
        <field key="createdon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="updatedon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" default="" />

        <index alias="userId" name="userId" primary="false" unique="false" type="BTREE">
            <column key="userId" collation="A" null="false" />
        </index>

        <composite alias="ItineraryItems" class="saItineraryItem" local="id" foreign="itineraryId" cardinality="many" owner="local" />
        <aggregate alias="User" class="modUser" local="userId" foreign="id" cardinality="one" owner="foreign" />

    </object>

    <object class="saItineraryItem" table="itinerary_items" extends="xPDOSimpleObject">

        <field key="itineraryId" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="source" dbtype="tinyint" precision="2" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="record" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="fkey" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="true" default="" />
        <field key="category" dbtype="smallint" precision="5" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="sdate" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="edate" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="stime" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="etime" dbtype="varchar" precision="10" phptype="string" null="true" default="" />
        <field key="notes" dbtype="varchar" precision="255" phptype="string" null="true" default="" />
        <field key="timestamp" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" default="" />
        <field key="createdon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" />
        <field key="updatedon" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="true" default="" />

        <index alias="itineraryId" name="itineraryId" primary="false" unique="false" type="BTREE">
            <column key="itineraryId" collation="A" null="false" />
        </index>

        <aggregate alias="Itinerary" class="saItinerary" local="itineraryId" foreign="id" cardinality="one" owner="foreign" />

    </object>

</model>

Another question, as I work further with the above schema: When running the following query

$c = $this->modx->newQuery('saItinerary');
$c->leftJoin('saItineraryItem');
$c->select(array('Itinerary.id'));
$c->where(array('Itinerary.userId'=>$uid));
$count = $this->modx->getCount('saItinerary',$c);

I get the error:
"No foreign key definition for parentClass: saItinerary using relation alias: saItineraryItem"

Which do I have wrong, the query or the schema?

P.S.: What I’d really like to have is an xPDO-equiv of the following mysql query:

SELECT COUNT(i.id) as total 
FROM rev205_itineraries as i LEFT JOIN rev205_itinerary_items as items 
ON i.id = items.itineraryId
WHERE i.userId = 1

------
Server: *nix, ModX Evo 1.0.4, Apache 2.x, PHP 5.x, Mysql 5.x.