CODEWITHSUNDEEP
mysqllaravel
Chan
Chan

Reputation: 1959

How to order by where in fields in Laravel

I did some calculate in a view table for products in MYSQL, so I list these id for product reference id.

$referenceIds = viewTable::orderBy('score', 'DESC')->lists('product_id');

And I want to get the product from those reference id.

$products = Product::whereIn('id', $rederenceIds);

The product content is correct, but the order is wrong, how to load product order by reference id, I know I can do it in MySQL by using ORDER BY FIELD(id,' . $fields . '), but I want to find out Laravel way, and hope not use ORDER BY FIELD command because it seems waste to much database efficiency.

Upvotes: 15

Views: 11917

Answers (3)

Surlavi
Surlavi

Reputation: 1

If the key is primay key (I think in this question it is ...), you can try this:

$products = Product::whereIn('id', $rederenceIds)->get();
$products = array_replace(array_flip($rederenceIds), $products->getDictionary());

The result is Array, not Collection.

I don't know whether it has an efficiency problem, but order by field has other problems. For example, order by field is not supported by sqlite so it doesn't work in testing with in-memory database.

Upvotes: -1

omar j
omar j

Reputation: 541

You will have to inject some raw sql, but it's not a nightmare:

$referenceIds = viewTable::orderBy('score', 'DESC')->lists('product_id');
$referenceIdsStr = implode(',', $referenceIds);
$products = Product::whereIn('id', $rederenceIds)->orderByRaw(DB::raw("FIELD(product_id, $referenceIdsStr)"))->get()->all();

Upvotes: 13

Glad To Help
Glad To Help

Reputation: 5387

Not sure if I understand this entirely, but if you want the products ordered, you should use

$referenceIds = viewTable::lists('product_id');

$ordered_products = Product::whereIn('id', $rederenceIds)->orderBy('id', 'DESC')->get();

Upvotes: 0

Related Questions