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

Laravel – Merge data with different column names

I want to take the data between the specified dates from 2 tables, add them together where the attribute ‘user’ matches, and output the conolicated results.

I made a join operation as follows, but the unionAll method combines the data in the 2 SQL results, but does not collect the same user data.

For example, the user with id 1 is recorded in a table as publisher_id and the column name I want to collect is ticket. In the other table, there is a record as user_id and the column name I want to collect is diamonds. If the ticket value is 10 in one table and the diamonds value is 20 in the other table, the result should be 30, but two separate data are coming. How do I do this merging using the paginate management?

public function index(Request $request)
    {
        $begin_date = $request->get('begin_date') ? date('Y-m-d 00:00:00', strtotime($request->get('begin_date'))) : now()->startOfMonth();
        $end_date = $request->get('end_date') ? date('Y-m-d 23:59:59', strtotime($request->get('end_date'))) : now()->endOfMonth();

        $begin_time = strtotime($begin_date);
        $end_time = strtotime($end_date);

        $a_recharges = ARecharge::select('publisher_id as user_id', DB::raw('SUM(ticket) as coins'))
            ->whereBetween('created_at', [$begin_date, $end_date])
            ->groupBy('publisher_id');

        $b_recharges = BRecharge::select('user_id', DB::raw('SUM(diamonds) as coins'))
            ->whereBetween('create_time', [$begin_time, $end_time])
            ->groupBy('user_id');

        $recharges = $a_recharges->union($b_recharges)
            ->orderByDesc('coins')
            ->paginate(10)
            ->appends($request->except('page'));

        return view('admin.publisherrecharges.index', compact('recharges'));
    }

I tried the unionAll method but the results are not what I wanted

Tags:

2

Answers


  1. 0

    You are not performing join operation, rather you have performed union operation that’s the reason you are not getting your desired output in result set.

    Union:- It is used to combine the results of two or more SELECT statements into a single result set.

    Join:- used to combine rows from from two or more tables, based on a related column between them.

    Try This…

    public function index(Request $request)
{
    $begin_date = $request->get('begin_date') ? date('Y-m-d 00:00:00', strtotime($request->get('begin_date'))) : now()->startOfMonth();
    $end_date = $request->get('end_date') ? date('Y-m-d 23:59:59', strtotime($request->get('end_date'))) : now()->endOfMonth();

    $begin_time = strtotime($begin_date);
    $end_time = strtotime($end_date);

    $join_result = DB::table('ARecharge')
         ->leftJoin('BRecharge', 'ARecharge.publisher_id', '=', 'BRecharge.user_id')
         ->whereBetween('ARecharge.created_at', [$begin_date, $end_date])
         ->whereBetween('BRecharge.create_time', [$begin_time, $end_time])
         ->select(
            'ARecharge.*',
            DB::raw('SUM(ARecharge.ticket) + SUM(BRecharge.diamonds) as total_coins')
          )
         ->groupBy('ARecharge.user_id')  //optionally you choose 'BRecharge.publisher_id'
         ->orderBy('total_coins', 'desc')
         ->get();
}
    Login or Signup to reply.
  2. 0

    Trying to do use a crude SQL generator blocks you from using anything but the most basic of SQL syntax. There’s (at least) 2 ways of doing this in plain SQL.

    You could start with an outer join:

    SELECT user_id, SUM(coins)
FROM (
  SELECT publisher_id AS user_id, (ARecharge.ticket + IFNULL(BRecharge.diamonds, 0)) 
  FROM ARecharge
  LEFT JOIN BRecharge
  ON publisher_id = user_id
  WHERE....
  UNION
  SELECT user_id, diamonds
  FROM BRecharge
  LEFT JOIN ARecharge
  WHERE publisher_id IS NULL
  AND ...
) AS ilv
GROUP BY user_id;

    Or simply do a Union:

    SELECT user_id, SUM(coins)
FROM (
  SELECT user_id, diamonds
  FROM BRecharge
  WHERE ...
  UNION
  SELECT publisher_id, ticket
  FROM ARecharge
  WHERE ...
) AS ilv
GROUP BY user_id;

    (and, of course, you could apply the aggregation to the sub-queries instead of the combined dataset)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search