Trouble replicating wordpress taxonomy table query

3 Views

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

Here’s the query I’m replicating:

Read More
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  lithiumdataModel{

    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"  => 'appmodelsTermsTaxonomies',
            "key" => "term_id",
        )
    );

}

class TermsTaxonomies extends  lithiumdataModel{

    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"  => 'appmodelsTerms',
            "key" => "term_id",
        )
    );

}

class TermsRelationships extends  lithiumdataModel{

    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" => 'appmodelsTermsTaxonomies',
            "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.

Post Views: 3

Related posts

Leave a Reply

1 comment

  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 appmodels;

class Terms extends lithiumdataModel {

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

    public $hasMany =  array(
        "TermsTaxonomies" => array(
            "to"  => 'appmodelsTermsTaxonomies',
            "key" => "term_id",
        ),
        "TermsRelationships" => array(
            "from" => "appmodelsTermsTaxonomies",
            "to" => "appmodelsTermsRelationships",
            "key" => array(
                "term_id" => "term_taxonomy_id"
            ),
        ),
    );

}

?>

    For the TermsTaxonomies model:

    <?php

namespace appmodels;

class TermsTaxonomies extends lithiumdataModel {

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

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

    public $hasMany = array(
        "TermsRelationships" => array(
            "to" => "appmodelsTermsRelationships",
            "key" => array(
                "term_taxonomy_id" => "term_taxonomy_id"
            ),
        ),
    );

}

?>

    For the TermsRelationships model:

    <?php

namespace appmodels;

class TermsRelationships extends lithiumdataModel {

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

    public $belongsTo = array(
        "TermsTaxonomies" => array(
            "to" => 'appmodelsTermsTaxonomies',
            "key" => array(
                "term_taxonomy_id" => "term_taxonomy_id"
            ),
        ),
        "Terms" => array(
            "from" => "appmodelsTermsTaxonomies",
            "to" => "appmodelsTerms",
            "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"
    )
));