AR "via/viaTable" relations: sort by junction table field

[degibons]

Unfortunately, the problem with sorting by field in junction table is still exist. For example we have three tables:

item:

| id | title  | 
---------------
| 1  |  dog   |
| 2  |  cat   |
| 3  |  pup   |

item_has_value:

| item_id | value_id | rate |
-----------------------------
| 1       | 1        | 100  |
| 1       | 2        | 200  |
| 2       | 3        | 200  |
| 2       | 4        | 100  |
| 3       | 2        | 100  |

value:

| id | title  | 
---------------
| 1  |  bow   |
| 2  |  wow   |
| 3  |  mew   |
| 4  |  miaow |

And these relations:

Item Model:

public function getValues()
{
        return $this->hasMany(Value::class, ['id' => 'valueId'])->via('itemValues');
}

public function getItemValues()
{
        return $this->hasMany(ItemHasValue::class, ['itemId' => 'id'])->orderBy(['rate' => SORT_DESC]);
}

And query to get linked values (properly sorted by rate field in juction table):

... ->getItems()->with('values')->...

It fails, because the orderBy applies only in the first sql query (as it mentioned in https://stackoverflow.com/questions/27690401/activerecord-where-and-order-on-via-table/27691992#27691992).

But if we add these relation in ItemHasValue model:

ItemHasValue Model:

public function getValue()
{
        return $this->hasOne(Value::class, ['id' => 'valueId']);
}

And perform these query:

... ->getItems()->with([
    'itemValues' => function($query) {
                $query->with('value');
            },
])->...

It will sort related values properly. But performs the exact same sql queries (as in the via relation).

So, question is - why does it happen, and why we can't sort properly by junction table field? Maybe it is possible (in via / viaTable relations with sort) to add to the second query something like this: ... ORDER BY FIELD(id, 2, 1, 3, 4) ... or sort it internaly ?

[samdark]

Do you mean by "still exist" that the problem was going to be fixed by a pull request? If yes, which one?

[degibons]

No. I don't know any pull request which solves this problem. I mean that there was several issues and discussions about this problem, unfortunately without satisfactory solution. E.g.: https://github.com/yiisoft/yii2/issues/16606 https://github.com/yiisoft/yii2/issues/10174 https://stackoverflow.com/questions/27690401/activerecord-where-and-order-on-via-table https://forum.yiiframework.com/t/how-to-orderby-on-viatable-in-hasmany-model-relationship/80043

[mikk150]

@degibons do you make Query class for every model?

If so, then you can do this in Query class

    /**
     * @param string $field
     * @return string
     */
    public function prependWithTableAlias($field)
    {
        /** @var ActiveQuery $this */
        list(, $alias) = $this->getTableNameAndAlias();
        return $alias . '.[[' . $field . ']]';
    }

    public function orderBy($columns)
    {
        array_map(function ($order, &$key) {
            $key = $this->prependWithTableAlias($key);
        }, $columns);

        return parent::orderBy($columns);
    }

[anasjaghoub]

Hi @degibons,

Though this is an old post, most probably you already got an answer/workaround for it ;) Today I faced exactly the same issue and found a solution that works perfectly without changing the DB-relation signature.

My solution as below:

• Instead of using $this->hasMany use an ActiveQuery
• Use the property $multiple on the query to indicate that this query will return an array of results.
• Use leftJoin method, and define the junction table relation inside it.

Example:

public function getValues()
{
$query = Value::find()
->leftJoin(ItemHasValue::tableName(), ItemHasValue::tableName() . '. value_id = ' . Value::tableName() . '.id' )
->where([
// define all ur conditions here
])
$query->multiple = true;

return $query;
}

I hope this can help you or anyone who faces this issue.

CC: @samdark can you please document this somehow, to help anyone who wants to order via a junction table and maintain the sort on the primary table.

[samdark]

@anasjaghoub that's quite specific case so I'm not sure where/how to docuement it best...

[RickKukiela]

I ran into this same issue of wanting to sort by a junction table with a dynamic relations. I was able to come up with a work around that does work correctly but I'm not sure if what I'm doing is "good" or "optimal" and if there is any arguments for or against my solution. If one of the Yii Devs can check my post at stack overflow and let me know your thoughts on this it would be very much appreciated. I love Yii and plan on using for pretty much all of my web apps going forward so I just really want to learn and understand the why behind this and the best way to handle this moving forward

https://stackoverflow.com/questions/69329769/yii2-dynamic-relational-query-junction-with-sort-uses-2-queries-instead-of-a-joi

[Commifreak]

We have the same issue. A leftjoin should work but its inefficient.

What if....

Yii generate the IN() query as usual but inlcudes an automatic order by FIELD(pk, 1,2,3)? Yii always adds then an order by FIELD with the same values as pasted inside the IN(). Since the IN ids are the ordered (because they returned ordered from the previous relation table) the order would be then correct, right?

So, when Yii generated the second (IN) query as: WHERE user.id IN (1, 2, 4, 10, 954, 979, 1080, 1102, 1116) and adding an always order by FIELD (user.id, 1, 2, 4, 10, 954, 979, 1080, 1102, 1116) would save the ordering, doesnt it?

Does that make any sense?

[mikk150]

We have the same issue. A leftjoin should work but its inefficient.

if left join is inefficient, your indexes are bad.

What if....

