mysql Laravel Eloquent查询双分录日记账

jm81lzqq 于 2023-11-16 发布在 Mysql

关注(0)|答案(2)|浏览(98)

我有一个表journals，它具有列transaction_id、类型（接收或支付）、金额的借方和贷方列、与accounts表的account_id关系
记录可以是多个收款或付款条目，并且如果条目是类型，则记录与transaction_id匹配的这些多个条目的总和的相反条目收款列贷方应具有金额，否则列借方应具有金额。
我想检索结果如下.获取所有日记帐，每行应该有相对的行列，行列应该是这样的. transaction_id，type，amount（debit or credit），credit_id（基于分录类型，来自日记帐或相对日记帐），debit_id（基于分录类型，来自日记帐或相对日记帐匹配transaction_id）

$accounts = DB::select("
SELECT 
    a.id as account_id, 
    a.title as accountTitle,
    j1.type as type,
    CASE 
        WHEN j1.type = 'cashReceipt' THEN j1.credit
        ELSE j1.debit
    END AS amount,
    CASE 
        WHEN j1.type = 'cashReceipt' THEN j1.account_id
        ELSE j2.account_id
    END AS credit_id,
    a_credit.title as credit_account_title,
    CASE 
        WHEN j1.type = 'cashPayment' THEN j1.account_id
        ELSE j2.account_id
    END AS debit_id,
    a_debit.title as debit_account_title,
    j1.narration
FROM 
    accounts a
JOIN 
    journals j1 ON a.id = j1.account_id
JOIN 
    journals j2 ON j1.transaction_id = j2.transaction_id
JOIN
    accounts a_credit ON a_credit.id = CASE 
        WHEN j1.type = 'cashReceipt' THEN j1.account_id
        ELSE j2.account_id
    END
JOIN
    accounts a_debit ON a_debit.id = CASE 
        WHEN j1.type = 'cashPayment' THEN j1.account_id
        ELSE j2.account_id
    END
WHERE 
    (j1.type = 'cashReceipt' AND j2.type = 'cashPayment')
    OR 
    (j1.type = 'cashPayment' AND j2.type = 'cashReceipt')

字符串
“）;
但未按预期返回行，行数为35640，但结果为7640
我有这样的行x1c 0d1x

mysql

来源：https://stackoverflow.com/questions/77428781/laravel-eloquent-query-for-double-entry-journal

2条答案

按热度按时间

x3naxklr1#

您提供的查询似乎试图检索日记帐分录及其对应的相对分录。您似乎希望获取所有具有基于transaction_id的匹配相对分录的日记帐分录。但是，您的查询中存在一些问题，可能会导致意外结果：
在不区分借方和贷方条目的情况下连接j1和j2可能会创建笛卡尔积，从而导致比预期更多的行。您的WHERE子句中的条件可能没有足够的限制来正确匹配条目。下面是一个更新的查询，它应该可以帮助您检索所需的结果：

SELECT 
j1.transaction_id,
j1.type,
CASE 
    WHEN j1.type = 'receipt' THEN j1.credit
    WHEN j1.type = 'payment' THEN j1.debit
END AS amount,
CASE 
    WHEN j1.type = 'receipt' THEN j1.credit_id
    WHEN j1.type = 'payment' THEN j1.debit_id
END AS credit_id,
CASE 
    WHEN j1.type = 'receipt' THEN j1.debit_id
    WHEN j1.type = 'payment' THEN j1.credit_id
END AS debit_id
FROM journals j1
WHERE j1.type = 'receipt' OR j1.type = 'payment'
UNION ALL
SELECT 
j2.transaction_id,
j2.type,
CASE 
    WHEN j2.type = 'receipt' THEN j2.credit
    WHEN j2.type = 'payment' THEN j2.debit
END AS amount,
CASE 
    WHEN j2.type = 'receipt' THEN j2.credit_id
    WHEN j2.type = 'payment' THEN j2.debit_id
END AS credit_id,
CASE 
    WHEN j2.type = 'receipt' THEN j2.debit_id
    WHEN j2.type = 'payment' THEN j2.credit_id
END AS debit_id
FROM journals j2
WHERE j2.type = 'receipt' OR j2.type = 'payment';

字符串
在此查询中，使用UNIONALL将日记账表中的记录进行合并合并，将收款和付款分录的逻辑分开，并确保根据分录类型检索相应的credit_id和debit_id，这将有助于您获得正确的结果。

请确保您的数据模型和命名约定与此查询匹配，以便其正确工作。

赞(0）回复(0）举报 2023-11-16

3zwtqj6y2#

我了解到您希望从journals表中检索所有行，同时根据transaction_id将每一行与其相对条目进行匹配，并将相对行从结果集中排除。为此，您可以使用自连接和子查询。

SELECT 
j1.transaction_id,
j1.type,
j1.amount,
j1.account_id AS debit_account_id,
j2.account_id AS credit_account_id
FROM journals j1
INNER JOIN journals j2 ON j1.transaction_id = j2.transaction_id
WHERE j1.type = 'payment' AND j2.type = 'receipt'
OR j1.type = 'receipt' AND j2.type = 'payment';

字符串

赞(0）回复(0）举报 2023-11-16

我来回答

mysql Laravel Eloquent查询双分录日记账

2条答案

相关问题

热门标签

最新问答