OrderBy or SortBy on the collection of ties Laravel?

Good evening. The challenge is sorting the values while fetching from the database. Overall, if I implement connection one-to-one, no problems, everything works. But I have in the database is in use the EAV pattern. Every product belongs to a group of characteristics. Each characteristic has the position field on which I set the sort priority. After that, each characteristic has a relationship with the value bound to the product. And these values, too, have the field position.

Let's say I got data from database and do the sorting
$products->sortBy(function($q){
 return $q->relatedProduct->relatedSpecsGroup->position;
})

This will work if the relationship "relatedSpecsGroup" will be one to one, but that is the relationship "to-many through", hence this method will not work. In the documentation I did on this occasion was not found.

It turns out that "relatedSpecsGroup" contains features that are bound to the group product. And each of the characteristics has a value, which in turn belongs to and item characteristics.

relatedSpecsGroup for each item looks like this:
[
['id', 'title','position','relatedValue'=>['id','value','position']],
['id', 'title','position','relatedValue'=>['id','value','position']],
['id', 'title','position','relatedValue'=>['id','value','position']],
['id', 'title','position','relatedValue'=>['id','value','position']],
]

I need to sort by values relatedValue, but first, sort relatedSpecsGroup on position, thereby vystavlyaetsya the sorting order for features, and only then for values of the characteristics.

As this kind of sort delayutsya? Can such sorting be done on the query level because it is significantly faster than the sortBy method on the collection. If not, how is it realizuetsya with sortBy?

Thank you!
March 23rd 20 at 19:13
1 answer
March 23rd 20 at 19:15
Try this
Product::with(['relatedSpecsGroup' => function($query) {
 $query->orderBy('position', 'desc');
}])
 ->orderBy('relatedValue', 'desc')
 ->get();
No, it's not so hard. With a separate request is - Beulah60 commented on March 23rd 20 at 19:18
@Beulah60, so the author need a separate
I need to sort by values relatedValue, but first, sort relatedSpecsGroup at the position
- rolando28 commented on March 23rd 20 at 19:21
@rolando28,
$products->sortBy(function($q){
 return $q->relatedProduct->relatedSpecsGroup->position;
})


Look carefully how it sorts) - Beulah60 commented on March 23rd 20 at 19:24
@Beulah60, Yes, I saw. through the connection. or the communication can be separate from the model sort? - rolando28 commented on March 23rd 20 at 19:27
@Adeline_Schiller the author of AU. You have what problem? - rolando28 commented on March 23rd 20 at 19:30
@rolando28, initially using eloquent is not able to do the sorting - Beulah60 commented on March 23rd 20 at 19:33
@Beulah60in question need to sort the relationship of the products, then the products, not the products through a relationship. Either the question is formulated incorrectly - rolando28 commented on March 23rd 20 at 19:36
@rolando28, the task of the next, you need to sort products by group characteristics. But each characteristic has its priority. For example, first sort by values of characteristics of the screen, then the characteristic values storage.

Here's an example:
Phone X 16GB(position:1) EU(position:1)
Phone X 16GB(position:1) CHINA(position:2)
Phone X 32GB(position:2) EU(position:1)
Phone X 32GB(position:2) CHINA(position:2)

Products in discord, but then they need to put in a certain order. In the example, there are 2 characteristics of storage and country. So the warehouse had field position it is set to 1 and the values of the same performance in the same is position. So, first take the Store, sorted according to their values. Then take the following characteristics and sorted according to its values. The characteristics may be any number. - Adeline_Schiller commented on March 23rd 20 at 19:39
I was thinking, take all the values from the database, attach values to ties to the product directly, which was something like
id
title
position: {position:1,position3,position 5}

And sort by N columns, but this method will be very slow, because of course the priority, delative this case at the level of the query to the database - Adeline_Schiller commented on March 23rd 20 at 19:42
@Adeline_Schillerhonestly did not understand. Especially
sort products by group characteristics

the name of the band or something?) I'm in an Internet shops did not penetrate, maybe @Beulah60 will help you understand.
As to the sort of characteristics.. If they are specific to product relationships, and sort each product so
Product::with(['attitude characteristics' => function($query) {
 $query->orderBy('field', 'desc');
 }])->get();
- rolando28 commented on March 23rd 20 at 19:45
@rolando28, Well smotrite, I have a product, it privyazalsya group of characteristics. For example Phone X to the NEPA privyazalsya group called "phone" in this group there is, for example, 2 characteristics: the screen and storage. And each characteristic in turn has a value, for example 16GB,32GB, UA,UK, etc...