Yii generate the IN() query as usual but inlcudes an automatic order by FIELD(pk, 1,2,3)? Yii always adds then an order by FIELD with the same values as pasted inside the IN(). Since the IN ids are the ordered (because they returned ordered from the previous relation table) the order would be then correct, right?

So, when Yii generated the second (IN) query as: WHERE user.id IN (1, 2, 4, 10, 954, 979, 1080, 1102, 1116) and adding an always order by FIELD (user.id, 1, 2, 4, 10, 954, 979, 1080, 1102, 1116) would save the ordering, doesnt it?

Does that make any sense?

No, as SQL does not care what order things are in... ORDER BY is for that

[Commifreak]

No, as SQL does not care what order things are in... ORDER BY is for that

Indeed, thats becuase the FIELD addition would work, since IN() does not care about order.

We tested that with an quick'n'dirty hack:

Index: vendor/yiisoft/yii2/db/conditions/InConditionBuilder.php
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/vendor/yiisoft/yii2/db/conditions/InConditionBuilder.php b/vendor/yiisoft/yii2/db/conditions/InConditionBuilder.php
--- a/vendor/yiisoft/yii2/db/conditions/InConditionBuilder.php	
+++ b/vendor/yiisoft/yii2/db/conditions/InConditionBuilder.php	(date 1633067577674)
@@ -91,7 +91,7 @@
             $column = $this->queryBuilder->db->quoteColumnName($column);
         }
         if (count($sqlValues) > 1) {
-            $sql = "$column $operator (" . implode(', ', $sqlValues) . ')';
+            $sql = "$column $operator (" . implode(', ', $sqlValues) . ') order by field('.$column.','.implode(', ', $sqlValues).')';
         } else {
             $operator = $operator === 'IN' ? '=' : '<>';
             $sql = $column . $operator . reset($sqlValues);

That would work in our case (for testing purposes). This shows what I mean.

So - it seems, that an addiotional leftJoin is the master solution on those issues (https://github.com/yiisoft/yii2/issues/17166#issuecomment-766326257)? Or exists other solution tips for those?

[DrummerKH]

Hello everyone! i has the same issue. I have two models with relation via table. And i need models that sorted by field which exists in via table. You know that in case of hasMany()->viaTable(), Yii makes two separate queries. My solution is add LEFT JOIN via table with ORDER BY to the query from needed model. Here is example, if you have relation in primary model like:

public function getModles(): ActiveQuery
{
        return $this
            ->hasMany('model_table', ['id' => 'relation_id'])
            ->viaTable('via_table', ['owner_id' => 'id']);
}

You can add priority on via table field:

public function getModles(): ActiveQuery
{
        return $this
            ->hasMany('model_table', ['id' => 'relation_id'])
            ->viaTable('via_table', ['owner_id' => 'id'])
            ->leftJoin('via_table via', 'via.relation_id = id')
            ->orderBy([
                'via.priority' => SORT_ASC,
            ]);
}

It works perfect for me.

[robsch]

@Commifreak Unfortunately, this seems to be problematic as soon as an additional where condition exists. Right? Then all conditions gets wrapped into ( )...

[robsch]

@DrummerKH Does your solution really work? Which queries gets created by

public function getModles(): ActiveQuery
{
        return $this
            ->hasMany('model_table', ['id' => 'relation_id'])
            ->viaTable('via_table', ['owner_id' => 'id'])
            ->leftJoin('via_table via', 'via.relation_id = id')
            ->orderBy([
                'via.priority' => SORT_ASC,
            ]);
}

Within my app the second query contains the IN clause with the ids of the first query. But there is no constraint regarding the model id. Considering these values in the linking table (from the original post):

| item_id | value_id | rate |
-----------------------------
| 1       | 1        | 100  |
| 1       | 2        | 200  |
| 2       | 3        | 200  |
| 2       | 4        | 100  |
| 3       | 2        | 100  |

Item::find()->where(['id' => 3])->with('values')->one() would select (with the second query containing the IN clause) the records with value_id=2, which are two instead of one! The first one has rate value 200, which is wrong. In case of an item has only a single value entry, it doesn't matter, of course, since only the first one gets used and the others will be ignored. But if there are more, then the order may be wrong.

Correct me if I'm wrong (I'm confused anyway). At least in my code that doesn't seem to work as intended.

[robsch]

@samdark Is it possible that you or anyone implement that? The approach of @Commifreak looks promising but is not complete, afaik. It's to complex for me to provide a solution, sadly...

Or could you provide a working work around? I assume that the sugessted solutions doesn't work without restrictions, if I'm not wrong.

[robsch]

Found this answer which should reliably work. With this it should be:

public function getValues()
{
    $ids = $this->getItemValues()->select('itemId')->column();
    return $this->hasMany(Value::class, ['id' => 'valueId'])
        ->via('itemValues')
        ->orderBy([new Expression('FIELD (id, ' . implode(',', $ids) . ')')]);
}

public function getItemValues()
{
    return $this->hasMany(ItemHasValue::class, ['itemId' => 'id'])
        ->orderBy(['rate' => SORT_DESC]);
}

Can anyone confirm that this is a working solution that works in all situations? I think it should work as long as no other orderBy gets appended to the query. To be 100% sure a transaction might be needed as well.

Update: Implemented that in my app, but found out that this also works only with a single item. If multiple gets fetched (Item::find()->where(['id' => 1,2,3])->with('values')->all() it does not work. A bit frustrating ...