Sep 2, 2022

Level 2

how to select from subquery in laravel using eloquent or query builder?

I am building one query i.e

 SELECT txnDate.month,
               txnDate.transactionDate,
               txnDate.dateType,
               userTxn.user_id AS userId,
               userTxn.closing_bal AS closingBalance,
               userTxn.opening_bal AS openingBalance
        FROM (
                 (SELECT date_format(res_dt, '%M-%y') AS month,
                         MIN(res_dt)                  AS transactionDate,
                         'MIN'                        AS dateType
                  FROM user_transactions
                  WHERE ipay_error = 'TXN'
                  GROUP BY date_format(res_dt, '%M-%y')
                 )
                 UNION
                 (
                     SELECT date_format(res_dt, '%M-%y') AS month,
                            MAX(res_dt)                  AS transactionDate,
                            'MAX'                        AS dateType
                     FROM user_transactions
                     WHERE ipay_error = 'TXN'
                     GROUP BY date_format(res_dt, '%M-%y')
                 )
             ) txnDate
                 JOIN user_transactions userTxn
                      ON userTxn.res_dt = txnDate.transactionDate
        order by transactionDate;

now for quick explanation i have made this query to fetch last some months record having initial and last dates transsaction now everythings is fine but in laravel i was trying it in query builder method in which i am stucked

what i have done so far is made inside union query like this

$minDateRecords = UserTransaction::where('ipay_error', 'TXN')
            ->selectRaw('
            date_format(res_dt, "%M-%y") AS month,
            MIN(res_dt) AS transactionDate,
            "MIN" AS dateType'
            )->groupBy('month');

        $maxDateRecords = UserTransaction::where('ipay_error', 'TXN')
            ->selectRaw('
            date_format(res_dt, "%M-%y") AS month,
            MAX(res_dt) AS transactionDate,
            "MAX" AS dateType'
            )->groupBy('month')
            ->union($minDateRecords)
            ->get()
            ->toArray();

but now i am confused how to make it temp table by which outer query works something like this

  $data = UserTransaction::where($conditions)
        ->select([
            'txnDate.month,
            txnDate.transactionDate,
            txnDate.dateType,
            userTxn.user_id AS userId,
            userTxn.closing_bal AS closingBalance,
            userTxn.opening_bal AS openingBalance'
        ])->join('subquery.join','on-condiotoj')
        ->from(function($query){
            $minDateRecords = UserTransaction::where('ipay_error', 'TXN')
            ->selectRaw('
            date_format(res_dt, "%M-%y") AS month,
            MIN(res_dt) AS transactionDate,
            "MIN" AS dateType'
            )->groupBy('month');
            
            $maxDateRecords = UserTransaction::where('ipay_error', 'TXN')
            ->selectRaw('
            date_format(res_dt, "%M-%y") AS month,
            MAX(res_dt) AS transactionDate,
            "MAX" AS dateType'
            )->groupBy('month')
            ->union($minDateRecords)
            ->get()
            ->toArray();
        })->groupBy($conditons);

this is only for reference what i am thinking to make

so please help me with it if this is possible in laravel

Thanks

psrz

3 years ago

Level 10

I think the trick to achieve the query you are trying to write with eloquent is making the subquery with the union the joinSub() part of it.

I made this using laravel's User model. First, build the two subqueries for the union inside.

$query1 = User::query()
        ->selectRaw('min(id) as "info"')
        ->selectRaw("'min' as \"infoType\"");

$query2 = User::query()
        ->selectRaw('max(id) as "info"')
        ->selectRaw("'max' as \"infoType\"")
        ->union($query1);

I'm using postgres, so you might have to tweak the field delimiters if you're using mysql

If I run $query2 this is the sql generated:

(
  select
    max(id) as "info",
    'max' as "infoType"
  from
    "users"
)
union
  (
    select
      min(id) as "info",
      'min' as "infoType"
    from
      "users"
  )

which returns two rows.

Then you assemble everything with the main model

    $rows = User::query()
        ->select(['id', 'name', 'join_sub.infoType', 'join_sub.info'])
        ->joinSub($query2, 'join_sub', 'users.id', '=', 'join_sub.info')
        ->get()
    ;

And all of that makes:

select
  "id",
  "name",
  "join_sub"."infoType",
  "join_sub"."info"
from
  "users"
  inner join (
    (
      select
        max(id) as "info",
        'max' as "infoType"
      from
        "users"
    )
    union
      (
        select
          min(id) as "info",
          'min' as "infoType"
        from
          "users"
      )
  ) as "join_sub" on "users"."id" = "join_sub"."info"

Tray2

3 years ago

Level 73

I suggest putting this in a migration

DB::statement(" 
CREATE OR REPLACE VIEW <view name> AS
SELECT date_format(res_dt, '%M-%y') AS month,
       MIN(res_dt)                  AS transactionDate,
       'MIN'                        AS dateType
FROM user_transactions
WHERE ipay_error = 'TXN'
GROUP BY date_format(res_dt, '%M-%y')
UNION
SELECT date_format(res_dt, '%M-%y') AS month,
MAX(res_dt)                  AS transactionDate,
'MAX'                        AS dateType
FROM user_transactions
WHERE ipay_error = 'TXN'
GROUP BY date_format(res_dt, '%M-%y')
");

Just exchange the <viev_name> with something fitting with the suffix views, like record_views.

Then make a model like RecordView then you can query it like you would a single table.

You can read more about views here https://tray2.se/posts/database-design-part-2

rohansinghrawat

3 years ago

Level 2

@Tray2 sir this is absolutely fine but i was looking for some eloquent or query builder methods

jlrdw

3 years ago

Level 75

@rohansinghrawat eloquent converts to regular sql at run tme. However you can use eloquent on a view. ELoquent also has all of the query builder methods.

Remember also active record is a shortcut that gets converted at run time.

Do some toSql() to see the regular queries produced by eloquent.

rohansinghrawat

3 years ago

Level 2

@jlrdw i know sir that eventually everything is converted into plain sql and then it gets executed i was just curios that if this is possible using laravel's predefined method

psrz

3 years ago

Level 10

@rohansinghrawat

I showed how to do it with eloquent queries

Using a view is just another approach to the same problem. If you think it's unlikely you'll have to modify or tweak the query you posted initially, then a view perhaps is a better fit.

On the other hand, if you think you'll have to dinamically change or modify those subqueries you'll have to use eloquent, or running migrations to modify the view will be pain.

Up to you.

1 like

Tray2

3 years ago

Level 73

@rohansinghrawat You are already using an inline view in your query, and to make it more Eloquent as you say, the best thing is to push that inline view into the database as a regular view, and then use eloquent for the rest of the query.

1 like

Please or to participate in this conversation.