Início / coding / Perguntas / 79575841
Accepted
Michael Kocurek
Asked: 2025-04-16 02:41:01 +0800 CST

Converter esta função do modelo Eloquent em uma consulta MySQL?

  • 772

Preciso converter esta função do modelo Eloquent em uma consulta MySQL:

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

Portanto, a consulta deve ser parecida com esta (faltando a lógica da relação HasOne acima que deveria estar na consulta):

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;

Obrigado

sql

2 respostas

  1. Best Answer

    A consulta ao banco de dados é fornecida abaixo

    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;
    • 1

  2. Veja como expressar essa lógica usando SQL bruto :

    
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;
    • 0

relate perguntas