Laravel:转换SQL查询，0结果

Question

我正在尝试转换这个使用大量联接的SQL查询，以使用laravel自己的系统。

原始查询和工作查询：

$link = $this->pdo->prepare(
    "SELECT
            affiliates.affiliate_id,
            affiliate_details.firstname,
            affiliate_details.lastname,
            sum(case when affiliates.status = 'accept' then 1 else 0 end) as offers_active,
            sum(case when affiliates.status = 'deny' then 1 else 0 end) as offers_deny,
            sum(case when affiliates.status = 'pending' then 1 else 0 end) as offers_pending,
            count(invoices_aff.invoice_id) as sales,
            count(invoice_details.invoice_id) as refunds
    FROM
        affiliates
    INNER JOIN
        offers ON (offers.id = affiliates.offer_id and offers.vendor_id = :vendor_id)
    INNER JOIN
        user_details as affiliate_details ON (affiliate_details.user_id = affiliates.affiliate_id)
    LEFT JOIN
        invoices ON (invoices.offer_id = offers.id)
    LEFT JOIN
        invoices_aff ON (invoices_aff.invoice_id = invoices.invoice_id AND invoices_aff.affiliate_id = affiliates.affiliate_id)
    LEFT JOIN
        invoice_details ON (invoice_details.invoice_id = invoices_aff.invoice_id AND invoice_details.status = 'refunded')
    GROUP BY
        affiliates.affiliate_id
    "
);

$link->execute(array('vendor_id' => Auth::user()->id));

改为与拉拉一起工作：

$affiliates = DB::table('affiliates')
                ->select(
                    array(
                        'affiliates.affiliate_id',
                        'affiliate_details.firstname',
                        'affiliate_details.lastname',
                        DB::raw('sum(case when affiliates.status = "accept" then 1 else 0 end) as offers_active'),
                        DB::raw('sum(case when affiliates.status = "deny" then 1 else 0 end) as offers_deny'),
                        DB::raw('sum(case when affiliates.status = "pending" then 1 else 0 end) as offers_pending'),
                        DB::raw('count(invoices_aff.invoice_id) as sales'),
                        DB::raw('count(invoice_details.invoice_id) as refunds')
                    )
                )
                ->join('offers', function($join)
                {
                    $join->on('offers.id', '=', 'offers.vendor_id');
                    $join->on('offers.vendor_id', '=', DB::raw(Auth::user()->id));
                })
                ->join('user_details as affiliate_details', 'affiliate_details.user_id', '=', 'affiliates.affiliate_id')
                ->leftJoin('invoices', 'invoices.offer_id', '=', 'offers.id')
                ->leftJoin('invoices_aff', function($join)
                {
                    $join->on('invoices_aff.invoice_id', '=', 'invoices.invoice_id');
                    $join->on('invoices_aff.affiliate_id', '=', 'affiliates.affiliate_id');
                })
                ->leftJoin('invoice_details', function($join){
                    $join->on('invoice_details.invoice_id', '=', 'invoices_aff.invoice_id');
                    $join->on('invoice_details.status', '=', DB::raw("'refunded'"));
                })
                ->groupBy('affiliates.affiliate_id')
                ->get();

但它给了我0的结果。

我还不太熟悉Laravel查询生成器，但我已经非常正确地遵循了示例(至少在我自己的头脑中是这样)，而且我在这里看不到自己的错误。

我这里的问题出什么问题了？

Answer 1

第一个连接(“要约”)出现了错误。应该是offers.id = affiliates.offer_id，而不是offers.id = offers.vendor_id。

   ->join('offers', function($join)
    {
        $join->on('offers.id', '=', 'affiliates.offer_id');
        $join->on('offers.vendor_id', '=', DB::raw(Auth::user()->id));
    });