3rd party db/table support needed

Just like with this post (http://modxcms.com/forums/index.php?PHPSESSID=4148811bcf908d343c838008ecd1e77a&/topic,17980.0.html) I have tried for 2 days to get this query to work without xPDOCriteria and am hoping to get this working and then back into getting it all working as objects. The query statement itself as is returned by $query->toSql() works in mysql directly. So I have a good query and I can also retrieve data from a single table under getCollection(). I assume my schema relationships are to blame (everything else was created through reverse engineering script under xpdo docs) but having a hard time getting my head around them.

Also this is a "local to server" 3rd party DB for which I am accessing all the needed tables through Views of the same table name to remain under single connection. DB is provided by 3rd party vendor and I have no control over naming, relationship, etc etc. It only used as a read-only data source.

Any help in how I can get this working and/or properly call it under proper xpdo object calls would be greatly appreciated.

If it helps here is a link to the full DB model (just uploaded copy this is not the running source)

http://ethannewmedia.com/xpdo/autodataEVD/



$query= new xPDOCriteria($modx,’
SELECT
VT05_Trim.YearCode, VT50_ModelYear.ModelYearID, VT04_Model.ModelId, VT04_Model.DivCode, VT04_Model.ModelDesc as model, VT05_Trim.BodyStyle,
VT05_Trim.Acode as acode
FROM VT04_Model
LEFT JOIN VT50_ModelYear ON (VT50_ModelYear.ModelID = VT04_Model.ModelID)
LEFT JOIN VT05_Trim ON (VT05_Trim.ModelID = VT04_Model.ModelID)
WHERE VT04.DivCode = :divCode
AND VT04_Model.LngCode = :lngCode
AND VT04_Model.CountryCode = :cntryCode
AND VT50_ModelYear.YearCode = :yrCode
AND VT05_Trim.Estimated != :est
AND VT05_Trim.YearCode = VT50.YearCode
GROUP BY VT05_Trim.YearCode,VT04_Model.ModelDesc,VT05_Trim.BodyStyle
ORDER BY VT05_Trim.YearCode DESC, VT04_Model.ModelDesc ASC,VT05_Trim.BodyStyle ASC
’,array(
’:lngCode’ => ’EN’,
’:cntryCode’ => ’US’,
’:divCode’ => ’HO’,
’:yrCode’ => ’C10’,
’:est’ => ’Y’
));

$results = $modx->getCollection(’VT04Model’,$query);

This simply returns an empty array.

Thanks everyone/anyone

Adam

Not sure I understand why you are hand crafting this query to return a collection of xPDOObjects. You can’t instantiate objects from custom result sets like that unless you get all the data just right. They must have all the proper primary and foreign key fields, etc. In such cases you can use the getObjectGraph()\getCollectionGraph() methods to pull back a graph of related objects. But in this case, I think you need to just use the PDOStatement that is prepared by xPDOCriteria to get a result set and go through the rows doing what you will with the data.

I see. I was trying to follow another post that had it setup similar to the and was using the getCollection.

If I just prepare the state $query->prepare(); how can I just execute the query as you have suggested.

Quote from: aesmith at Apr 20, 2011, 04:16 PM

If I just prepare the state $query->prepare(); how can I just execute the query as you have suggested.

The PDOStatement is returned (and saved to the xPDOCriteria instance) immediately. You can simply reference it:

ic thank you very much. I was trying to find something like this in the api docs but was looking under modx->pdo ... I didn’t see the pull down in the upper right corner to get xpdo specific api docs so that I could understand xPDOCriteria. Thanks a million.