CODEWITHSUNDEEP
phpsqlactiverecordyii2
LinusR
LinusR

Reputation: 1209

Yii2 many-to-many relation: order results of with() by field in junction table

Just to pre-empt the idea that this is a duplicate of questions such as this, this, or this: those situations are different because they're talking about getting all B items, in an order defined by a junction table field, for a specific A item. I'm talking about querying for many A items, and for each A item, eagerly loading its B items in an order defined by a field in the junction table.

Say you have paper and author tables which are many-to-many, associated through paper_author table. But for academic papers, the order of authors is important, so the paper_author table has a numeric rank field.

Now you want to display a list of all papers' titles, and beside each paper's title you want to list the authors, in the proper order. You also want to eagerly load the authors to prevent having to run 100 author queries if the list of papers is 100 papers long.

You have the standard Yii2 ActiveRecord relations already set up. So you do something like,

<?php
$papers = Paper::find()->with('authors')->all();

foreach ($papers as $paper) {
    echo "Title: " . $paper->title . "\n"
        . "Authors: ";

    foreach ($paper->authors as $author) {
        echo $author->name . " ";
    }

    echo "\n";
}
?>

However, the authors for each paper come out in an undefined order. OK, so you make a special change to the declaration of the authors relation in the Paper class:

<?php
class Paper extends ActiveRecord {
    // ...

    public function getAuthors() {
        return $this
            ->hasMany(Author::className(), ['id' => 'author_id'])
            ->viaTable('paper_author', ['paper_id' => 'id'],
                // SPECIAL CHANGE: 3rd param to viaTable()
                function ($query) {
                    $query->orderBy('rank');
                }
            )
    }

    // ...
}
?>

However, this doesn't work because the $query being modified is the SELECT on paper_author, whose purpose is to get a list of author_ids. After that, another SELECT query is run on the author table with WHERE id IN (...author_ids...), but the results of that query do not respect the order of the IDs given to the IN() function -- that's just not how IN() works.

And even if you could make it so the results of that query did respect that order, you'd have this problem: the query brings back a list of authors where each author appears only once. This makes it efficient. However, a given author may be attached to more than one paper in the list of papers you're displaying. Author order (rank) is not an attribute of an author; it's an attribute of the author-paper relation.

So is this a problem that Yii2 is able to solve, and I don't know it? Or is some custom result-processing necessary?

Upvotes: 2

Views: 356

Answers (1)

oakymax
oakymax

Reputation: 1474

I think that for this case the correct way would be to define additional AR class for junction table because it's not only many-to-many link but also have own meaning -- the order position of an author in an authors list.

So, when you'll have class like PaperAuthorship for example (with proper relations between Paper, PaperAuthorship and Author) you'll be able to execute your query this way:

$papers = Paper::find()
    ->with('paperAuthorships', 'paperAuthorships.author')
    ->all();

foreach ($papers as $paper) {
    echo "Title: " . $paper->title . "\n"
        . "Authors: ";

    foreach ($paper->paperAuthorships as $paperAuthorship) {
        echo $paperAuthorship->author->name . " ";
    }

    echo "\n";
}

And it will be quite easy to achieve the correct sort order (for example in relation Paper.paperAuthorships)

Upvotes: 0

Related Questions