Help with adding a custom table

First, let me point out my version: MODX Revolution 2.2.4-pl (June 14, 2012)

Okay, so now, what I'm trying to do is add a foreign key from my custom table, back to modx_site_content.

Here goes:

In modx_site_content, we have individual rows of tags. These are search keywords that we use to crawl twitter. The results from twitter, get stored in there as well, every things working great! Now, I'm tasked with finding a way to actually track the keywords. They want to know over time, what keywords are bringing back results, which ones aren't. My plan, which so far is somewhat working, was to create a new table, called "twitter_dataresults"

Structure looks like the following:



Simply, results will contain how many twitter results were returned for that particular search phrase. Createdon is the date.

Now, query_id is where I'm stuck, this is the foreign key that should map back to modx_site_content id for that particular search keyword.

Following this example, this is where I'm at: http://bobsguides.com/custom-db-tables.html

Here is my schema for the table:



Now, when I run the script to magically create the classes, etc, I can search or query data in that table, but it's not displaying the associated foreign key data.

Lastly, do I need to update the schema for modx_site_content to reflect the new table? Long term wise, I'd like to be able to view various keywords, and see all associated responses over time, to determine if the keywords are worth having or not.

I feel like I'm close, but have a feeling i'm not setting the aggregate alias correctly.

It looks right to me. When you have the Dataresults object, you should be able to get the Resource like this:



Be sure you're calling loadClass() before that code executes, and it's a good idea to check the results of loadClass(). It returns false on failure. Also, you have to regenerate the class and map files whenever you change the schema.

Quote from: BobRay at Jan 26, 2013, 12:13 AM

It looks right to me. When you have the Dataresults object, you should be able to get the Resource like this:

$dr = $modx->getObject('DataResults', $criteria);
if ($dr) {
    $doc = $dr->getOne('Resource');
    $title = $doc->get('pagetitle');
} else {
  return 'Doc not found';
}

Be sure you're calling loadClass() before that code executes, and it's a good idea to check the results of loadClass(). It returns false on failure. Also, you have to regenerate the class and map files whenever you change the schema.

Amazing, it's working. Thank you so much for your quick feedback.

Secondly, if I would want to run a query on a certain Keyword in modx_site_content, would that require updating the schema for that table?

I'm not sure I understand the question, but I don't think so unless you want to create another alias for your class.

Quote from: BobRay at Jan 26, 2013, 04:56 PM

I'm not sure I understand the question, but I don't think so unless you want to create another alias for your class.

Sorry, let me clarify.

So the custom table I created, which has a foreign key back to modx_site_content. I'm curious if I need to update the schema so if I run a query on a various entry in the table, it returns all related entries from the custom table I created.

I guess, worst case, just write a regular query with a join to get that data from twitter_dataresults

I get it. I think if you want to grab the info from the custom field with $resource->getOne(), you'd need to modify the modResource schema (highly discouraged), but you can always do a join in an xPDO query.

You could write a plugin that would add the related object ID into an unused Resource field when a resource is saved (if that makes sense).

There may be another way, but I don't know it.

Gotcha, makes sense. I think for now, I'll just run a join via xPDO query.

Thanks for all your help!