Laravel Eloquent Query for Double Entry Journal

Question

I have a table journals having columns transaction_id, type(receipt or payment), debit and credit columns for amount, account_id relation with accounts table

the records can be multiple receipt or payment entries, and an opposite entry to record sum of these multiple entries matching transaction_id if entries are type receipt column credit should have amount else column debit should have amount.

i want to retrieve results as following. get all journals, each row should have opposite row columns, rows columns should be like this. transaction_id, type, amount(debit or credit), credit_id(based on entry type, from journal or opposite journal), debit_id(based on entry type, from journal or opposite journal matching 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')

");

but not returning rows as expected, number of rows are 35640, but result was 7640

i have rows like this

Answer 1

I understand that you want to retrieve all rows from the journals table while matching each row with its opposite entry based on the transaction_id. You also want to exclude the opposite rows from the result set. To achieve this, you can use a self-join and a subquery.

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';

Answer 2

The query you provided appears to be an attempt to retrieve journal entries with their corresponding opposite entries. It seems like you want to get all journal entries that have a matching opposite entry based on the transaction_id. However, there are a few issues in your query that could lead to unexpected results:

Joining j1 and j2 without distinguishing between debit and credit entries can create a Cartesian product, resulting in more rows than you expect. The conditions in your WHERE clause may not be restrictive enough to match entries correctly. Here's an updated query that should help you retrieve the desired results:

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';

In this query, use a UNION ALL to combine the records from the journals table.separate the logic for receipt and payment entries and ensure that retrieve the corresponding credit_id and debit_id based on the entry type. This should help you get the correct results.

Make sure your data model and naming conventions match this query for it to work correctly.