<p>I found the solution and posted it <a href="https://craftcms.stackexchange.com/questions/1086/order-tags-by-count-e-g-top-10-tags-used/36066#36066">here</a> in another issue related to this problem.</p>
<p>Here is my solution (just the code):</p>
<pre><code>$query = (new Query())
    -&gt;select([
        '{{relations}}.targetId as id',
        'COUNT({{relations.targetId}}) as count',
        '{{content}}.title as title'
    ])
    -&gt;from('relations')
    -&gt;leftJoin('tags', '{{relations}}.targetId={{tags}}.id')
    -&gt;leftJoin('content', '{{content}}.elementId={{tags}}.id')
    -&gt;leftJoin('elements', '{{elements}}.id={{tags}}.id')
    -&gt;leftJoin('elements source', '{{source}}.id={{relations}}.sourceId')
    -&gt;where([
        '{{elements}}.enabled' =&gt; 1,
        '{{source}}.enabled' =&gt; 1,
        '{{source}}.revisionId' =&gt; NULL,
        '{{source}}.draftId' =&gt; NULL,
    ])
    -&gt;groupBy('id, title')
    -&gt;orderBy( 'count DESC, title ASC')
;

$query-&gt;limit($limit);

if (is_numeric($limit) &amp;&amp; !is_null($limit)) {
    $query-&gt;limit($limit);
}

$results = $query-&gt;all();

foreach ($results as $key =&gt; $value) {
    $id = $value['id'];
    $results[$key] = Tag::find()-&gt;id($id)-&gt;one();
}

return $results;
</code></pre>

CC BY-SA 4.0
2020-08-23T10:21:49.170Z
2020-08-23T10:21:49.170Z
Slowwie
<p>I wrote a query like this:</p>
<pre><code> $query = (new Query())
        -&gt;select('COUNT(elements.id) as count, tags.*')
        -&gt;from('relations')
        -&gt;join('INNER JOIN', 'tags', '{{relations}}.targetId={{tags}}.id')
        -&gt;join('LEFT JOIN', 'content', '{{content}}.elementId={{tags}}.id')
        -&gt;join('LEFT JOIN', 'elements', '{{elements}}.id={{tags}}.id')
        -&gt;where([
            'elements.enabled' =&gt; 1,
            'elements.revisionId' =&gt; NULL
        ])
        -&gt;groupBy('{{relations}}.targetId')
        -&gt;orderBy('count DESC');
</code></pre>
<p>So my intention there is to fetch all tags wich have content and do have <code>revisionId IS NULL</code>.</p>
<p>But it never works. I get just all the relations including these one related to elements with a revisionId not null.</p>
<p>Can someone help me in this issue.</p>
<p>I am completely new to craft and use to the symfony query builder wich is kind of different.</p>

<craft3><tags><query><revisions>
Query only Tags wich are not connected to a revision
# Query only Tags wich are not connected to a revision

(Asked by: username_0 on 2020-08-21)

I wrote a query like this:
 $query = (new Query())
        ->select('COUNT(elements.id) as count, tags.*')
        ->from('relations')
        ->join('INNER JOIN', 'tags', '{{relations}}.targetId={{tags}}.id')
        ->join('LEFT JOIN', 'content', '{{content}}.elementId={{tags}}.id')
        ->join('LEFT JOIN', 'elements', '{{elements}}.id={{tags}}.id')
        ->where([
            'elements.enabled' => 1,
            'elements.revisionId' => NULL
        ])
        ->groupBy('{{relations}}.targetId')
        ->orderBy('count DESC');

So my intention there is to fetch all tags wich have content and do have revisionId IS NULL.
But it never works. I get just all the relations including these one related to elements with a revisionId not null.
Can someone help me in this issue.
I am completely new to craft and use to the symfony query builder wich is kind of different.

**Tags:** craft3, tags, query, revisions

---

## 1 answers

### Answer by username_0 on 2020-08-23. Score: 0 | Accepted answer

I found the solution and posted it here in another issue related to this problem.
Here is my solution (just the code):
$query = (new Query())
    ->select([
        '{{relations}}.targetId as id',
        'COUNT({{relations.targetId}}) as count',
        '{{content}}.title as title'
    ])
    ->from('relations')
    ->leftJoin('tags', '{{relations}}.targetId={{tags}}.id')
    ->leftJoin('content', '{{content}}.elementId={{tags}}.id')
    ->leftJoin('elements', '{{elements}}.id={{tags}}.id')
    ->leftJoin('elements source', '{{source}}.id={{relations}}.sourceId')
    ->where([
        '{{elements}}.enabled' => 1,
        '{{source}}.enabled' => 1,
        '{{source}}.revisionId' => NULL,
        '{{source}}.draftId' => NULL,
    ])
    ->groupBy('id, title')
    ->orderBy( 'count DESC, title ASC')
;

$query->limit($limit);

if (is_numeric($limit) && !is_null($limit)) {
    $query->limit($limit);
}

$results = $query->all();

foreach ($results as $key => $value) {
    $id = $value['id'];
    $results[$key] = Tag::find()->id($id)->one();
}

return $results;


---

