Question 372299 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel orm query between two accounting accounts

I have a table transaction_details

cta    scta   sscta charge  
102    1      1     80.00   
102    1      1      5.00   
102    1      2     20.00   
102    1      2      2.00   
105    1      0     30.00   
105    2      0     50.00   
107    1      0     45.00   
107    1      0     71.00

For example, I want to make a query from account 102 1 2 to account 105 1 0

I have only achieved the following:

$transaction_details = TransactionDetails::whereBetween('cta', [ 102, 105 ])->get();

I want to make a query from account cta(102) scta(1) sscta(2) to account cta(105) scta(2) sscta(0):

cta    scta   sscta charge  
102    1      2     20.00   
102    1      2      2.00   
105    1      0     30.00   
105    2      0     50.00
Tags:

2

Answers


  1. 0

    The requirements are not very clear; for example, if there you have this:

    id    cta    scta   sscta charge  
 1    102    1      1     80.00   
 2    102    1      1      5.00   
 3    102    1      2     20.00   
 4    102    1      3      2.00   
 5    103    1      3     30.00   
 6    103    2      0     30.00   
 7    105    0      1     50.00   
 8    105    1      1     50.00   
 9    107    1      0     45.00   
10    107    1      0     71.00

    Do you want to select rows 4, 5, 6, 7?

    A "raw" approach

    If the answer is YES, you could concatenate the columns.

    $transaction_details = TransactionDetails::where(
  DB::raw('CONCAT(LPAD(`cta`, 3, '0'),LPAD(`scta`, 3, '0'),LPAD(`sscta`, 3, '0')) between '102001002' and '105001000')
);

    This supports values up to 999 for the cta, scta and sscta columns.

    A clearer and more fluent approach.

    use IlluminateDatabaseEloquentBuilder;

...

TransactionDetails::query()
            ->where(function (Builder $q) {
                $q->where('cta', 102)
                  ->where('scta', 1)
                  ->where('sscta', '>=', 2);
            })
            ->orWhere(function (Builder $q) {
                $q->where('cta', '>', 102)
                  ->where('cta', '<', 105);
            })
            ->orWhere(function (Builder $q) {
                $q->where('cta', 105)
                  ->where('scta', 1)
                  ->where('sscta', '<=', 0);
            })

    Equivalent query:

    SELECT *
FROM transaction_details
WHERE
    -- For records with cta = 102
    (cta = 102 AND scta = 1 AND sscta >= 2)
OR
    -- For records with cta between 103 and 104, inclusive
    (cta > 102 AND cta < 105)
OR
    -- For records with cta = 105
    (cta = 105 AND scta = 1 AND sscta <= 0);
    Login or Signup to reply.
  2. 0

    I can’t find a simple solution without raw where.
    This should work:

    $transaction_details = TransactionDetails
  ->whereBetween('cta', [ 102, 105 ])
  ->whereRaw('case when `cta` = ? then `scta` >= ? when `cta` = ? then `scta` <= ? else True end', [102, 1, 105, 2])
  ->whereRaw('case when (`cta` = ? and `scta` = ?) then `sscta` >= ? when (`cta` = ? and `scta` = ?) then `sscta` = ? else True end', [102, 1, 2, 105, 2, 0])
  ->get()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search