Help needed with left-inner join combo with ActiveQuery

Yii 2.0
1

This is what I essentially need to do (https://stackoverflow.com/questions/28590173/sql-inner-joining-to-left-joined-table) and like this (https://stackoverflow.com/questions/9685289/using-left-join-and-inner-join-in-the-same-query#answer-56815807).

What is happening is I have a taxonomy table, taxonomy_term table, content table, and junction table content_taxonomy_term. I need to get all terms for a specific taxonomy for a specific piece of content. So, I want to left join content_taxonomy_term and taxonomy_term and inner join taxonomy_term to taxonomy so that it only queries for terms that are for ‘categories’ for example. I have been trying to set up relations on the Content model so that I can get all ‘categories’ but I can’t seem to set it up properly using hasMany and am struggling to figure out how to do this at all.

2

Hi @Accelm,

Would you please show us the ERD?

3

Create two relations linked by “via”

class Content extends ActiveRecord
{
    /**
     * @return \yii\db\ActiveQuery
     */
    public function getContentTaxonomyTermList()
    {
        return $this->hasMany(ContentTaxonomyTerm::className(), ['content_id' => 'content_id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getTaxonomyTermList()
    {
        return $this->hasMany(TaxonomyTerm::className(), ['taxonomy_term_id' => 'taxonomy_term_id'])->via('contentTaxonomyTermList');
    }
}

For example:

$content = Content::findOne(...);
$taxonomyTermList = $content->taxonomyTermList;
4

@softark the ERD is as follows

content
content_taxonomy_term
taxonomy_term
taxonomy

content:taxonomy_term is n:m and content_taxonomy_term is the junction table for that with a content_id column and a taxonomy_term_id column.

taxonomy_term:taxonomy is 1:n and the taxonomy_term table has a column taxonomy_id that relates the term to a taxonomy.

Screenshot from 2020-05-28 15-03-05
Screenshot from 2020-05-28 15-03-051129×483 56.3 KB

@fabriziocaldarelli that hasn’t worked for me because it is not forming the query properly. This is how I need the query formed:

SELECT * 
FROM `content`
LEFT JOIN `content_taxonomy_term` ON `content_taxonomy_term`.`content_id`=`content`.`id`
LEFT JOIN `taxonomy_term`
	INNER JOIN `taxonomy` ON `taxonomy`.`id`=`taxonomy_term`.`taxonomy_id`
		AND `taxonomy`.`slug`='category'
	ON `taxonomy_term`.`id`=`content_taxonomy_term`.`taxonomy_term_id`;

Since I have multiple taxonomies (tag, category, etc) I need a way to query for taxonomy terms that are categories (or tags) only. If I just do a left join it will return terms for all taxonomies. The links that I shared in my original post were what I had found that would allow me to do what I am trying to do, but I am open to other options!

My issue is that I would like to use Yii’s ActiveRecord relational functions to get all ‘categories’ and ‘tags’ for content and have them under the appropriate relation. But so far it does not seem like I can properly form the query using their methods. I might just have to do an extra query to get the taxonomy_id for the taxonomy and then use that value in the ON condition that joins the taxonomy_term table and avoid doing the extra join to the taxonomy table. It’s not ideal but I haven’t been able to figure out a way to do it properly for a few days at this point.

5

So, the relations among C(Content), CTT(ContentTaxonomyTerm), TT(TaxonomyTerm) and T(Taxonomy) are:

  1. C has many CTTs / CTT has one C
  2. CTT has one TT / TT has many CTT
  3. C has many TT via CTT / TT has many C via CTT
  4. TT has one T / T has many TT

Well, I’ve noticed that TaxonomyTerm could have one parent TaxonomyTerm … this could be a headache. As for now, I’d like to ignore it in favor of simplicity.

Since content_taxonomy_term is just a junction table, the essential relations are #3 and #4.

As @fabriziocaldarelli says, #3 could be defined using #1. Or you could define it like the following using viaTable:

public function getTaxonomyTerms()
{
    return $this->hasMany(Item::className(), ['id' => 'taxonomy_term_id'])
        ->viaTable('content_taxonomy_term', ['content_id' => 'id']);
}

As stated in the Guide (Active Record > Relational Active Record > Dynamic Relational Query) (https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#dynamic-relational-query):

Because a relation method returns an instance of yii\db\ActiveQuery, you can further build this query using query building methods before performing DB query

In your case, you could do something like this:

$content = Content::findOne(123);
$taxonomyTermsOfSlug = $content->getTaxonomyTerms()
    ->innerJoin('taxonomy', 'taxonomy.id = taxonomy_term.taxonomy_id')
    ->where(['taxonomy.slug' => $slug])
    ->orderBy('taxonomy_term.name')
    ->all();

And you could declare a shortcut relation for it:

public function getTaxonomyTermsOfSlug($slug)
{
    return $this->getTaxonomyTerms()
        ->innerJoin('taxonomy', 'taxonomy.id = taxonomy_term.taxonomy_id')
        ->where(['taxonomy.slug' => $slug]);
}