Convert this Eloquent model function to a MySQL query? [closed]

Question

I need to convert this Eloquent Model Function to a MySQL Query:

public function currentPrice(): HasOne
    {
        return $this->hasOne(Price::class)->ofMany([
            'published_at' => 'max',
            'id'           => 'max',
        ], function(QueryBuilder $query) {
                $query->where('published_at', '<', now());
        });
    }

So, the query should look something like this (missing the above HasOne relation logic that should be in the query):

SELECT items.id, items.article_name, prices.price, prices.published_at, weights.weight, weights.amount
FROM items
INNER JOIN prices ON items.id = prices.item_id
INNER JOIN weights ON items.id = weights.item_id
ORDER BY prices.published_at;

Answer 1

Db Query is given bellow

SELECT 
    i.id,
    i.article_name,
    p.price,
    p.published_at,
    w.weight,
    w.amount
FROM items i
INNER JOIN (
    SELECT item_id, MAX(published_at) AS max_published_at
    FROM prices
    WHERE published_at < NOW()
    GROUP BY item_id
) latest_prices ON i.id = latest_prices.item_id
INNER JOIN prices p 
    ON p.item_id = latest_prices.item_id 
    AND p.published_at = latest_prices.max_published_at
INNER JOIN weights w ON i.id = w.item_id
ORDER BY p.published_at DESC;

Answer 2

Here’s how to express that logic using raw SQL:

SELECT 
    items.id,
    items.article_name,
    prices.price,
    prices.published_at,
    weights.weight,
    weights.amount
FROM items
INNER JOIN (
    SELECT p1.*
    FROM prices p1
    INNER JOIN (
        SELECT item_id, MAX(published_at) AS max_published_at
        FROM prices
        WHERE published_at < NOW()
        GROUP BY item_id
    ) p2 ON p1.item_id = p2.item_id AND p1.published_at = p2.max_published_at
    INNER JOIN (
        SELECT item_id, MAX(id) AS max_id
        FROM prices
        WHERE published_at < NOW()
        GROUP BY item_id
    ) p3 ON p1.item_id = p3.item_id AND p1.id = p3.max_id
) prices ON items.id = prices.item_id
INNER JOIN weights ON items.id = weights.item_id
ORDER BY prices.published_at DESC;