Accordingly
//$q->relatedProduct->relatedSpecsGroup is an array of characteristics. Each of which, there is a field position and another link relatedValue.


Based on the example above, the product And prenadlezhit to the group characteristics of the Phone. Group characteristics the Phone carries a 2 features: store and country. By and large we sort by value characteristics, but focusing on the position field in the feature, we decide on the values of some characteristics need to sort. If "storage" is position 2, and say "the country" has position 1, then first we sort by value and then the value storage.

Still messy, but perhaps such a description would bring more clarity. Thanks for trying to help!) - Adeline_Schiller commented on March 23rd 20 at 19:48
@Adeline_Schiller, I'd love to help, but the solution is not know and task is a bad idea) Here we need a clever query with join, select debug, this is the place you need to understand. Immediately you here hardly write. Try to break the task into smaller components and it is asked in the toaster. - rolando28 commented on March 23rd 20 at 19:51
@Beulah60 good night, would you mind to help me with my issue? - Adeline_Schiller commented on March 23rd 20 at 19:54
@Adeline_Schiller, I understand that there is susesi say the phone has a memory and the country. These caracteristics have to say "weight" and need to sort on this weight?

There simply will not help orm lark, she's not that awesome, actually.

Easier to do, as I wrote, join . And there is simply sorted by positian - Beulah60 commented on March 23rd 20 at 19:57
@Beulah60, see, I have this query

$products = ModelProvidersProducts::select(DB::raw('products.title,products.id,specifications_values.position,specifications.position as spec_pos'))
 ->leftJoin('products', 'products.id', '=', 'providers_products.product_id')
 ->leftJoin('nomenclature', 'nomenclature.id', '=', 'products.category')
 ->leftJoin('products_specifications', 'products_specifications.p_id', '=', 'product_id')
 ->leftJoin('specifications', 'specifications.id', '=', 'products_specifications.s_id')->orderBy('spec_pos')
 ->leftJoin('specifications_values', 'specifications_values.id', '=', 'products_specifications.v_id')->orderBy('position')
->orderBy('category')
 ->get();


But since 1 one of the product of N values, then the result of this query will duplicate records with different values for characteristics. For example, right now I have 6 products in the database, but displays 12.

id: 73 title: Iphone 5 64GB UK

id: 74 title: Iphone 5 128GB UK

id: 76 title: Iphone 5 16GB CH

id: 71 title: Iphone 5 16GB UK

id: 74 title: Iphone 5 128GB UK

id: 71 title: Iphone 5 16GB UK

id: 73 title: Iphone 5 64GB UK

id: 76 title: Iphone 5 16GB CH

id: 72 title: Iphone 6 64GB UK

id: 75 title: Iphone 6 16GB UK

id: 75 title: Iphone 6 16GB UK

id: 72 title: Iphone 6 64GB UK

After inquiry, I did unique() on the id field, but still sorts correctly. More precisely, sort the products by position value for the first characteristic, the second doesn't work - Adeline_Schiller commented on March 23rd 20 at 20:00
@Beulah60, I need to sort not in the field of the position table specifications, and on the same field, but the table specifications_values. But specifications_values which specifications to go in the first place, reshaetsya the value fields position in the table specifications - Adeline_Schiller commented on March 23rd 20 at 20:03
@Beulah60, actually I have no idea how to sort this kind of thing. Everywhere and people the problem with sorting according to known count values and usually is a sort of collection. If you withdraw from laravel and search options for sorting the data presented in EAV, a clear answer is not found. Because I tried to do differently, although I understand that will be a big problem for performance and pagination.

I took the whole request, went through the collection and delivered to each array element the following
5cfd72566b4dc835996442.png

But the problem here in the fact that all commodities can be a different number of values, even within the same category.
By the way, and here I was comprehended by failure, because I again found itself in what people write sortBy()->sortBy()->sortBy(), but it only works when we know the number of values to sort.

In the mountains the specs I put are sorted by the position value specifications and made them the keys and value set the position field to value characteristics

If not at the query level, at least at the collection level will be able to suggest a solution? - Adeline_Schiller commented on March 23rd 20 at 20:06
@Adeline_Schiller, do a join using a select with limit 1, which sortirai at the position - Beulah60 commented on March 23rd 20 at 20:09

Find more questions by tags Laravel