[FIXED] Merge into one query two distincts

Issue

How do I need to make this into one query?

 $yearsInvoices = ModelOne::query()
      ->select(DB::raw('DISTINCT(YEAR(date)) as year'))
      ->groupBy('year')
      ->pluck('year')
      ->toArray();

  $yearsExpenses = ModelTwo::query()
       ->select(DB::raw('DISTINCT(YEAR(date)) as year'))
       ->groupBy('year')
       ->pluck('year')
       ->toArray();

  $years = \array_merge($yearsExpenses, $yearsInvoices);

Would appreciate some help. It's possible to join those two tables like this:

->join(modeltwo_table, modeltwo_table.c_id, modelone_table.c_id)

What I had:

 $yearsInvoices = ModelOne::query()
      ->join(modeltwo_table, modeltwo_table.c_id,'=', c_id)
      ->select([
          DB::raw('DISTINCT(YEAR(date)) as modelone_year'), 
          DB::raw('DISTINCT(YEAR(modeltwo_table.date)) as modeltwo_year')
      ])
      ->groupBy('modelone_year')
      ->groupBy('modeltwo_year')
      ->pluck('modeltwo_year', 'modelone_year')
      ->toArray();

But it didn't work.

Solution

It turns out that the way you use DISTINCT doesn't work because Laravel will generate invalid SQL syntax. Try something like this:

$yearsInvoices = ModelOne::query()
      ->join(modeltwo_table, modeltwo_table.c_id,'=', c_id)
      ->select('YEAR(date)) as modelone_year', 'YEAR(modeltwo_table.date)) as modeltwo_year')
      ->distinct()
      ->groupBy('modelone_year')
      ->groupBy('modeltwo_year')
      ->pluck('modeltwo_year', 'modelone_year')
      ->toArray();

Just after writing this answer I realized that there was probably a simpler way, try this:

 $yearsInvoices = ModelOne::query()
      ->join(modeltwo_table, modeltwo_table.c_id,'=', c_id)
      ->select(
          DB::raw('DISTINCT YEAR(modelone_table.date) as modelone_year, YEAR(modeltwo_table.date) as modeltwo_year'))
      ->groupBy('modelone_year')
      ->groupBy('modeltwo_year')
      ->pluck('modeltwo_year', 'modelone_year')
      ->toArray();

Answered By - Stefan Teunissen