0

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.

0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.