Convert a DB Query to Modx

654 Posts

mconsidine Reply #21, 13 years, 3 months ago

Ah, okay, got it. When I ran Bob’s snippet I must have overlooked an edit, because I ended up with a boatload of files that were related instead to the MODX database. I’m not sure that I ever saw the map and class files created, though the schema was there. Anyway, it just made sense for me to delete what was created and start over. Also, I was not manually editing the schema to add the kinds of relationships it sounds like you need.

Matt

151 Posts

jaredloman Reply #22, 13 years, 3 months ago

So now, I’m pretty confident that I have parsed my schema. I had to completely comment out the createSchema() function from Bob’s snippet in order to get it to not overwrite my current schema.

So..Bob..at least in my situation &createSchema = ’0’ did not work. I even further tested it by adding a conditional to the function that would always inevitably evaluate false (e.g. if($createSchema = "pigLegs") {... ), and that still overwrote my schema. The only thing I could do to get it to bypass that was to comment it out. Not sure if it is something specific to me, or what? Is this confirmed working for you or anyone else?

I’m not however fully confident that my relationships are correctly established. They seem to make sense, but who knows? As suggested by SplittingRed... I did a composite/aggregate relationship (or so I think I did). Does this look off at all?

<?php // <-- Just here for code coloring
	<object class="Product" table="product" extends="xPDOObject">
		<field key="product_id" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="model" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="sku" dbtype="varchar" precision="64" phptype="string" null="false" />
		<field key="location" dbtype="varchar" precision="128" phptype="string" null="false" />
		<field key="quantity" dbtype="int" precision="4" phptype="integer" null="false" default="0" />
		<field key="stock_status_id" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="image" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="manufacturer_id" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="shipping" dbtype="int" precision="1" phptype="integer" null="false" default="1" />
		<field key="price" dbtype="decimal" precision="15,4" phptype="float" null="false" default="0.0000" />
		<field key="tax_class_id" dbtype="int" precision="11" phptype="integer" null="false" />
		<field key="date_available" dbtype="date" phptype="date" null="false" />
		<field key="weight" dbtype="decimal" precision="5,2" phptype="float" null="false" default="0.00" />
		<field key="weight_class_id" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
		<field key="length" dbtype="decimal" precision="5,2" phptype="float" null="false" default="0.00" />
		<field key="width" dbtype="decimal" precision="5,2" phptype="float" null="false" default="0.00" />
		<field key="height" dbtype="decimal" precision="5,2" phptype="float" null="false" default="0.00" />
		<field key="length_class_id" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
		<field key="status" dbtype="int" precision="1" phptype="integer" null="false" default="0" />
		<field key="date_added" dbtype="datetime" phptype="datetime" null="false" default="0000-00-00 00:00:00" />
		<field key="date_modified" dbtype="datetime" phptype="datetime" null="false" default="0000-00-00 00:00:00" />
		<field key="viewed" dbtype="int" precision="5" phptype="integer" null="false" default="0" />
		<field key="sort_order" dbtype="int" precision="11" phptype="integer" null="false" default="0" />
		<field key="subtract" dbtype="int" precision="1" phptype="integer" null="false" default="1" />
		<field key="minimum" dbtype="int" precision="11" phptype="integer" null="false" default="1" />
		<field key="cost" dbtype="decimal" precision="15,4" phptype="float" null="false" default="0.0000" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="product_id" length="" collation="A" null="false" />
		</index>
		
		<composite alias="ProductDescription" class="ProductDescription" local="product_id" foreign="product_id" cardinality="many" owner="local" />
	</object>
	<object class="ProductDescription" table="product_description" extends="xPDOObject">
		<field key="product_id" dbtype="int" precision="11" phptype="integer" null="false" index="pk"  generated="native" />
		<field key="language_id" dbtype="int" precision="11" phptype="integer" null="false" index="pk" />
		<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" index="index" />
		<field key="meta_keywords" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="meta_description" dbtype="varchar" precision="255" phptype="string" null="false" />
		<field key="description" dbtype="text" phptype="string" null="false" />

		<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true" type="BTREE" >
			<column key="product_id" length="" collation="A" null="false" />
			<column key="language_id" length="" collation="A" null="false" />
		</index>
		<index alias="name" name="name" primary="false" unique="false" type="BTREE" >
			<column key="name" length="" collation="A" null="false" />
		</index>
		
		<aggregate alias="Product" class="Product" local="product_id" foreign="product_id" cardinality="one" owner="foreign" />
	</object>

Aside from this, I’m at a complete loss.. maybe it has something to do with my query?

Thanks!
-Jared

24,544 Posts

BobRay Reply #23, 13 years, 3 months ago

I see what’s happening with the schema file. It’s a bug [fixed]. You should still be able to use $createSchema = false in the code as long as you comment out the line that sets that value.

I think your next-to last line should be a composite alias since it’s not a many-to-many relationship (which would involve three tables, not two).

I don’t think you need a join at all if I’m understanding the setup.

Fix that, regenerate the map and try this:

$product = $modx->getObject('modProduct', 1);  // assuming that there's a product with that ID.

$description = $product->getOne('Description');

$output = '<br />Model: ' . $produce->get('model') . '<br />Description: ' . $description->get('description');

return $output;

If that works, you should be able to use getObjectGraph() and getCollectionGraph() to get data from both tables in a single query.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

151 Posts

jaredloman Reply #24, 13 years, 3 months ago

I’m absolutely speechless... It works!

I don’t fully understand "why" it works...but it works.

I can hardly wait for the Docs (& Bob’s Guides grin

) to be filled with more info on this stuff so I can wrap my brain around it better. I’d really like to get a better grasp on these relationships.

I’m going to have to do some debugging work so I can piece together how this all happens in the mean time.

Again, I’m absolutely speechless.. Thank you kindly for your persistence! I have learned SO much!

-Jared

24,544 Posts

BobRay Reply #25, 13 years, 3 months ago

Thanks for the kind words, but the credit really belongs to OpenGeek for creating xPDO.

Let’s go one step further:

<?php
/* The third argument here can be any specified criterion or an xPDO query */
$product = $modx->getObjectGraph('modProduct', array('Description' => array()), array('product_id' => '1'));

$output = '<br />Model: ' . $product->get('model');
$output .= '<br />Description: ' . $Description->get('description');
return $output;

or -- for a collection of them:

<?php
$products = $modx->getCollectionGraph('modProduct', array('Description' => array()), array('location' => 'London'));

foreach ($products as $product) {
$output = '<br />Model: ' . $product->get('model');
$output .= '<br />Description: ' . $Description->get('description');
}
return $output;

As frosting, say that you have a Tpl chunk called productTpl that looks something like this:

<p><b>Product</b/</p>
<p>Name: [[+name]</p>
<p>Model: [[+model]</p>
<p>Description: [[+description]]</p>
<p>Price: [[+price]]</p>
<p> -----------------------------</p>

And you want to show the products available in the ’London’ location:

<?php
$products = $modx->getCollectionGraph('modProduct', array('Description' => array()), array('location' => 'London'));

foreach ($products as $product) {
$fields = array_merge($product->toArray(), $Description->toArray());
return $modx->getChunk('productTpl',$fields);
}

:)

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting