Trouble replicating wordpress taxonomy table query

Question

I'm trying to forgo some wordpress overhead and query the databases directly using lithium model relationships.

Here's the query I'm replicating:

SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (<list of id's>) ORDER BY t.name ASC

This is the relationship if I understand it correctly:

wp_term_relationships belongs to wp_term_taxonomy, which belongs to wp_terms.

Here's how I've setup my models:

class Terms extends  \lithium\data\Model{

    public $_meta = array(
        'source' => 'wp_terms',
        'key' => 'term_id'
    );

    protected $_schema = array(
        'term_id'    => array('type' => 'id'),
        'name'       => array('type' => 'string'),
        'slug'       => array('type' => 'string'),
        'term_group' => array('type' => 'int')
    );

    public $hasOne =  array(
        "TermsTaxonomies" => array(
            "to"  => 'app\models\TermsTaxonomies',
            "key" => "term_id",
        )
    );

}

class TermsTaxonomies extends  \lithium\data\Model{

    public $_meta = array(
        'source' => 'wp_term_taxonomy'
    );

    protected $_schema = array(
        'term_taxonomy_id' => array('type' => 'id'),
        'term_id'          => array('type' => 'int'),
        'taxonomy'         => array('type' => 'string'),
        'description'      => array('type' => 'string'),
        'parent'           => array('type' => 'int'),
        'count'            => array('type' => 'int')
    );

    public $belongsTo = array(
        "Terms" => array(
            "to"  => 'app\models\Terms',
            "key" => "term_id",
        )
    );

}

class TermsRelationships extends  \lithium\data\Model{

    public $_meta = array(
        'source' => 'wp_term_relationships'
    );

    protected $_schema = array(
        'object_id'        => array('type' => 'id'),
        'term_taxonomy_id' => array('type' => 'int'),
        'term_order'       => array('type' => 'int')
    );

    public $belongsTo = array(
        "TermsTaxonomies" => array(
            "to" => 'app\models\TermsTaxonomies',
            "key" => "term_taxonomy_id",
        )
    );

}

I get a "Model relationship TermTaxonomies not found." error when I run this query:

$terms = Terms::find('all', array(
    "conditions" => array(
        "TermTaxonomies.taxonomy" => "category",
        "TermRelationships.object_id" => array(8489)
    ),
    "with" => array(
        "TermTaxonomies", "TermRelationships"
    )
));

Needless to say, I'm fairly certain that I don't have a correct grasp on Lithium Model Relationships.

Answer 1

The immediate "Model relationship TermTaxonomies not found" error that you are seeing is due a typo.

You have named your models TermsTaxonomies and TermsRelationships while Terms::find is looking for "TermTaxonomies" and "TermRelationships". Update your "with" statement to use the correct class names.

It may be instructive to consult a diagram of the tables under discussion: http://codex.wordpress.org/images/9/9e/WP3.0-ERD.png

In each model you can make your keys explicit, then the define the relationships between each model. I've left out the schemas as it has no bearing on your question.

For the Terms model:

<?php

namespace app\models;

class Terms extends \lithium\data\Model {

    public $_meta = array(
        'source' => 'wp_terms',
        'key' => 'term_id'
    );

    public $hasMany =  array(
        "TermsTaxonomies" => array(
            "to"  => 'app\models\TermsTaxonomies',
            "key" => "term_id",
        ),
        "TermsRelationships" => array(
            "from" => "app\models\TermsTaxonomies",
            "to" => "app\models\TermsRelationships",
            "key" => array(
                "term_id" => "term_taxonomy_id"
            ),
        ),
    );

}

?>

For the TermsTaxonomies model:

<?php

namespace app\models;

class TermsTaxonomies extends \lithium\data\Model {

    public $_meta = array(
        'source' => 'wp_term_taxonomy',
        'key' => 'term_taxonomy_id'
    );

    public $belongsTo = array(
        "Terms" => array(
            "to"  => 'app\models\Terms',
            "key" => array(
                "term_id" => "term_id",
            ),
        ),
    );

    public $hasMany = array(
        "TermsRelationships" => array(
            "to" => "app\models\TermsRelationships",
            "key" => array(
                "term_taxonomy_id" => "term_taxonomy_id"
            ),
        ),
    );

}

?>

For the TermsRelationships model:

<?php

namespace app\models;

class TermsRelationships extends \lithium\data\Model {

    public $_meta = array(
        'source' => 'wp_term_relationships',
        'key' => array('object_id', 'term_taxonomy_id'),
    );

    public $belongsTo = array(
        "TermsTaxonomies" => array(
            "to" => 'app\models\TermsTaxonomies',
            "key" => array(
                "term_taxonomy_id" => "term_taxonomy_id"
            ),
        ),
        "Terms" => array(
            "from" => "app\models\TermsTaxonomies",
            "to" => "app\models\Terms",
            "key" => array(
                "term_taxonomy_id" => "term_id"
            ),
        ),
    );
}

?>

Finally in your Controller:

$terms = Terms::find('all', array(
    "conditions" => array(
        "TermsTaxonomies.taxonomy" => "category",
        "TermsRelationships.object_id" => array(8489)
    ),
    "with" => array(
        "TermsTaxonomies",
        "TermsRelationships"
    )
));